Jaa

Getting Started with the SQL Server Driver for PHP

  • Artikkeli

In this post, I’ll focus on the basics of using the SQL Server Driver for PHP to insert and retrieve data in a SQL Server Express database. I’ll build a web page that exposes enough of the driver functionality for you to understand the basics of connecting to a server, inserting and retrieving data, and handling errors.

 

A quick note about simplicity: The web page I’ll build in this post is very simple – it’s just a simple form, really. It collects information about people who are registering for some fictional event. I’m intentionally keeping it very simple so I can focus on the PHP code for using the driver. (Finally, I get to write some code. J) For a more complex example that demonstrates more of the driver features, see the Example Application that is part of the driver documentation.

 

Installation and Configuration

To execute the code in this post, you will need to have PHP, the SQL Server Driver for PHP, and SQL Server Express installed on your local machine. If you used the Web Platform Installer and followed the instructions in my last post, you are ready to go. J If you installed PHP manually, you will still need to install the driver and SQL Server Express:

 

· Download SQL Server Express here: SQL Server Express Download

· Download the SQL Server Driver for PHP here: Driver Download

 

After you have downloaded the driver, you need to move the appropriate driver .dll file into your PHP extension directory, add the corresponding entry in your php.ini file to enable the extension, and restart your Web server. The appropriate version of the driver depends on whether your PHP installation PHP 5.2 or 5.3, is thread-safe or non-thread-safe, and whether it was compiled with Visual C++ 6.0 or Visual C++ 9.0. For example, if you installed a non-thread-safe version of PHP 5.3 that was compiled with Visual C++ 9.0, you need to put the php_sqlsrv_53_nts_vc9.dll file into your PHP extension directory and add the following line to the extension section of your php.ini file (and then restart your Web server):

 

extension=php_sqlsrv_53_nts_vc9.dll

Creating the Database

The database I’ll use for this simple web application can be created by executing the PHP script that is attached to this post (CreateExampleDB.php). Of course, you’ll have to update the script with your SQL Server login and password. If you have SQL Server Management Studio installed, you can simply execute the following Transact-SQL:

 

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

USE [master]

CREATE DATABASE ExampleDB

GO

USE ExampleDB

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

      )ON [PRIMARY]

) ON [PRIMARY]

     

CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueEmail] ON [dbo].[RegistrationTbl]

(

      [Email] ASC

) ON [PRIMARY]

 

Building the Web Page

The web page I’ll build begins with the code below. In the following sections of this post, I’ll fill in the PHP code for connecting to the server, inserting and retrieving data, and handling errors. Copy this code into a file named ExampleForm.php and put it in your Web server’s root directory so you can test the page as we go. (The completed web page is in the .zip file that is attached to this post.)

 

<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

      //I’ll fill in the code here as we move through this tutorial.

?>

</body>

</html>

 

Connecting to the Server

Before I can insert or retrieve data, I need to establish a connection to the server. The SQL Server Driver for PHP API provides the sqlsrv_connect function for doing this. This function takes one required parameter ($serverName) and one optional parameter ($connectionOptions). The $connectionOptions parameter allows me to specify the database I want to connect to, my SQL Server login, my password, and other options.  (For a complete list of the connection options, see the sqlsrv_connect reference page.) So, here’s what I need to connect to my local instance of SQL Sever Express:

 

$serverName = ".\sqlexpress";

$connectionOptions = array("Database"=>"ExampleDB",

         "UID"=>"My_Login",

       "PWD" => "My_Password");

$conn = sqlsrv_connect($serverName, $connectionOptions);

if($conn === false)

{

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

}

 

I’ll explain the error handling later, but I’ll try to anticipate other questions you might have about this code here:

· What is the “.” in the server name? This is just shorthand for specifying the local server. You could also use “(local)\sqlexpress” or “yourServerName\sqlexpress”.

·  If I have to specify the database in the connection string, how do I change databases later? You simply need to use the sqlsrv_query function to execute a statement that changes the database. The Transact-SQL command for this is USE databaseName.

 

Inserting Data

