Getting Started with PHP and SQL Azure

Artikel
02/12/2010

[Updated November 9th, 2011]

In this post, I’ll show you how to get started with PHP and SQL Azure. I’ll walk you through building a very simple Web page in the same way I did in a recent post: Getting Started with the SQL Server Driver for PHP. That blog entry is very relevant because I used a SQL Server Express database as my data store, and in the development stage of an application, there is almost no difference between a PHP/SQL Server Express application and a PHP/SQL Azure application. There are some differences and limitations, which I’ll highlight, but not many that I encountered. (Note: I won’t go into the benefits of cloud computing in this post, but if you are interested, I would suggest starting here: Cloud Computing.) For more thorough information about SQL Azure, see the SQL Azure documentation and the Windows Azure SDK for PHP.

Updates as of November 9th, 2011:

The latest version of the SQL Server Drivers for PHP (which includes the sqlsrv and pdo_sqlsrv drivers, both of which provide connectivity to SQL Azure) can be downloaded here: https://www.microsoft.com/download/en/details.aspx?id=20098.

The latest version of SQL Server Express can be downloaded here: https://www.microsoft.com/download/en/details.aspx?id=26729. I suggest installing SQLEXPADV_x86 or SQLEXPADV_x64 option (depending on your architecture) as the advanced installation provides advanced functionality and tooling.

In a Nutshell

The only difference between the application I built in this post (the post I mentioned earlier) and the one I’ll build here is in the connection code:

 $serverName = "tcp:ServerID.database.windows.net, 1433";
 $connectionOptions = array("Database" => "DatabaseName", 
                            "UID" => "Username@ServerID",
                            "PWD" => "password");
 $conn = sqlsrv_connect($serverName, $connectionOptions);
  
 if($conn === false)
 {
     die(print_r(sqlsrv_errors(), true));
 }

Notice three things:

In the $serverName variable, the server name is prefixed with tcp:, and…
the connection port is specified (1433).
The format of the UID element in the connection options array is Username@ServerID.

Note: I’ll show you where I’m getting the value of ServerID and how to set up a username and password in the sections below.

Those are the only changes I needed to make to the application code. So, in the development stage of your application, you can build your application against SQL Server Express. For details about moving the database to the cloud, read on.

PDO connection information: If you want to use PDO, here’s the connection code. Otherwise, using PDO with SQL Azure should be the same as using it with SQL Server.
 $server = "ServerID.database.windows.net,1433";
 $username = "Username@ServerID";
 $password = "Password";
 $database = "DbName";
 try
 {
     $conn = new PDO("sqlsrv:server=$server ; Database = $database", $username, $password);
 }
 catch(Exception $e)
 {
     die(print_r($e));
 }

Create a SQL Azure Server

To create a SQL Azure server, you need a Windows Azure subscription. You can sign up for a free trial here: https://www.microsoft.com/windowsazure/free-trial/. (You’ll need a Windows Live ID to sign up.) For information about other options and pricing details, see https://www.microsoft.com/windowsazure/offers/ and https://www.microsoft.com/windowsazure/pricing/.

After creating your subscription, go to the Windows Azure Management Portal to get started: https://windows.azure.com/. To create your server, click on Database in the left-hand pane…

Next, click on Create a new SQL Azure Server…

Next, you will be taken through the steps to create your server.

Select the subscription with which your server will be associated:

Select the region in which your server will be deployed:

Create the administrator name and password. Note that this is the username and password I referred to in the connection code above:

In the next step, click Add to add a firewall rule that enables access to your server. Note that I’m only allowing access from my current IP address (which you may or may not want to do):

Lastly, check the Allow other Windows Azure services to access this server box and click Finish.

Now, in the left-hand pane of the portal, under your subscription, you should see a server icon with your server ID:

Note that if you click on the server icon, it’s properties will appear in the right-hand pane (making them easy to copy). The server ID you see here is the ServerID I mentioned in the connection code at the start.

Now, on to creating a database…

Create a SQL Azure Database

To create a database, click the Create icon near the top of the portal…

…then fill in the database name, select an edition, and select a size:

You should now see your newly created database among the databases listed for your server:

Install the SQL Server Driver for PHP

The easiest way to access SQL Azure databases from PHP is by using the SQL Server Driver for PHP. I described how to install and configure the driver in this post: Getting Started with the SQL Server Driver for PHP. If you need instructions for installing PHP and configuring your Web server, see Getting Started with PHP on Windows.

Test Connectivity from PHP

To test connectivity to the SQL Azure database we created earlier (ExampleDB), simply execute the connection code above (in the In a Nutshell section), replacing ServerID with your generated server ID, and, of course, Username and Password with your username and password. If you are trying to connect from behind a corporate firewall, you may need to install the Microsoft Firewall Client. You may also need to change the identity of the application pool under which PHP is running to a domain account. For troubleshooting other connection problems, see Using SQL Server Client APIs with SQL Azure 1.0.

Add Tables to the Database

You can create tables in your database by using the database management UI in the Windows Azure Management Portal (just click on the Manage icon near the top of the portal and login). However, you can also execute SQL commands just like you would with SQL Server to create a database, which is what I’ll do here.

