Migrating From MySQL to SQL Server running PHP on IIS

In the following code, we connect to the MySQL server with mysql_connect() and then select the database with mysql_select_db() that we will work against.
MySQL PHP

MySQL Actor Table Viewer

$db_host = 'localhost';

$db_user = 'root';

$db_pwd = 'pass@word1';

$database = 'sakila';

$table = 'actor';

if (!mysql_connect($db_host, $db_user, $db_pwd))

    die("Can't connect to database");

if (!mysql_select_db($database))

    die("Can't select database");

SQL Server PHP
With SQL Server PHP, the database context is established in one call to sqlsrv_connect() that returns a connection handle for running a queries.

SQL Server Actor Table Viewer

$db_host = '.\SQLEXPRESS';

$db_user = 'sa';   //recommend using a lower privileged user

$db_pwd = 'pass@word1';

$database = 'sakila';

$table = 'actor';

$connectionInfo = array("UID" => $db_user, "PWD" => $db_pwd, "Database"=>$database); 

$conn = sqlsrv_connect( $db_host, $connectionInfo);

if( !$conn )

{

     echo "Connection could not be established.\n";

     die( print_r( sqlsrv_errors(), true));

}

You can refer to Connection Options for the list of supported keys for the connection array used for the variable $connectionInfo.



In the next block of code, you’ll see the differences in running a query against MySQL and SQL Server.
MySQL PHP
This example uses the mysql_query() function to execute the query and return the result as a statement handle for further processing.

// sending query

$result = mysql_query("SELECT * FROM {$table} LIMIT 0, 15");

if (!$result) {

    die("Query to show fields from table failed");

}

In this example, the query uses the LIMIT clause to display the first 15 records.
SQL Server PHP
The sqlsrv_query() function is used to run a query using the connection context provided by the $conn connection handle. In this example, the SQL statement was changed to use the TOP clause and to show the fields for the SELECT statement rather than use * for all columns.

// sending query

$tsql = "SELECT TOP 15 actor_id, first_name,last_name,CONVERT(varchar(50),last_update,121) AS lupdate FROM {$table}";

$result = sqlsrv_query( $conn, $tsql);

if (!$result) {

 die("Query to show fields from table failed");

}

I
 

The following block of code is used for both MySQL and SQL Server PHP to setup the HTML table.

echo "";
echo "
";
echo "

';

echo "Table :   ";
echo  $table;
echo "
";

echo "";
echo "
";
 
// printing table headers with desired column names
echo "
";
echo "
";
echo "
";
echo "
";
echo "
";
 
 
Using PHP to Fetch Data from MySQL and SQL Server
In this next section of code, I’ll show one of the many ways to fetch data from MySQL and the corresponding way in with SQL Server using PHP.MySQL PHPThe following code block shows how to loop through the results for the query using the mysql_fetch_assoc() function to return an array of strings keyed with the column name.// printing table rowswhile($row = mysql_fetch_assoc($result)){    echo "";    echo "";    echo "";    echo "";    echo "";    echo "\n";}echo "

';

echo "actor_id";
echo "
';

echo "first_name";
echo "
';

echo "last_name";
echo "
';

echo "last_update";
echo "
';    echo $row['actor_id'];     echo "
';    echo $row['first_name'];     echo "
';    echo $row['last_name'];     echo "
';    echo $row['last_update'];      echo "
";SQLServer PHPWith SQL Server PHP, you’lluse the sqlsrv_fetch_array() function to perform the same action asmysql_fetch_assoc() as shown in the next code block.// printing table rowswhile($row = sqlsrv_fetch_array($result)){    echo "";    echo "';    echo $row['actor_id'];     echo "";    echo "';    echo $row['first_name'];     echo "";    echo "';    echo $row['last_name'];     echo "";    echo "';    echo $row['lupdate'];      echo "";    echo "\n";}echo "";In this example, the last_update datetime column wasconverted to a varchar in the query string aliased AS lupdate executed earlier.
 
After running a query inyour PHP application, it’s a good practice to close your resources. MySQL PHPIn this final code block, mysql_free_result() is used to free resources for the PHPapplication.
// Close statement and connection
mysql_free_result($result);
?>
SQL ServerPHPWith SQL Server PHP, thereare two functions used to free resources: sqlsrv_free_stmt()works similar to mysql_free_result(); sqlsrv_close()closes the connection to the server.
// Close statement and connection
sqlsrv_free_stmt( $result);
sqlsrv_close( $conn);
?>
compile by  Divyang Panchasara Sr. Programmer Analyst Hitech OutSourcing