Migrating a PHP Application using MySQL to SQL Server running PHP on IIS
By Bill Ramos, Advaiya Inc.
In this blog post, I’ll walk through an example of migrating a PHP application originally written for a Windows Apache MySQL PHP server (WAMP) onto an IIS server running PHP and SQL Server 2008 R2 Express (often referred to as WISP for Windows IIS SQL Server PHP). To install the WISP stack, you will want to use the Windows Web Platform Installer 3.0 (WebPI) which will simplify getting IIS, PHP and SQL Server 2008 R2 express installed and working together. In this example, I will use the Sakila MySQL sample database that was migrated from MySQL to SQL Server. To learn more about how to perform the migration, see the blog post “MySQL to SQL Server Migration: How to Use SSMA”.
The PHP program example opens a connection to the database server and outputs the actor table into an HTML table. I’ll show the snippets of code for MySQL and PHP followed by the SQL Server PHP example. For SQL Server connectivity with PHP, I’m going to use the Microsoft Drivers for PHP for SQL Server version 2.0 that are installed with WebPI on IIS. I’ve provided links to the respective PHP API calls so that you can compare the APIs for yourself.
Using PHP to Connect to a MySQL and SQL Server Database
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
<html><head><title>MySQL Actor Table Viewer</title></head><body>
<?php
$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.
<html><head><title>SQL Server Actor Table Viewer</title></head><body>
<?php
$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.
Using PHP to Run a Query Against MySQL and SQL Server
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");
}
In this example, I’ve used the Transact-SQL function CONVERT to change the datatime data type for last_update to a varchar datatype to simplify the code later.
The following block of code is used for both MySQL and SQL Server PHP to setup the HTML table.
echo "<table >";
echo "<td style='solid black;Font-size=28;Font-Weight=bold'>';
echo "Table : ";
echo $table;
echo "</td>";
echo "</table>";
echo "<table >";
echo "<tr>";
// printing table headers with desired column names
echo "<td style='border=1px solid black;Font-size=18;Font-Weight=bold'>';
echo "actor_id";
echo "</td>";
echo "<td style='border=1px solid black;Font-size=18;Font-Weight=bold'>';
echo "first_name";
echo "</td>";
echo "<td style='border=1px solid black;Font-size=18;Font-Weight=bold'>';
echo "last_name";
echo "</td>";
echo "<td style='border=1px solid black;Font-size=18;Font-Weight=bold'>';
echo "last_update";
echo "</td>";
echo "</tr>";
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 PHP
The 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 rows
while($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td style='border=1px solid black'>';
echo $row['actor_id'];
echo "</td>";
echo "<td style='border=1px solid black'>';
echo $row['first_name'];
echo "</td>";
echo "<td style='border=1px solid black'>';
echo $row['last_name'];
echo "</td>";
echo "<td style='border=1px solid black'>';
echo $row['last_update'];
echo "</td>";
echo "</tr>\n";
}
echo "</table>";
SQL Server PHP
With SQL Server PHP, you’ll use the sqlsrv_fetch_array() function to perform the same action as mysql_fetch_assoc() as shown in the next code block.
// printing table rows
while($row = sqlsrv_fetch_array($result))
{
echo "<tr>";
echo "<td style='border=1px solid black'>';
echo $row['actor_id'];
echo "</td>";
echo "<td style='border=1px solid black'>';
echo $row['first_name'];
echo "</td>";
echo "<td style='border=1px solid black'>';
echo $row['last_name'];
echo "</td>";
echo "<td style='border=1px solid black'>';
echo $row['lupdate'];
echo "</td>";
echo "</tr>\n";
}
echo "</table>";
In this example, the last_update datetime column was converted to a varchar in the query string aliased AS lupdate executed earlier. You can specify data type conversions in PHP. See the help topic “How to: Specify PHP Data Types” for more information.
Using PHP to Close the Connection for MySQL and SQL Server
After running a query in your PHP application, it’s a good practice to close your resources.
MySQL PHP
In this final code block, mysql_free_result() is used to free resources for the PHP application.
// Close statement and connection
mysql_free_result($result);
?>
</body></html>
SQL Server PHP
With SQL Server PHP, there are 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);
?>
</body></html>
Resources for PHP for MySQL and SQL Server
By having both the PHP Manual MySQL Functions API and the SQLSRV Driver API Reference (Microsoft Drivers for PHP for SQL Server), conversion from the MySQL PHP API to SQL Server PHP API is a straightforward process. The two PHP applications for MySQL and SQL Server are attached to this blog post.
SQL Server and MySQL PHP Table Viewer Examples.zip
Comments
Anonymous
June 01, 2011
Nice article. examples are awesome.Anonymous
June 30, 2011
Sorry for the simple question since I'm new to database. I like to migrate from MySQL to SQL server, but does this mean my PHP code will have to be re-coded for connecting/query to SQL server?Anonymous
July 01, 2011
I am very, very new to php, attempting to convert files others did to 5, but I had to use double quotes at the ends of some lines in this example to make it work properly (substituted one of my tables for your fieldnames)......at the end of the <td> lines. :) kbAnonymous
October 02, 2013
Make sure to build your own array fetch, query and connection functions and reuse those in your code then you simply swap out the MySQL functionality for mssqlAnonymous
March 26, 2015
Thanks Bill for posting this. To answer Lou's MySQL to SQL Server questions, for anyone reading this more recently re-coding the connection and query sections of your PHP code is the least of your worries. It is the actual SQL code that will cause the biggest headaches. While all SQL databases are based on an ANSI standard, they have also extended this standard with statements that are unique to the database platform. T-SQL is just one example of extending the base SQL standard. In addition there are many other considerations (like performance) between different database engines that can effect your final outcome with both positive and negative un-expected side effects of switching RDBMS.