Get Started with PHP on Windows with SQL Server Express

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.

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 Start 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 Start 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 OK. 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 New Database… 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 New Table… 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 Save 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 Edit Top 200 Rows, 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!