Get Started with PHP on Windows with SQL Server Express

    Akash Mehta
    Share

    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.

    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.

    Installing PHP and SQL Server

    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):

    Figure 1. Components to be installed

    Components to be installed


    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 I Accept 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.

    warning: Enter a Strong Password

    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.

    Your New SQL Server Installation

    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 Start menu.

    Figure 2. Start menu link

    Start menu link


    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).

    Figure 3. Connecting to a server instance

    Connecting to a server instance


    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 (Start > Run and enter services.msc) as shown in Figure 4, “SQL Server is running”, and check that SQL Server is started.

    Figure 4. SQL Server is running

    SQL Server is running


    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.

    Figure 5. The SSMS interface

    The SSMS interface


    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.

    Accessing SQL Server From PHP

    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.

    Figure 6. The IIS Manager interface

    The IIS Manager interface


    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).

    Figure 7. The Modules list

    The Modules list


    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.

    Figure 8. Handler Mappings list

    Handler Mappings list


    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”.

    Figure 9. Adding a module mapping

    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.

    Figure 10. SQL Server information from phpinfo

    SQL Server information from phpinfo


    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”.

    Figure 11. Creating a new database

    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.

    Figure 12. Creating a new database table

    Creating a new database 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”.

    Figure 13. New table created

    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”.

    Figure 14. Entering data

    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!