How to get data from Microsoft SQL Server using PHP

Intro

For the blog engine I’m writing for the WinPHP contest I needed a place to store data. Because windows specific features need to be used, Microsoft SQL Server is the logic choice. Getting data from the database isn’t as easy as making that choice. The extension that comes with PHP worked well with SQL Server 2005, but failed when running on a Windows Server 2008 machine with SQL Server 2008. Here’s a step-by-step tutorial on how to get it to work on SQL Server 2008.

Installation

First, download and install the Microsoft SQL Server 2005 Native Client.

Than download and install the SQL Server Driver for PHP Version 1.0 into the /Ext folder of your php installation.

Edit the php.ini file and add extension=php_sqlsrv.dll.

Restart IIS to reload the php.ini file.

To test if it works create a .php file at a location accessible from you browser file and add <?php phpinfo(); ?> to it. Open the location and check if sqlsrv appears in the list.

Getting Data

The hard part is done now. To get data from you SQL server a connection has to be made first. A call to sqlsrv_connect( string $serverName, array $connectionInfo ) with the right credentials will do just that.

For example:

$connectionInfo = array('Database' = > 'YourServerInstance',
                        'UID' = > 'UserName', 'PWD' = > 'AComplicatedPassword');
$connection = sqlsrv_connect('DatabaseOfChoice', $connectionInfo);

Getting some data from the database is almost as easy as just using SQL. A call to sqlsrv_query( resource $conn, string $tsql, array $params). This example returns all values from the aTable table where theID column equals 5:

$result = sqlsrv_query($connection, 'select * from aTable where theID = (?) ',
                       array(5));

The data can be extracted from the result set by calling sqlsrv_fetch_array( resource $stmt). The function returns an array of a row, which by default can be accessed associative or numeric. Here’s an example.

while ($row = sqlsrv_fetch_array($result)) {
  echo($row['ID'].', '.$row['Title'].', '.$row['Name']);
}

More Code

Here’s an entire class as a start to use in your own projects. It uses a singleton pattern so the database connection doesn’t have to be made a every call to the database. Only a call like db::query(‘select * from table’); to query the database. If a connection has to be made first, the class will take care of that.

class db {
 private
  static $instance = null;
 private
  $connection;

 public
  static function getInstance() {
    if (self::$instance == null) {
      self::$instance = new self;
    }
    return self::$instance;
  }

 private
  function __construct() {
    $connectionInfo =
        array('Database' = > 'YourServerInstance', 'UID' = > 'UserName',
              'PWD' = > 'AComplicatedPassword');

    $this->connection = sqlsrv_connect("DatabaseOfChoice", $connectionInfo);

    if ($this->connection == = false) {
      echo '<h2>Unable to connect to database</h2><br/>';
      die(print_r(sqlsrv_errors(), true));
    };
  }

 public
  static function query($query, $params = null) {
    $db = self::GetInstance();
    $result = sqlsrv_query($db->connection, $query, $params);
    if (!$result) {
      echo 'Error in statement execution.\n';
      die(print_r(sqlsrv_errors(), true));
    }
    return $result;
  }
}

What’s Next?

I don’t think it’s a good idea to hard code the connection information in the .php file. A far better way is to store these settings in an .xml file and load that from the db-class. Also, the class above can easily be extended with other functions from the SQL server for PHP API.