Next I’ll write code that will take data from the submitted form and insert it into the database. To do this, I’ll use the sqlsrv_query function that executes an INSERT command. (There are three lines of code to focus on here, just below the /*Insert data*/ comment.) First, I’ll need to define the SQL that I want to execute ($insertSql). Second, I define an array ($params) that contains the parameter values from the $_POST variable. And last, I pass the connection resource, the SQL string, and the parameter array to the sqlsrv_query function.

 

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>";

            }

      }

}

 

As in the previous section, I’ll explain the error handling later, but I’ll try to anticipate other questions now:

· Why does the SQL string contain question marks? I’m using question marks (?) in place of the parameter values instead of concatenating parameter values with the SQL string to avoid SQL injection attacks. I wrote a post about why this is important some time back: How and Why to Use Parameterized Queries.

· Why are the variables in the $params array references? This is the recommended approach for constructing parameter arrays. Not using references in the parameter array can lead to unexpected results if the query is executed multiple times.

 

Retrieving Data

Now I want to display a table that contains information about all the people who have registered for my fictional event. Again, I’ll use the sqlsrv_query function, but this time I’ll use it to execute a SELECT statement. To access rows of returned data, I’ll use the sqlsrv_fetch_array function. (By default, this function returns an array with both numeric and associative indexes.) Notice that I’m using the sqlsrv_has_rows function to display the table only when the executed query actually returns rows (i.e. there are actually results to display).

 

$sql = "SELECT * FROM RegistrationTbl ORDER BY LastName";

$stmt = sqlsrv_query($conn, $sql);

if($stmt === false)

{

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

}

if(sqlsrv_has_rows($stmt))

{

      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($stmt))

      {

            $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>");

}

 

Handling Errors

OK, now for the promised discussion of the error handing code. The sqlsrv_errors function returns error information about the last operation performed by the driver. In most cases, I simply use the PHP die function and print the error array. Obviously, this would only be OK for a development environment. But notice in the Inserting Data section above, I am actually checking for a particular SQL Server error code: 2601. This error occurs when I try to enter a duplicate entry into a field that doesn’t allow duplicates. Since the Email column in my database doesn’t allow duplicates (specified by the UNIQUE constraint), I can use this error information to inform the user that the entered e-mail address has already been used.

 

You may also wonder why I use the triple equals operator (===) to check for errors. This is simple a precaution. Since the triple equals operator compares both value and type, I make sure that an integer value of zero (that could be returned by a function) is not evaluated as the Boolean value false.

 

OK, that’s it for now. Look for posts soon that drill down into some of the features of the SQL Server Driver for PHP. As always, please comments and questions are welcome.

Thanks.

-Brian

Share this on Twitter

 

GetStartedPHPDriver.zip

Comments

  • Anonymous
    March 05, 2010
    The comment has been removed
  • Anonymous
    March 05, 2010
    Glad it was helpful...and I'm sure your comments will help other readers. Thanks!
  • Anonymous
    May 11, 2011
    How can i update a datetime field with NULL?when i tried it get updated with 01-01-1900 (something like this)
  • Anonymous
    May 13, 2011
    If you set a parameter value to null, you should see NULL in the database. Like this...$params = array(4, null);$stmt = sqlsrv_query($conn, "insert into Table_1 (id, text)                            values (?,?)", $params);-Brian
  • Anonymous
    March 05, 2012
    I config this story but recive this error:(why?what is my user name and password???Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => -1 [code] => -1 [2] => [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [message] => [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Login timeout expired [message] => [Microsoft][SQL Server Native Client 10.0]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => -1 [code] => -1 [2] => [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) )
  • Anonymous
    March 14, 2012
    @ "please help me"-Either your server is not on your network, or it is not configured to allow remote connections (this is the default). This will be helpful in configuring for remote connections: www.ehow.com/how_6885671_configure-2008-allow-remote-connections.htmlHope that helps.
  • Anonymous
    October 25, 2013
    The comment has been removed
  • Anonymous
    February 24, 2014
    how can we make delete function like the above insert ?
  • Anonymous
    June 18, 2014
    $params = array(&$_POST['lastName'],                           &$_POST['firstName'],                           &$_POST['emailAddress'],why &
  • Anonymous
    June 18, 2014
    At the time this post was published, there was a issue with the driver that could be avoided if parameter values were passed by reference (which is what the & is for). I don't know, however, if that issue has been fixed. I think the best place to ask is here: sqlsrvphp.codeplex.com/discussions.Hope that helps.-Brian