Accessing SQL Server 2005 Databases with PHP

Article
02/15/2007

SQL Server Technical Article

Deepak Vohra

September 2006

Applies to:
SQL Server 2005 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). PHP 5.1.6 is the latest version of PHP and includes extensions for various databases including the SQL Server database. The SQL Server database extension in PHP 5 is installed by default when PHP is installed. With the SQL Server database extension, a connection can be established with the SQL Server 2005 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. (18 printed pages)

Click here to download the Word document version of the article, AccessSQLwPHP.doc.

Introduction
Installing PHP in Windows Script Host
Installing SQL Server 2005 Express
Creating a Connection
Creating a Database Table
Obtaining a Result Set
Conclusion

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 these configuration settings are described in Table 1.

Table 1. SQL Server Database extension configuration settings

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 SQL Server 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 SQL Server 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

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 Table 2.

Table 2. SQL Server Database Extension functions

Extension Function Optional parameters are specified in []	Description	Return Type
mssql_connect ( [string servername [, string username [, string password [, bool new_link]]]] )	Establishes a SQL Server 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 SQL Server database.	'TRUE' on success 'FALSE' on failure.
mssql_close ( [resource link_identifier] )	Closes a SQL Server 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 SQL Server database. The connection to SQL Server 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 SQL Server 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

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 PHP 5 by using Windows Script Host on Microsoft Windows.

To install Windows Script 5.6:

To download Windows Script 5.6 for Windows XP and Windows 2000, go to the Windows Script 5.6 page on the Microsoft Download Center.
To install Windows Script 5.6, which includes Windows Script Host 5.6, Windows Script Components, and Windows Script Runtime Version 5.6, double-click the scr56en application.

To install PHP 5.1:

To download PHP 5.1 Windows binaries, go to the PHP 5.1.6 download page on the PHP Web site and download the PHP 5.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 PHP 5 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 SQL Server 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 the SQL 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 PHP 5.1.6 download page on the PHP Web site and download the Collection 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
```
Register the ActiveScript DLL with the following command.
```
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.
```
<job id="test">
    <script language="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 Server 2005 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 SQL Server 2005 Express Edition. Before we install the SQL Server 2005 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 Server 2003 SP1 and Windows XP SP2, Windows Installer 3.0 is pre-installed.
Download and install the Microsoft .NET Framework 2.0. The .NET Framework 2.0 version is different for 32-bit and 64-bit platforms.

The 32-bit .NET version is available at the Microsoft .NET Framework Version 2.0 Redistributable Package (x86) page on the Microsoft Download Center.

The 64-bit .NET version is available at the Microsoft .NET Framework Version 2.0 Redistributable Package (x64) page on the Microsoft Download Center.

To install SQL Server 2005 Express Edition:

Download the SQL Server 2005 Express application, SQLEXPR.EXE, from the Microsoft SQL Server 2005 Express Edition page on the Microsoft Download Center.

Note SQL Server 2005 Express supports the following operating systems: Windows 2000 SP4, Windows Server 2003 SP1, and Windows XP SP2.
Double-click the SQLEXPR.EXE application to extract SQL Server files and install the Microsoft SQL Server 2005 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 SQL Server 2005 Setup Support Files.
Click the Next button to start the SQL Server 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 SQL Server 2005 Express edition in Mixed Mode Authentication. In Mixed Mode Authentication, SQL server 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 SQL Server 2005 components. Click Next.
Click Finish to conclude the SQL Server 2005 Express Edition installation.

To enable TCP/IP protocol:

On the Start menu, select Programs. Select Microsoft SQL Server 2005/Configuration Tools/SQL Server Configuration Manager to start SQL Server Configuration Manager.
In SQL Server Configuration Manager, select SQL Server 2005 Network/Configuration/Protocols for SQLEXPRESS.
Right-click the TCP/IP node and select Enable, as shown in Figure 1.

Figure 1. Enabling TCP/IP protocol
In Administrative Tools in Control Panel, open Services. Right-click the SQL Server (SQLEXPRESS) service and select Restart to restart the SQL Server (SQLEXPRESS) service to implement the SQL Server Configuration Manager changes.

To install SQL Server Management Studio Express Edition (SSMSEE):

Use this software to manage SQL Server 2005 databases.

To download the software, go to the SQL Server 2005 Express Edition Web site and select Download SQL Server Management Studio Express. Follow the instructions.
Double-click the SQLServer2005_SSMSEE application to install SQL Server Management Studio.

To create a new user in SQL Server Management Studio Express Edition (SSMSEE):

On the Start menu, select Programs. Select Microsoft SQL Server 2005, SQL Server 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 SQL Server 2005 database by using the PHP database extension for SQL Server databases. To do this, run a PHP script in a Windows Script host file.

To connect to the SQL Server 2005 database by using PHP:

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

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

<job id="sqlserver">
    <script language="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 SQL Server Configuration Manager:
1. In the SQL Server Configuration Manager dialog box, select the node protocols for SQLEXPRESS.
2. Right-click the TCP/IP protocol node and select Properties, as shown in Figure 2.
  
  Figure 2. TCP/IP Properties
3. In the TCP/IP Properties dialog box, select the IPAddresses tab. The port number is the IPALL>TCP Dynamic Ports value, as shown in Figure 3.
Figure 3. TCP/IP port number
Use the mssql_connect function to establish a connection to the SQL Server 2005 database.
```
$connection = mssql_connect($servername,$username, $password);
```
We will 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 the 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 SQL Server Management Studio Express.
In the Connect to Server dialog box, specify the Server name, and select SQL Server Authentication as the Authentication mode, as shown in Figure 4. Specify the Login as sa and enter a password for sa. Click the Connect button.

Figure 4. Connecting to a server
In SQL Server Management Studio Express, select the Catalog table that was created by the PHP script. The table displays the data that was added in the PHP script, as shown in Figure 5.

Figure 5. Catalog table in SQL Server Management Studio Express

Obtaining a Result Set

In this section, we retrieve a result set from the SQL Server 2005 database by using the PHP SQL Server database functions.

To retrieve the result set:

Define an SQL query that selects all the rows in the Catalog table, as in the following code.
```
$sql= "SELECT * FROM Catalog";
```
To obtain a result set by using the mssql_query function, run the following SQL query.
```
$result= mssql_query($sql);
```
Use the mssql_fetch_row($result) function to iterate over the result set and retrieve a row. A row retrieved by using mssql_fetch_row consists of an enumerated array of field values. The following mssql_num_rows() function returns the number of rows in the result set.
```
for ($i = 0; $i < mssql_num_rows( $result ); $i++)
    {
        $row = mssql_fetch_row($result);
    }
