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.
Now try that query again with unicode (É and À in particular). It’s buggy and it took me forever to figure it out
@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.