To add tables (one table, actually) to the ExampleDB database, execute the following PHP script (after filling in your server ID, username, and password):

 <?php
 $serverName = "tcp:xxxxxxxxxx.database.windows.net, 1433";
 $connectionOptions = array("Database" => "ExampleDB", 
                            "UID" => "Username@xxxxxxxxxx",
                            "PWD" => "password");
 $conn = sqlsrv_connect($serverName, $connectionOptions);
  
 if($conn === false)
 {
     die(print_r(sqlsrv_errors(), true));
 }
  
 $createTBLsql = "CREATE TABLE [dbo].[RegistrationTbl]
                 (
                     [ID] [int] IDENTITY(1,1) NOT NULL,
                     [Email] [nvarchar](50) NOT NULL,
                     [LastName] [nvarchar](50) NOT NULL,
                     [FirstName] [nvarchar](50) NOT NULL,
                     [RegDate] [datetime] NOT NULL,
                     CONSTRAINT [PK_RegistrationTbl] PRIMARY KEY CLUSTERED 
                     (
                         [ID] ASC
                     )
                 )
                 CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueEmail] ON [dbo].[RegistrationTbl] 
                 (
                     [Email] ASC
                 )";
  
 $createTBLstmt = sqlsrv_query($conn, $createTBLsql);
 if($createTBLstmt === false)
 {
     die(print_r(sqlsrv_errors(), true));
 }
 else
 {
     echo "Table successfully created.";
 }
 ?>

If you look closely at the SQL for creating the table, you’ll notice that I am explicitly creating a clustered index on the table, a requirement for all tables in SQL Azure.

Run the Application

Now we can run our application with a SQL Azure database. Copy the following script to a file in your root directory (for more detail about this simple application, see Getting Started with the SQL Server Driver for PHP):

 <html>
 <head>
 <Title>Example Web Form</Title>
 </head>
 <body>
 <form method="post" action="?action=add" enctype="multipart/form-data" >
     Last name <input type="text" name="lastName" id="lastName"/><br />
     First name <input type="text" name="firstName" id="firstName"/><br />
     E-mail address <input type="text" name="emailAddress" id="emailAddress"/><br />
     <input type="submit" name="submit" value="Submit" />
 </form>
  
 <?php
 /* Connect to SQL Azure */
 $server = "tcp:xxxxxxxxxx.database.windows.net,1433"; 
 $user = "Username@xxxxxxxxxx";
 $pass = "password";
 $database = "ExampleDB";
  
 $connectionoptions = array("Database" => $database, 
                            "UID" => $user, 
                            "PWD" => $pass);
  
 $conn = sqlsrv_connect($server, $connectionoptions);
 if($conn === false)
 {
     die(print_r(sqlsrv_errors(), true));
 }
  
 if(isset($_GET['action']))
 {
     if($_GET['action'] == 'add')
     {
         /*Insert data.*/
         $insertSql = "INSERT INTO RegistrationTbl (LastName, FirstName, Email, RegDate) VALUES (?,?,?,?)";
         $params = array(&$_POST['lastName'], 
                         &$_POST['firstName'], 
                         &$_POST['emailAddress'], 
                         date("Y-m-d"));
         $stmt = sqlsrv_query($conn, $insertSql, $params);
         if($stmt === false)
         {
             /*Handle the case of a duplicte e-mail address.*/
             $errors = sqlsrv_errors();
             if($errors[0]['code'] == 2601)
             {
                 echo "The e-mail address you entered has already been used.<br />";
             }/*Die if other errors occurred.*/
             else
             {
                 die(print_r($errors, true));
             }
         }
         else
         {
             echo "Registration complete.</br>";
         }
     }
 }
  
 /*Display registered people.*/
 $sql = "SELECT * FROM RegistrationTbl ORDER BY LastName";
 $stmt3 = sqlsrv_query($conn, $sql);
 if($stmt3 === false)
 {
     die(print_r(sqlsrv_errors(), true));
 }
  
 if(sqlsrv_has_rows($stmt3))
 {
     print("<table border='1px'>");
     print("<tr><td>Last Name</td>");
     print("<td>First Name</td>");
     print("<td>E-mail Address</td>");
     print("<td>Registration Date</td></tr>");
     while($row = sqlsrv_fetch_array($stmt3))
     {
         $regDate = date_format($row['RegDate'], 'Y-m-d');
         print("<tr><td>".$row['LastName']."</td>");
         print("<td>".$row['FirstName']."</td>");
         print("<td>".$row['Email']."</td>");
         print("<td>".$regDate."</td></tr>");
     }
     print("</table>");
 }
 ?>
 </body>
 </html>

Differences Between SQL Azure and SQL Server

As I mentioned earlier, the only code I needed to change in the code above when changing to a SQL Azure database was the connection code. However, there are some other differences that you might run into when developing a more complex application. I’ll list the two that got me, but for a complete list, see the SQL Azure documentation: Guidelines and Limitations.

You cannot change databases with the Transact-SQL USE command. To change databases, you must close your open connection and open a new one to the target database.
Table partitioning is not supported.

Additional Resources

That’s it for now. Hopefully, this is enough to get you started with PHP and SQL Azure. Look for more in-depth posts soon about using PHP with the Azure platform. And, as always, please let me know what information I’ve missed (or just what other information you would like to see).

Thanks.

-Brian

Share this on Twitter

Comments

Anonymous
July 28, 2011
Thank you so much for those post about SQL/Azure with PHP.now i now where to start from.
Anonymous
December 26, 2014
Awesome information, this beginner's guide to SQL Azure could also be useful sqlturbo.com/beginners-guide-to-microsoft-azure-sql-database
Anonymous
February 27, 2015
I've my php project, I've created database but somehow I'm not able to connect with database. Can you please help me how do I can connect with my database???
Anonymous
February 27, 2015
Sorry. This post is out of date. Up-to-date information is here: azure.microsoft.com/.../sql-database-php-how-to-use
Anonymous
August 31, 2015
The comment has been removed

Dela via