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.