Accessing SQL Server 2005 Databases with PHP

Filename: Accessing SQL Server 2005 with PHP.doc1

Accessing SQL Server 2005 Databases with PHP

SQL Server Technical Article

Writer: Deepak Vohra

Published: September2006

Applies To: SQL Server2005 Express Edition Database

Summary: PHP:Hypertext Preprocessor (PHP) is a scripting language that is suited for developing Web applications. A PHP script can be embedded in an HTML page and run as a .php script or as a Windows Script Host script (.wsf file). PHP5.1.6 is the latest version of PHP and includes extensions for various databases including the SQLServer database. The SQLServer database extension in PHP 5 is installed by default when PHP is installed. With the SQLServer database extension, a connection can be established with the SQLServer2005 database and SQL statements run on the database. The PHP extension supports databases created in different versions of SQL Server. This paper covers configuring the PHP extension with SQL Server 2005 Express Edition databases only.

Copyright

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.

Microsoft and Windowsare either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Filename: Accessing SQL Server 2005 with PHP.doc1

Introduction

SQL Server Database Extension Configuration Settings

SQL Server Database Extension Functions

Installing PHP in Windows Script Host

Installing SQL Server 2005 Express

Creating a Connection

Creating a Database Table

Obtaining a Result Set

Conclusion

Accessing SQL Server 2005 with PHP1

Introduction

PHP provides an extension for accessing Microsoft® SQL Server™ databases. To use the SQL Server extension, all that is required is to activate the extension in the PHP configuration file.

SQL Server Database Extension Configuration Settings

The PHP SQL Server database extension provides configuration settings to run PHP scripts with the SQL Server database. These configuration directives are specified in the PHP configuration file. Some of the these configuration settings are described in Table1.

Configuration setting / Description / Type / Default value
mssql.allow_persistent / Specifies whether persistent connections are allowed. If set to true (“1”), persistent connections may be established with a SQLServer database. A persistent connection is a connection that does not close after a script has run. / boolean / 1
corresponding to true.
mssql.max_persistent / Specifies the maximum number of persistent connections per process. / integer / -1
Value of “-1” signifies no limit.
mssql.max_links / Specifies the maximum number of SQLServer database connections per process, including persistent connections. / integer / -1
Value of “-1” signifies no limit.
mssql.min_error_severity / Specifies the minimum error severity to display. / integer / 10
mssql.connect_timeout / Specifies the connect timeout in seconds. / integer / 5
mssql.timeout / Specifies the query timeout in seconds. / integer / 60

Table 1.SQL Server Database Extension configuration settings

SQL Server Database Extension Functions

The SQL Server database extension provides functions to connect to the SQL Server database, run SQL statements and stored procedures in the database, and retrieve the results of running SQL queries. Some commonly used SQL Server database extension functions are described in Table2.

Extension Function
Optional parameters are specified in [] / Description / Return Type
mssql_connect ( [string servername [, string username [, string password [, bool new_link]]]] ) / Establishes a SQLServer database connection. The link to the SQL Server database closes after the PHP script runs. / Returns a link identifier for the SQL Server database on success. ‘False’ on error.
mssql_select_db ( string database_name [, resource link_identifier] ) / Selects a SQLServer database. / ‘TRUE’ on success
‘FALSE’ on failure.
mssql_close ( [resource link_identifier] ) / Closes a SQLServer database connection. If a link identifier is not specified, the last opened link is closed. Does not close persistent links generated by using mssql_pconnect(). / Returns ‘TRUE’ on success and ‘FALSE’ on failure.
mssql_pconnect ( [string servername [, string username [, string password [, bool new_link]]]] ) / Opens a persistent connection to a SQLServer database. The connection to SQLServer database does not close after a PHP script runs. mssql_close() does not close the connection. / Returns a link identifier for the SQL Server database on success. ‘False’ on error.
mssql_query ( string query [, resource link_identifier [, int batch_size]] ) / Runs a SQL query (SELECT statement) in the SQLServer database. / Returns a SQL Server database result resource on success. ‘TRUE’ if no rows are returned. ‘FALSE’ if an error is generated.
mssql_result ( resource result, int row, mixed field ) / Retrieves the result data of a column in a row. Parameter row specifies the row number (0-based). Parameter field specifies the field as a column name or column index. Column name may be specified as tablename.fieldname. / String of data
mssql_num_rows ( resource result ) / Returns the number of rows in a result set. / int
mssql_num_fields ( resource result ) / Returns a number of fields in a result set. / int
mssql_fetch_array ( resource result [, int result_type] ) / Retrieves a result row as an associative array (result_type specified as MSQL_ASSOC), a numeric array (result_type MSQL_NUM), or both (result_type MSQL_BOTH). An associative array field may be accessed by using the field name. A numeric array field may be accessed by using the field index. / array
mssql_fetch_row ( resource result ) / Retrieves a row as an enumerated array. Column values may be retrieved with column index (0-based). / Array corresponding to the fetched row or
‘FALSE’ if no more rows.
mssql_fetch_field ( resource result [, int field_offset] ) / Returns an object containing field information.
Object has properties name (field name), column_source (table from which column was taken), max_length (maximum length of column), numeric (1 if column is numeric), and type (column type). / object
mssql_fetch_object ( resource result ) / Retrieves a row as an object.
Field data must be accessed by field name. / Object with properties corresponding to fetched row. ‘FALSE’ if an error.
mssql_field_name ( resource result [, int offset] ) / Returns a field name. / string
mssql_field_length ( resource result [, int offset] ) / Returns a field length. / int
mssql_field_type ( resource result [, int offset] ) / Returns the field type. / string
mssql_free_result ( resource result ) / Deallocates result resources. / boolean
mssql_free_statement ( resource statement ) / Deallocates statement resources. / boolean

