In a recent article, we saw how easy it is to get a web server up and running on Windows with PHP, IIS, and SQL Server Express using the Web Platform Installer. Now. let’s look at how we can use those tools together to build a simple web app.
Don’t forget to check out the quiz at the end, sponsored by Microsoft, and possibly win a free copy of SQL Server Developer edition. There’s a limited number available so hurry up and dive right in.
Key Takeaways
- Ease of Setup: Utilizing the Microsoft Web Platform Installer simplifies the process of setting up a PHP and SQL Server development environment on Windows, integrating various components like IIS, PHP, and SQL Server Express effortlessly.
- Installation Requirements: The tutorial is applicable for users with systems running Windows XP SP2 and above, highlighting the necessity of having IIS installed and configured before beginning the installation of PHP and SQL Server.
- PHP and SQL Server Integration: After installing PHP, the article guides through verifying the installation and configuring IIS to run PHP applications using FastCGI, ensuring PHP is correctly set up to interact with SQL Server.
- Database Creation and Management: Demonstrates creating and managing a SQL Server database using SQL Server Management Studio, including setting up tables and inserting data, which is essential for developing dynamic data-driven web applications.
- PHP Connectivity with SQL Server: Provides detailed steps on connecting to SQL Server from PHP using the sqlsrv PHP extension, including code snippets for executing queries and handling data retrieval, which are crucial for building PHP applications that interact with SQL Server databases.
Getting Started
The Microsoft Web Platform Installer, or WPI, is a unified installer for web applications and related software. WPI can install PHP, Microsoft SQL Server, the .NET framework and various open source applications, to name just a few! The installer also configures Internet Information Services (the web server included with Windows) for these components, so getting started is easy. But before we begin building our web app, we’ll use the WPI to set up our development environment.
This article assumes you have a basic understanding of PHP and relational databases—if you can write PHP and SQL code to manipulate database records, you’re good to go. We’ll move through the PHP fairly quickly, in order to focus on the power and simplicity of the platform.
First, visit the WPI download page and get a copy of the installer. You’ll need a system running Windows XP SP2+, Server 2003 SP1+, Server 2008, Vista SP1, or Windows 7; I used Vista SP1 for this tutorial. The installer itself is only 1MB, but be prepared for a couple of hundred megabytes of data transfer when the installer downloads the components of the platform.
Before we start installing, check that your system has IIS installed. IIS is bundled with most editions of Windows, however it is not included in Home editions. Check Windows features (note, though, that names vary) under Programs and Features in the Control Panel to verify that IIS is installed; if not, simply install IIS before proceeding.
Open the Web Platform Installer and have a look through the options available. You may recognise some of the open source applications, like WordPress, under the Applications tab; the installer can set up these applications and all the platform software they require with minimal fuss.
Switch to the Web Platform tab to start installing the components we’ll need. Use the Customize links to select components. At a minimum, we’ll need to select Web Server > Application Development > CGI, Frameworks and Runtimes > PHP > PHP 5.x, and everything under Database. If you followed the previous article, The Easy Way to Install PHP on Windows, some of these components will already be installed. Figure 1, “Components to be installed” shows the components my setup used (plus SQL Server Management Studio):
Note that SQL Server 2008 Native Client is listed as a dependency for the PHP driver. The native client software provides ODBC functionality for the PHP driver and is required even when working with SQL Server 2005 instances.
Click and, during the install procedure, you’ll be asked to configure the authentication for your new SQL Server setup. In this tutorial we’ll use SQL Server Authentication, so select Mixed Mode Authentication, instead of Windows Integrated Authentication; note, though, that both are fine for development. We’ll keep the default username of sa, but you can choose any password you want.
Even though WPI neglects to warn you about it, the installation of SQL Server Express will not complete if you enter a weak password. The WPI troubleshooting page has the details you’ll need to ensure your password is strong.
Go ahead and let the WPI download and install these items. If you encounter any challenges, visit the WPI troubleshooting page.
Once the platform installer is finished, SQL Server should be up and running on your machine. Start up the SQL Server Management Studio using the shortcut the installer will have placed in your menu.
You’ll be prompted to connect to an SQL Server instance as shown in Figure 3, “Connecting to a server instance”. To do so, use the username and password combination you set up earlier (or your Windows login credentials, if you chose Windows authentication).
Note that BUSLN1 is my computer name, and SQLEXPRESS is the default instance name for the SQL Server (Express Edition) installed by the WPI. This should be filled in already, but you may need to choose SQL Server Authentication from the Authentication methods list. If you can’t connect, check that SQL Server is running: open up the Services console ( > and enter services.msc) as shown in Figure 4, “SQL Server is running”, and check that SQL Server is started.
Once you connect, the SQL Server Management Studio (SSMS), shown in Figure 5, “The SSMS interface”, will display your databases as well as other pieces of server information.
SSMS allows you to configure, manage, and administer your SQL Server instance, as well as manage its databases. For more information about SSMS, see Introducing SQL Server Management Studio on MSDN.
If you installed the Database Manager for IIS like I did, you can also verify that your database server is up and running using the Database Manager within IIS Manager (it’s under the Management section on the front page). However, the Database Manager expects to connect to a particular database, so you’ll have to create one using SSMS first.
WPI has installed a recent release of PHP and configured IIS for it. Let’s check if IIS is up and running. The easiest way to do this is to visit http://localhost/ and check that the default IIS page displays in your browser.
Load up the IIS Manager shown in Figure 6, “The IIS Manager interface”—it’s under Administrative Tools in Control Panel, but it should also appear if you type IIS into your menu search bar.
Here we can configure and administer our IIS server, as well as managing the hosted websites. Since IIS is included with most editions of Windows, the WPI has simply added and enabled certain required components, such as the CGI component. The options you see may not be exactly the same as those above, but as long as you have CGI enabled you should be ready to go.
If you couldn’t access your IIS server at http://localhost/ earlier, click on your web server (the top-level entry on the left pane, most likely labelled with your computer name; for example, it’s BUSLN1 for me). Click in the Actions pane on the right. Then select the Default Web Site and use the Actions pane on the right to ensure that the web site is also running.
Now let’s make sure PHP was installed correctly. Our server will be executing PHP via the FastCGI module, the recommended method for IIS. First, select the top entry for your server under Connections on the left, and double-click on Modules in the middle window (it’s one of the components grouped under IIS).
You should see FastCgiModule in this list, as in Figure 7, “The Modules list”. If not, click Configure Native Modules on the right, and enable the FastCgiModule. If the FastCgiModule isn’t an option, click Register, name the module FastCgiModule, select %windir%System32inetsrviisfcgi.dll as the path to the module, then enable it.
Now select your Default Web Site, open up the Modules section, and once again ensure that FastCgiModule is in the list. (If not, use the previous instructions to put it there.)
Finally, with Default Web Site selected, double-click Handler Mappings.
The WPI should have added an entry titled PHP_via_FastCgi, as in Figure 8, “Handler Mappings list”, when it installed PHP. However, if this option doesn’t appear in the list, click Add Module Mapping on the right, enter *.php as the request path, select FastCgiModule from the module list, and select your php-cgi.exe binary for the path—this should be in C:Program FilesPHPphp-cgi.exe or something similar. (Remember to use quotes around the executable path, because of that space in Program Files.) Select a name like PHP via FastCgi, and click . IIS will prompt you to create a FastCgi application for PHP, as shown in Figure 9, “Adding a module mapping”.
Now we’re ready to run our PHP applications under IIS. Your default website will have a document root similar to C:inetpubwwwroot—you can change this using the IIS Manager. For now, remove the default IIS files in this folder and create an index.php file. Open up your favourite text editor and add the following:
<?phpphpinfo();exit;
Switch to your web browser and visit http://localhost/ again ( or http://localhost/index.php if you didn’t remove the IIS splash page). Search for sqlsrv, which is the native SQL Server driver for PHP that’s provided by Microsoft. Skip the first entry and you should find that the driver is installed and enabled, as Figure 10, “SQL Server information from phpinfo” shows.
Now let’s create a database and retrieve it using PHP. Switch back to the SQL Server Management Studio, connect to your database server if you haven’t already, and create a new database. Simply right-click on Databases, and select to display the New Database dialog shown in Figure 11, “Creating a new database”.
You’ll see a new entry appear for your new database under Databases. Expand the entry for your database, right-click on Tables, and select to create a new table.
You can change the table name using the Properties pane on the right, enter the schema information (here we just have an ID and a name field in a table called people), and set your id field to a primary key by right-clicking the Column Name field. For the equivalent of an auto increment field, select ID as the Identity Column under Table Designer on the right. This isn’t entirely necessary, but I figured veteran MySQL-ers would look for this option.
Finally, click the button (or hit Ctrl+S) to create your table. You’ll see it appear in your Object Explorer on the left, as in Figure 13, “New table created”.
Right-click dbo.people on the left, click , and enter some dummy data into a row, as shown in Figure 14, “Entering data”.
Now go back to your text editor, open the index.php file we worked on before, and update it with the following:
<?php$server = "BUSLN1\SQLEXPRESS";$options = array( "UID" => "sa", "PWD" => "password", "Database" => "test");$conn = sqlsrv_connect($server, $options);if ($conn === false) die("<pre>".print_r(sqlsrv_errors(), true));echo "Successfully connected!";sqlsrv_close($conn);
You’ll definitely need to change the options in italics—these should reflect the configuration information you previously entered in the connection dialog of SQL Server Management Studio. Also, we use \ instead of in the server name because is the escaping character in PHP. Change $server to reflect your computer name, and replace the “PWD” array value with the password you chose. If you selected Windows Authentication when you installed SQL Server, leave out the “UID” and “PWD” lines; SQL Server will use your current Windows account to authenticate with the database server.
This code attempts to connect to the local SQL Server instance using the native SQL Server driver. The API is slightly different from the native mysql, mysqli, or pgsql drivers for PHP, but it’s easy to pick up. For further details on the driver, see the MSDN hosted documentation.
Load up http://localhost/index.php and you should see the message “Successfully connected!” If not, check that your SQL Server is running and that these credentials work in SQL Server Management Studio; the PHP driver should give you the same result (including the error message wording). For further details on connecting to SQL Server, see the sqlsrv_connect documentation.
Now let’s retrieve the row we created earlier. Head back to your text editor and add this code after the "Successfully connected!" line:
$sql = "SELECT * FROM dbo.people";$query = sqlsrv_query($conn, $sql);if ($query === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row = sqlsrv_fetch_array($query)){ echo "<p>Hello, $row[name]!</p>";}sqlsrv_free_stmt($query);This will retrieve all records from our people table, and output the value in the name field of every record. Finally, we call sqlsrv_free_stmt to free up the resources associated with the query statement. This last call is optional, but can make a difference for complex prepared statements and large result sets.
Visit http://localhost/index.php in your web browser; you should see an entry for every record in your people table. Add a few more rows in SQL Server Management Studio and refresh the page to get a new result set. Now that we know how to access SQL Server from PHP, we’re ready to build our web application!
Frequently Asked Questions (FAQs) about SQL Server and PHP
How can I connect to SQL Server using PHP?
To connect to SQL Server using PHP, you need to use the sqlsrv_connect() function. This function requires two parameters: the server name and an array of connection options. The server name is typically the name of the computer where the SQL Server is installed. The connection options array can include items like the database name, username, and password. Here’s an example of how to use this function:$serverName = "server_name";$connectionOptions = array(
"Database" => "db_name",
"Uid" => "username",
"PWD" => "password");$conn = sqlsrv_connect($serverName, $connectionOptions);
What are the common errors when connecting to SQL Server with PHP and how can I troubleshoot them?
There are several common errors you might encounter when connecting to SQL Server with PHP. One of the most common is “Unable to connect to server”. This usually means that the server name or connection options are incorrect. Double-check these values and try again. If you’re still having trouble, you can use the sqlsrv_errors() function to get more detailed error information.
How can I execute SQL queries using PHP?
To execute SQL queries using PHP, you can use the sqlsrv_query() function. This function requires two parameters: the connection resource and the SQL query string. Here’s an example of how to use this function:$sql = "SELECT * FROM table_name";$query = sqlsrv_query($conn, $sql);
How can I fetch data from SQL Server using PHP?
After executing a SQL query using the sqlsrv_query() function, you can fetch the resulting data using the sqlsrv_fetch_array() function. This function requires the query resource as a parameter and returns an associative array of the fetched row. Here’s an example:while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) {
echo $row['column_name'];}
How can I handle transactions in SQL Server using PHP?
Transactions in SQL Server can be handled using the sqlsrv_begin_transaction(), sqlsrv_commit(), and sqlsrv_rollback() functions. These functions allow you to start a new transaction, commit a transaction, and rollback a transaction, respectively. Here’s an example:sqlsrv_begin_transaction($conn);$sql = "INSERT INTO table_name (column1, column2) VALUES (?, ?)";$params = array($value1, $value2);if(sqlsrv_query($conn, $sql, $params)) {
sqlsrv_commit($conn);} else {
sqlsrv_rollback($conn);}
How can I close a connection to SQL Server using PHP?
To close a connection to SQL Server, you can use the sqlsrv_close() function. This function requires the connection resource as a parameter. Here’s an example:sqlsrv_close($conn);
How can I handle errors in SQL Server using PHP?
Errors in SQL Server can be handled using the sqlsrv_errors() function. This function returns an array of error information about the last operation performed.
How can I use prepared statements in SQL Server using PHP?
Prepared statements can be used in SQL Server using the sqlsrv_prepare() and sqlsrv_execute() functions. These functions allow you to prepare a SQL query and execute it, respectively. Here’s an example:$sql = "INSERT INTO table_name (column1, column2) VALUES (?, ?)";$params = array($value1, $value2);$stmt = sqlsrv_prepare($conn, $sql, $params);if(sqlsrv_execute($stmt)) {
echo "Row inserted.";} else {
echo "Row insertion failed.";}
How can I retrieve the last inserted ID in SQL Server using PHP?
To retrieve the last inserted ID in SQL Server, you can use the SQL query “SELECT SCOPE_IDENTITY()”. Here’s an example:$sql = "INSERT INTO table_name (column1, column2) VALUES (?, ?); SELECT SCOPE_IDENTITY()";$params = array($value1, $value2);$stmt = sqlsrv_query($conn, $sql, $params);if($stmt) {
sqlsrv_next_result($stmt);
sqlsrv_fetch($stmt);
echo "Last inserted ID is ".sqlsrv_get_field($stmt, 0);} else {
echo "Row insertion failed.";}
How can I update data in SQL Server using PHP?
To update data in SQL Server, you can use a SQL UPDATE query. Here’s an example:$sql = "UPDATE table_name SET column1 = ? WHERE column2 = ?";$params = array($value1, $value2);if(sqlsrv_query($conn, $sql, $params)) {
echo "Row updated.";} else {
echo "Row update failed.";}
Akash MehtaAkash Mehta is a web developer and freelance writer specializing in web application development.
