PHP Configure Microsoft SQL Server¶

Tested with Microsoft SQL Server 2000/2008 on a Linux operating system.

Prerequisites¶

It is assumed the following Linux system packages are already installed.

freetds-common
freetds-bin
php-sybase
unixodbc

Configuration Instructions¶

Edit the FreeTDS configuration file
```
# vi /etc/freetds/freetds.conf
```

Add SQL Server DSN connection. Example of SQL Server 2000

[sqlserver]
     host = sqlservertest
     port = 1433
     tds version = 8.0

Example of SQL Server 2008 with an instance

[dbexample]
    host = 192.168.1.50
    port = 1433
    instance = instance-name
    tds version = 8.0

Test the connection using tsql as follows. SQL Server 2000

$ tsql -S sqlservertest -U username -D instance-name

SQL Server 2008

$ tsql -S 192.168.1.50 -U username -D instance-name

Enter the password for the user and press enter.

At the tsql prompt type a SQL query and press enter. Example of SQL Server 2000 Query

SELECT TOP 10 * FROM table_name;

Example of SQL Server 2008 Query

SELECT TOP 10 [Record_Id],[Creator_Id] 
FROM [addressbook].[dbo].[User_Field_Defs_Tbl] 
WHERE Description like '%test%';

Then to execute the SQL query, type without double quotes "go" then press enter. If it worked successfully, there will now be results rendered to the screen. To exit/quit type without double quotes "exit", "quit", "bye" or simply press CTRL-D.

Database Connection Test¶

TSQL Client¶

A database connection test can be done without a DSN as follows. Replace keywords.

$ tsql -H your.server.name -p 1433 -U yourusername -P yourpassword -D yourdatabasename

PHP¶

PHP code example of connecting to SQL Server using PDO dblib.

<?php
try
{
    $szDSN = 'dblib:host=sqlservertest;dbname=addressbook';
    $szDbUser = 'username';
    $szDbPass = 'password';
    $obDbHandle = new PDO($szDSN,$szDbUser, $szDbPass);
    $obDbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $szQry = "SELECT TOP 10 [Record_Id],[Creator_Id]
    FROM [addressbook].[dbo].[User_Field_Defs_Tbl]
    WHERE Description like '%test%'";
    $stmt = $obDbHandle->prepare($szQry);
    //$stmt->bindValue(1, 7256, PDO::PARAM_INT);
    $stmt->execute();

    while($obRow = $stmt->fetch(PDO::FETCH_OBJ))
    {
        echo $obRow->Record_Id . "<br />";
        echo $obRow->Creator_Id . "<br />";
    }
}
catch(PDOException $obException)
{
        echo $obException->getMessage() " in " . $obException->getFile() . " on line " . $obException->getLine() . ".\n";
} 
$dbh = null;

PHP code example of connecting to SQL Server using PDO sqlsrv.

<?php
try
{
    $szDSN = 'sqlsrv:Server=sqlservertest;Database=addressbook';
    $szDbUser = 'username';
    $szDbPass = 'password';
    $obDbHandle = new PDO($szDSN,$szDbUser, $szDbPass);
    $obDbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $szQry = "SELECT TOP 10 [Record_Id],[Creator_Id]
    FROM [addressbook].[dbo].[User_Field_Defs_Tbl]
    WHERE Description like '%VIP%'";
    $stmt = $obDbHandle->prepare($szQry);
    //$stmt->bindValue(1, 7256, PDO::PARAM_INT);
    $stmt->execute();

    while($obRow = $stmt->fetch(PDO::FETCH_OBJ))
    {
        echo $obRow->Record_Id . "<br />";
        echo $obRow->Creator_Id . "<br />";
    }
}
catch(PDOException $obException)
{
    echo $obException->getMessage() " in " . $obException->getFile() . " on line " . $obException->getLine() . ".\n";
}