Connect to MSSQL 2005 using PHP

Here’s a couple of quick tips to connecting to a MSSQL 2005 server using PHP on a Windows webserver. You will need to enable the php_mssql.dll in your php.ini file.

Here’s the code to connect to the server.

<?php

$conn = mssql_connect('my_server', 'db_username', 'db_password');
if (!$conn) { die('ERROR: Unable to connect to the database.'); }
mssql_select_db('database_name');

?>

Here is a helpful function I found that will help escape strings. I found this at http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php.

<?php

function ms_escape_string($data) {
//if ( !isset($data) or empty($data) ) return ";
if ( !isset($data) or empty($data) ) return """;         // modified to handle empty $data
if ( is_numeric($data) ) return $data;

$non_displayables = array(
  '/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
  '/%1[0-9a-f]/', // url encoded 16-31
  '/[x00-x08]/', // 00-08
  '/x0b/', // 11
  '/x0c/', // 12
  '/[x0e-x1f]/' // 14-31
);
foreach ( $non_displayables as $regex ) {
  $data = preg_replace( $regex, ", $data );
  $data = str_replace("'", """, $data );
  //return $data;
  return "'".$data."'";        // modified to handle empty $data
}

?>

Here is a sample query to the database.

<?php

$id = ms_escape_string($_GET['id']);    // escape variable
$sql = 'SELECT id FROM table_name WHERE id='.$id.";
$rs = mssql_query($sql);

if (!$rs)
{
  echo 'ERROR: Unable to get records. '.mssql_get_last_message();    // equivalent to mysql_error()
}else{
  while ($row = mssql_fetch_assoc($rs))
  {
    echo 'The id = '.$row['id'].'<br />';
  }
}

?>

Another thing I learned from the article is the equivalent of “mysql_insert_id()”. It is “SELECT @@IDENTITY”. To handle dates and make them friendly to PHP use CONVERT(VARCHAR, [date], 20). This will display it as if you are using date(‘Y-m-d H:i:s) – 24 hour format. So an example would be…

SELECT CONVERT(VARCHAR, getdate(), 20) AS now

I tried to use PDO but was having a hard time getting it to work. I kept getting errors that had something to do with functions. I searched for solutions and tried them but none worked for me. Also, the rowCount() will always return less than or equal to 0.

I had trouble on both sides PHP and Microsoft. This seems to be the best solution that works for me. Hope it helps others.

2 thoughts on “Connect to MSSQL 2005 using PHP

  1. @nicolas it’s always been a challenge to get PHP and MSSQL to play well. i’ve pretty much given up. if ever i had to query stuff from MSSQL again with PHP, i’ll just try .net and send the resultset as JSON format, then have PHP read it. not sure if it will handle unicode well though. fortunately, i have yet to have data with unicode issue so i’m not too familiar with the combination of PHP, MSSQL, and unicode.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s