```

Use the following code to output the field values in each of the rows by retrieving field values with a field index.

$WScript->Echo ("Catalog Id: ".$row[0]."\n");
    $WScript->Echo ("Journal: ".$row[1]."\n");
    $WScript->Echo ("Publisher: ".$row[2]."\n");
$WScript->Echo ("Edition: ".$row[3]."\n");
    $WScript->Echo ("Title: ".$row[4]."\n");
    $WScript->Echo ("Author: ".$row[5]."\n");

Use the following mssql_close() function to close the connection.
```
mssql_close ($connection);
```
To run the PHP script in Windows Script Host, copy the .wsf file, sqlserver.wsf, to the C:/PHP directory. The following shows the contents of the sqlserver.wsf script.

The port number in the .wsf script that you create might be different from 1879.
```
<script language="PHPScript">
```

$servername='localhost,1879'; $username='sqlserver'; $password='sqlserver';

$connection = mssql_connect($servername,$username, $password); mssql_select_db('tempdb', $connection);

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

mssql_query($sql);

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

mssql_query ($sql);

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

$sql= "SELECT * FROM Catalog"; $result= mssql_query($sql);

for ($i = 0; $i < mssql_num_rows( $result ); $i++) { $row = mssql_fetch_row($result); $WScript->Echo ("Row ".$i."\n"); $WScript->Echo ("Catalog Id: ".$row[0]."\n"); $WScript->Echo ("Journal: ".$row[1]."\n"); $WScript->Echo ("Publisher: ".$row[2]."\n"); $WScript->Echo ("Edition: ".$row[3]."\n"); $WScript->Echo ("Title: ".$row[4]."\n"); $WScript->Echo ("Author: ".$row[5]."\n"); } mssql_close ($connection); </script> </job>

Run the .wsf script with the Windows Script command-line interface cscript.
```
C:/PHP>cscript sqlserver.wsf >>sqlserver.txt
```
A connection to the SQL Server 2005 database is established and a table is created in the database. The following is the output from the sqlserver.wsf script.
```
Microsoft (R) Windows Script Host Version 5.6
```

Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. Row 0 Catalog Id: catalog1 Journal: MSDN Magazine Publisher: MSDN Edition: January 2006 Title: Create Reusable Project And Item Templates Fo Author: Matt Milner Row 1 Catalog Id: catalog2 Journal: MSDN Magazine Publisher: MSDN Edition: January 2006 Title: DataGridView Author: Nancy Michell

Conclusion

PHP scripts facilitate the development of Web applications. PHP 5 includes an extension for SQL Server databases that can be used to access a SQL Server 2005 database. To use Windows Script Host to run the PHP scripts, embed the scripts in a Windows Script file.

For more information:

SQL Server Developer Center

Share via