Table 2.SQL Server Database Extension functions

Installing PHP in Windows Script Host

In this section, we run PHP scripts by using the ActiveScript engine. The ActiveScript engine enables you to run PHP scripts in any host that supports ActiveScript, such as Microsoft Windows Script Host, Windows Script Components, and ASP or ASP.NET. We configure PHP5 by using Windows Script Host on Microsoft Windows®.

To install Windows Script5.6

To download Windows Script5.6 for WindowsXP and Windows2000, go to the Windows Script5.6 page on the Microsoft Download Center (
To install Windows Script5.6, which includes Windows Script Host5.6, Windows Script Components, and Windows Script Runtime Version5.6, double-click the scr56en application.

To install PHP5.1

To download PHP 5.1 Windows binaries, go to the PHP5.1.6 download page on the PHP Web site ( and download the PHP5.1.6 zip package.

Note: This site is not maintained by Microsoft.

Extract the PHP zip file to an installation directory, C:/PHP for example.
Add C:/PHP, the directory in which PHP5 is installed, to the PATH System environment variable.
ActiveScript requires a php-activescript.ini PHP configuration file. Modify the php.init-recommended file in the C:/PHP directory to php-activescript.ini.
Copy the php-activescript.ini file to the C:/WINNT/system32 directory, the directory in which Windows Script Host is installed.
Enable the SQLServer database extension in the php-activescript.ini configuration file. Set the extension directory by specifying the extension_dir directive as follows:

extension_dir = "C:\PHP\ext"

Activate theSQL Server database extension in the php-activescript.ini configuration file by removing the ; (semicolon) from the line that specifies the DLL for the SQL Server database extension. The line should be as follows:

extension=php_mssql.dll

To install ActiveScript engine

Go to the PHP5.1.6 download page on the PHP Web site ( and download theCollection of PECL modules for PHP 5.1.6.

Extract the pecl-5.1.6-Win32.zip file to a directory.
Copy php5activescript.dll, the DLL for ActiveScript, to the C:/PHP directory.
To register the ActiveScript DLL, in a command-line window, use the cd (change directory) command to change to the c:/PHP directory as in the following command. This directory contains the ActiveScript DLL.

>cd C:/PHP

C:/PHP>regsvr32 php5activescript.dll

To test to make sure that ActiveScript engine is installed

Create a Web script file and name it test.wsf.

Add the following script to test.wsf. A PHP script is specified with the language attribute in the <script/> element, which is set to PHPScript.

$WScript->Echo("ActiveScript Installed");

</script>

</job>

Double click the test.wsf file. This displays the message “ActiveScript Installed.”

The PHP script to obtain a connection with the SQL Server2005 database is run as a Windows Script file, which is a file with a .wsf suffix. A Windows Script file consists of a <job id=””> </job> element. The PHP script is set within the <script language=”PHPScript”> </script> element. Creating the Windows Script file that is used to obtain a connection with the SQL Server 2005 Express database is covered in Creating a Connection later in this paper.

Installing SQL Server 2005 Express

This section tells you how to install SQLServer2005 Express Edition. Before we install the SQLServer2005 database, we need to install some prerequisites.

To install prerequisite software

Download and install Windows Installer 3.1 from the Microsoft Download Center ( if it is not already installed.

Note: Check Add/Remove Programs to see if Windows Installer is installed. On Windows Server2003SP1 and WindowsXPSP2, Windows Installer3.0 is pre-installed.

Download and install Microsoft .NET Framework2.0. The .NETFramework2.0 version is different for 32-bit and 64-bit platforms.

The 32-bit .NET version is available at the Microsoft .NETFramework Version2.0 Redistributable Package (x86) page ( on the Microsoft Download Center.

The 64-bit .NET version is available at the Microsoft .NETFramework Version2.0 Redistributable Package (x64) page ( on the Microsoft Download Center.

To install SQLServer2005 Express Edition

Download the SQLServer2005 Express application, SQLEXPR.EXE, from the Microsoft SQLServer2005 Express Edition page ( on the Microsoft Download Center.

Note:SQLServer2005 Express supports the following operating systems: Windows2000SP4, Windows Server2003SP1, and WindowsXPSP2.

Double-click the SQLEXPR.EXE application to extract SQL Server files and install the Microsoft SQLServer2005 Setup wizard.
Accept the licensing terms and click Next.
In the Installing Prerequisites dialog box, click the Install button to install Microsoft SQL Native Client and the Microsoft SQLServer2005 Setup Support Files.
Click the Next button to start the SQLServer Installation Wizard.
Click Next to run the System Configuration Check. Click Next.
In the Registration Information dialog box, specify registration information and click Next.
In the Feature Selection dialog box, select the Database Services node and click Next.
We will install SQLServer2005 Express edition in Mixed Mode Authentication. In Mixed Mode Authentication, SQLserver handles login credentials. In the Authentication Mode dialog box, select Mixed Mode and specify an sa login password. Click Next.
In the Error and Usage Report Settings dialog box, select the check boxes if you want error reports for SQL Server 2005 and feature usage data for SQL Server 2005 to be reported automatically to Microsoft, and click Next.
In the Ready To Install dialog box, click Install to configure the SQLServer2005 components. Click Next.
Click Finish to conclude the SQLServer 2005 Express Edition installation.

To enable TCP/IP protocol

On the Start menu, select Programs. Select Microsoft SQLServer2005, Configuration Tools, SQLServer Configuration Manager to start SQLServer Configuration Manager.

In SQLServer Configuration Manager, select SQLServer2005 Network Configuration, Protocols for SQLEXPRESS.
Right-click the TCP/IP node and select Enable as shown in Figure1.

Figure 1.Enabling TCP/IP protocol

In Administrative Tools in Control Panel, open Services. Right-click the SQLServer (SQLEXPRESS) service and select Restart to restart the SQLServer (SQLEXPRESS) service to implement the SQLServer Configuration Manager changes.

To install SQLServer Management Studio Express Edition (SSMSEE)

Use this software to manage SQLServer 2005 databases.

To download the software, go to the SQLServer2005 Express Edition Web site ( and select Download SQLServer Management Studio Express. Follow the instructions.

Double-click the SQLServer2005_SSMSEE application to install SQLServer Management Studio.

To create a new user in SQLServer Management Studio Express Edition (SSMSEE)

On the Start menu, select Programs. Select Microsoft SQLServer 2005, SQLServer Management Studio Express to start Microsoft SQL Server Management Studio Express.
Connect by using the sa user login.
Select Security and then the Logins node. Right-click the Logins node and select New Login.
In the Login-New dialog box, specify a Login name (sqlserver, for example). Select SQL Server Authentication and specify a password. Deselect the password check boxes and select tempdb for the Default database. Tempdb is the database instance that we shall create a table in.
Select the Server Roles page. Select the sysadmin check box and click OK. A new SQL Server 2005 user is added.

Creating a Connection

In this section we create a connection with the SQLServer2005 database by using the PHP database extensionfor SQL Server databases. To do this, run a PHP script in a Windows Script host file.

To connect to the SQLServer2005 database by using PHP

Create a Windows Script file and name it sqlserver.wsf.

In the .wsf file, specify a script element for PHPScript.

</script>

</job>

Define variables for server name, user name, and password. A server name is defined using the following code.

$servername='localhost,port number';

$username='sqlserver';

$password='sqlserver';

To obtain the port number from SQLServer Configuration Manager:

In the SQLServer Configuration Manager dialog box, select the node protocols for SQLEXPRESS.
Right-click the TCP/IP protocol node and select Properties as shown in Figure2.

Figure 2.TCP/IP Properties

In the TCP/IP Properties dialog box, select the IPAddresses tab. The port number is the IPALL>TCP Dynamic Ports value, as shown in Figure3.

Figure 3.TCP/IP port number

Use the mssql_connect function to establish a connection to the SQLServer2005 database.

$connection = mssql_connect($servername,$username, $password);

We shall generate a table in the tempdb database instance. Use the mssql_select_db function to select the tempdb database instance.

mssql_select_db('tempdb', $connection);

Creating a Database Table

In this section we create a database table in the tempdb database instance.

To create a database table in the tempdb database instance

Define an SQL statement to create a table as in the following code.

$sql = "CREATE TABLE Catalog(CatalogId VARCHAR(25), Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25), Title
Varchar(45), Author Varchar(25))";

Use the following mssql_query function to run the SQL statement.

mssql_query($sql);

A database table is created.

Define an SQL statement to add a table row.

$sql = "INSERT INTO Catalog VALUES('catalog1', 'MSDN Magazine', 'MSDN', 'January 2006', 'Create Reusable Project And Item
Templates For Your Development Team', 'Matt Milner')";

Use the following mssql_query function to run the SQL statement.

mssql_query ($sql);

Add another row to database table named Catalog as in the following code.

$sql = "INSERT INTO Catalog VALUES('catalog2', 'MSDN Magazine', 'MSDN', 'January 2006', 'DataGridView', 'Nancy Michell')";

mssql_query($sql);

To check to make sure the database table has been created

Run the sqlserver.wsf script.

Start SQLServer Management Studio Express.
In the Connect to Server dialog box, specify the Server name, and select SQLServer Authentication as the Authentication mode as shown in Figure4. Specify the Login as sa and enter a password for sa. Click the Connect button.

Accessing SQL Server 2005 Databases with PHP