Get Started with PHP on Windows with SQL Server Express
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.
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 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 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”.
button (or hitRight-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!