Windows optimize

How do I… Install, configure and integrate PHP and MySQL on Windows?

PHP and MySQL are a powerful combination when it comes to providing dynamic content to your user base. Get the step-by-step instructions for installing and configuring these products in a Microsoft Windows environment.

PHP and MySQL are a powerful combination when it comes to providing dynamic content to your user base. Over the years, these two products have grown well beyond their initial niche and now power some of the world's busiest sites. While both products are open source and initially ran on UNIX/Linux, for quite some time, both have been available for the Windows platform. In this article, I will provide you with step-by-step instructions for installing and configuring these products in a Windows environment. For some of you, the information presented here will be simplistic, but this article is designed to be a complete step-by-step guide toward accomplishing the goal in the article title.

This particular software combination is often referred to as WIMP (Windows/IIS/MySQL/PHP), taking after the popularization of the LAMP (Linux/Apache/MySQL/PHP) name for the Linux world.

My lab system for this article is running the 64-bit version of Windows Server 2003 Enterprise Edition.

Step 1: Install the Application Server role onto your Windows Server 2003 R2 system

For this example, I'm going to use IIS as my Web server. You can also opt to use PHP and MySQL with Apache in which case you don't need IIS, but I am not going this route in this example.

To install the Application Server role onto your system, from the Start menu, choose Manage Your Server. On the Manage Your Server main window, click the "Add or remove a role" option. (Figure A)

Figure A

Installing the Application Server role onto your server makes it possible to host Web sites.

Windows will search your server for existing roles and then provide you with a complete list of roles available for installation. (Figure B)

Figure B

The Application Server role includes both IIS and ASP.net.

During the server role installation process, you may be asked to insert your Windows Server CD. The files needed for this role are on CD 1.

When you're done, the Manage Your Server screen is updated to reflect the addition of the new role.

Step 2: Download PHP and MySQL

At this point, your server is ready to start serving up Web pages. But, since you haven't yet installed PHP or MySQL, the pages will be static. Before you can install PHP and MySQL, you need to download the Windows versions of the software from their respective Web sites.

MySQL

I'll start with the MySQL installation. As of this writing, MySQL 5.1 is in beta, with version 5.0 available for general use. Even though I'm installing MySQL onto a 64-bit edition of Windows, I'll download the 32-bit version of the product. After all, PHP, which will be on the same server, is not yet generally available for 64-bit hardware, which means that IIS needs to be kicked down to 32-bits on the server as well, so there's no way to stay fully 64-bit. MySQL is available in three editions: Essentials, Complete, or an edition sans installer.

  1. Essentials: The Essentials package is an MSI installation file intended to meet the needs of most users.
  2. Complete: The Complete package, as you might expect, includes more components than Essentials, including a suite of benchmarking tools.
  3. Noinstall: This package is the same as the Complete package, but does not include the Configuration Wizard or an installer and must be installed manually.

For the purposes of this article, the Essentials package is desirable. In this case, the file I'm downloading is named mysql-essential-5.0.24a-win32.msi and it's saved to my desktop.

PHP

To add dynamism to your site, using a language like PHP is a necessity, particularly if you want to pull information from a database such as MySQL. There are two different versions of PHP: a 4.x series version and a 5.x series version. If you're using scripts from an older site, you may need the 4.x series version of PHP since 5.x introduces a number of changes. Since I'm creating a brand new site, I'll stick with the latest edition of the product. As of this writing, that is version 5.1.6.

The name of the downloaded file is php-5.1.6-Win32.zip.

Step 3: Install MySQL

The installation process is quite easy, but I'll provide a screen-by-screen look at this process as well as of the configuration process.

MySQL installation

The first screen of the installer is pretty typical, and asks you to choose the type of installation you would like to perform. I'm doing a typical installation, which will install MySQL into C:\Program Files (x86)\MySQL\MySQL Server 5.0. For 32-bit operating systems, the target directory will be C:\Program Files\MySQL\MySQL Server 5.0. (Figure C)

Figure C

What kind of installation would you like to perform?

I'm not going to show the summary screen. Believe it or not, for a typical installation, this is it for MySQL! The real work comes next.

MySQL configuration

Immediately following the installation, the MySQL Configuration Wizard starts, which asks you a number of questions about how you plan to use your server? Through your responses, the MySQL Configuration Wizard builds your database server's configuration file, which contains the tuning parameters that let your server do its job.

The first step in the configuration process is to decide how you want to configure your server. Do you want to be asked a bunch of questions pertaining to server use or do you want MySQL to use a canned configuration file that you can then manipulate by hand? I'm going for the Detailed option for this example. (Figure D)

Figure D

Do you want a detailed analysis or do you want to just use a canned configuration?

In order to make appropriate decisions about how much RAM and other resources to dedicate to the database function, the MySQL Configuration Wizard asks how you intend to use the server. (Figure E) If you're dedicating the server to nothing but database work, MySQL will use as much RAM and resources as you make available. Since my test server will double as a database and Web server, I'm choosing the Server Machine selection.

Figure E

What kind of server are you using?

Again, the configuration wizard asks another question that helps the tool provide you with the best tuned system for your purposes. For my example, I've opted to use the Multifunctional Database option (Figure F), which is suitable for general use.

Figure F

How do you plan to use the database?

The next stage of the process asks you to define your InnoDB tablespace settings. I have chosen to go with the default settings. (Figure G)

Figure G

Choose your InnoDB configuration options.

As you can see in Figure H below, the next step of the process is to determine about how many concurrent connections you will have to your MySQL database. Don't underestimate this number. If you run out of connections, additional users to your site will not be able to access the database. However, don't go too crazy since additional database connections require additional resources. It's a balance.

Figure H

The number of connections to your database is an important resource consideration.

The MySQL database is generally accessed via TCP/IP. MySQL, by default, uses port 3306 for the communication. You can turn off TCP/IP or change the port number on the configuration page shown in Figure I. Also on this page, you can choose the Enable Strict Mode option, which makes MySQL less forgiving when it comes to minor syntax errors. If you're very, very new to this stuff, I recommend that you disable strict mode while you learn to ease the frustration, but , over time, work to use strict mode since it will reduce the likelihood of bad data making its way into your database.

Figure I

TCP/IP and strict mode configuration settings.

I'm not going to say much about the next screen. (Figure J) Since I'm using English only, I've selected the Standard Character Set.

Figure J

Choose your character set option.

On the next screen of the configuration wizard, you're asked if you want to install MySQL as a Windows service and whether that service should be started when the server boots. Further, decide if you would like the MySQL commands added to your Windows path. For the sake of sanity, I do have the MySQL service starting up with the operating system and have opted to amend the system path. (Figure K)

Figure K

Select environmental options.

Security of your Web server and database are critical elements. As such, you should assign a strong root password to your database server, not enable remote root access unless absolutely necessary and should not create an anonymous account. (Figure L)

Figure L

Provide some semblance of security for your database server.

Security settings are the last configuration item in the wizard. Once complete, the wizard prepares your configuration, writes the configuration to C:\Program Files [{x86)]\MySQL\MySQL Server 5.0\my.ini, and then starts the database service.

You now have a working MySQL database server. Let's move on to PHP.

Step 4: Install and configure PHP

This is probably the hardest part of the whole process. Getting PHP working with IIS 6 can sometimes be a challenge. I'll provide a complete look at how to accomplish this feat.

Install PHP

Your first task is to install PHP from the contents of the zip file that you downloaded earlier. I generally install PHP into a folder named C:\PHP5. To do this in your environment:

  1. Create a folder called PHP5 on your C: drive.
  2. Unzip the contents of the downloaded file to this location. Windows Server 2003 R2's built-in ZIP file extraction tool works great for this purpose.

Believe it or not, that's the whole "installation" for PHP 5. Now, on to something a little more difficult: configuring PHP.

Configure PHP

There are a few steps that need to be taken to get PHP working under Windows with IIS.

  1. Add the C:\PHP5 directory to your system's path variable. (Figure M) This way, your system can more easily locate the PHP DLL files it needs.
    • Choose Start and right-click My Computer.
    • From the shortcut menu, select Properties.
    • On the Properties page, select the Advanced tab.
    • Click the Environment Variables button.
    • From the list of System variables double-click the Path variable.
    • To the end of the Path variable, add ";C:\PHP". (without the quotes!)
    • Restart your server.

Figure M

Modify your system path to include the PHP directory.
  1. Copy the file C:\PHP5\php.ini-dist to C:\PHP5\php.ini. This is PHP's configuration file. The -dist version is just a sample.
  2. Now, create a system variable named "PHPRC" and assign to it the path to the php.ini file. If you opt to do this instead of creating a registry entry to achieve the same goal, make sure your PHPRC variable contains only the path to php.ini, and not the path plus the php.ini file name.
  3. Finally, in order for PHP to work with MySQL, you need to make some changes to the c:\php5\php.ini file.
    • Open c:\php5\php.ini using your favorite text editor.
    • Locate the extension_dir configuration option and make sure the line reads:
      extension_dir = "./ext"
      This tells PHP where to find additional modules to load. You need at least one module in order to make use of MySQL with PHP.
    • Locate the line that reads ";extension=php_mysql.dll" and remove the leading semicolon. In the php.ini file, the semicolon is used to comment out a configuration line. In this case, we want to uncomment the line that loads the PHP MySQL module.
    • Directly underneath the preceding line, add: "extension=php_mysqli.dll". This module enables some additional MySQL functionality in newer version of the product.
  4. From a command prompt, issue the command "iisreset" to reload IIS and PHP with the new configuration file.

Configure IIS to run in 32-bit mode (64-bit Windows only)

The official PHP Windows binaries are not yet available for a 64-bit Windows architecture, but you can still run on 64-bit Windows. However, in order to work, you need to run IIS 6 in 32-bit emulation mode. To do this, from your Windows server, do the following:

  1. Go to a command prompt.
  2. From the command prompt window, change to C:\Inetpub\Adminscripts folder. If Windows is installed on a drive other than "C:", use that drive letter instead.
  3. While in the Adminscripts folder, type the following command:
    cscript adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1
    The value "1" for Enable32bitAppOnWin64 tells Windows to run the w3svc in 32-bit mode, while a value of "0" indicates 64-bits.
  4. Type iisreset.
  5. Reboot the server.

Configure IIS

Even though PHP is configured, before you can start using it, you need to tell IIS how it is able to use the new service. Until you do this, trying to load PHP files into a Web browser from your IIS server will result in nothing but errors.

You can run PHP in one of two ways with IIS – using ISAPI, or as a CGI extension. Since the ISAPI method generally results in better performance, we'll go that route for this article. To get started, you need to add the PHP ISAPI extension to IIS.

  1. Go to Start | Administrative Tools | Internet Information Services (IIS) Manager.
  2. Select server name | Web Service Extensions.
  3. From the right pane, click the "Add a new Web service extension..." option.
  4. On the resulting screen, in the "Extension name" field, enter "PHP ISAPI".
  5. Click the Add button.
  6. Browse to the C:\PHP5\php5isapi.dll file.
  7. Click OK.
  8. Check the "Set extension status to Allowed" checkbox. (Figure N)
  9. Click OK.

Figure N

Point the new extension at the php5isapi.dll file in the C:\PHP5 folder.

Add new extension (.php)

  1. Go to Start | Administrative Tools | Internet Information Services (IIS) Manager.
  2. Right-click Web Sites and, from the resulting shortcut menu, choose Properties.
  3. On the Properties page, choose the Home Directory tab.
  4. Click the Configuration button.
  5. Click the Add button.
  6. On the Add/Edit Application Extension Mapping window, next to the Executable box, click the Browse button and locate C:\PHP5\php5isapi.dll.
  7. In the Extension field, enter ".php". (Figure O)
  8. Under the Verbs heading, choose the "Limit to" option and enter "GET,POST,HEAD" into the text box.
  9. Click OK until you're back at the IIS Manager main window.

Figure O

The PHP extension tells IIS how to handle requests for .php pages.

Also, in IIS, make sure you set the permissions for your site so that you can execute scripts:

  1. Go to Start | Administrative Tools | Internet Information Services (IIS) Manager.
  2. Right-click Web Sites and, from the resulting shortcut menu, choose Properties.
  3. On the Properties page, choose the Home Directory tab.
  4. In the Application Settings, change the "Execute permissions" option to "Scripts only". (Figure P)

Figure P

Change the settings in IIS so scripts can run.

Finally, in IIS, make sure you add a default PHP page, such as index.php. By doing this, you avoid having users need to specify the full path, including the PHP script name to a particular folder on your Web server. Instead, if the user visits a directory and a file named index.php exists, IIS will load it automatically.

  1. Go to Start | Administrative Tools | Internet Information Services (IIS) Manager.
  2. Right-click Web Sites and, from the resulting shortcut menu, choose Properties.
  3. On the Properties page, choose the Documents tab.
  4. Click the Add button.
  5. On the Add Content Page screen, type "index.php". (Figure Q)
  6. Click OK.
  7. Click OK again. If you get a message about a Child Node also being affected, click OK.

Figure Q

The index page will load if none of the pages above it exist.

Test PHP

Now, it's time to test PHP to make sure it's able to serve up dynamic pages using IIS.

  1. In your IIS Web root (normally C:\Inetpub\wwwroot), create a file named phpinfo.php.
  2. Into this file, paste a single line of text:
    <? phpinfo(); ?>
    This is actually a complete PHP script and is used to spit out the complete configuration of your PHP-based Web server.
  3. Now, from any machine, visit http://{your server}/phpinfo.php or, from the server, visit http://127.0.0.1/phpinfo.php.
  4. If you get an error, something is not configured correctly.
  5. You should get a screen similar to the one shown below. (Figure R)
  6. Make sure that there is a section in the phpinfo for MySQL. If not, go back and make sure you correctly modified the php.ini file to load the MySQL modules.

Figure R

The PHP test page includes a complete PHP configuration overview.

Step 5: Test MySQL Web connection

To see if the installation was really successful, you need a sample database to test against. For this article, I'll use the MySQL command line tools to create a small sample database whose sole purpose is to make sure the PHP/MySQL connection is working.

To create this sample database:

  1. You first need to create the actual database. This is accomplished with the following command:
    mysqladmin -uroot –p******* create sample
    Note: If you get an error here, make sure the password is correct (the –u parameter specifies the user name to use, and the –p parameter is the password you assigned back in the MySQL configuration step).
  2. Now, on to creating the table and adding some data. Again, from the command line, execute the following command:
    • C:>mysql -uroot -p******* sample
      Replace the "*'s" here with the same password you used before. The remaining steps take place from within the MySQL command shell.
    • At the MySQL prompt, enter
      CREATE TABLE sample_table ( FirstName varchar(30), MiddleName varchar(30), LastName varchar(30) ); mysql> INSERT into sample_table Values ( "Scott", "David", "Lowe") ; mysql> INSERT into sample_table Values ( "Ralph", "John", "Malph") ;
    • Note: that the semicolons in the preceding steps are very important.
    • Using the root user account to access the database from the Web server is a major security risk. As such, you should create a separate user account for this purpose. Type the following command at the MySQL shell:
      GRANT ALL ON sample.* to Webuser@localhost identified by 'password';
    • Type 'quit' to exit the MySQL shell.

Next, you need a PHP script on your Web server that can read the contents of this new database. If this part is successful, you have a working IIS/PHP/MySQL system!

  1. In your Web root directory (normally C:\inetpub\wwwroot), create a file named dbtest.php.
  2. Into this file, paste the following PHP code:
<?
$Username = "Webuser";
$Password = "password";
$Database = "sample";
$Hostname = "localhost";
$MySQLConnection = mysql_connect($Hostname, $Username, $Password) or die("Unable to connect to MySQL Database!!");
$MySQLSelectedDB = mysql_select_db($Database, $MySQLConnection) or die("Could not Set the Database!!");
$MySQLRecordSet = mysql_query("SELECT * FROM sample_table");
print "First Name - " . "Middle Name - " . "Last Name<br>";
while ($MyRow = mysql_fetch_array($MySQLRecordSet, MYSQL_ASSOC))
{
print $MyRow['FirstName'] . " - " . $MyRow['MiddleName'] . " - " . $MyRow['LastName'] . "<br>";
}
mysql_close($MySQLConnection);
? >

Save this file and then point your Web browser at http://{your server}/dbtest.php. You should get something similar to Figure S below.

Figure S

If you see the three names, your configuration is successful.

Better idea

I never said the installation and configuration process would be easy! However, if you follow these steps, you should have a working MySQL/PHP system in fairly little time. If you have comments or suggestions to make this process easier, I'd love to hear them and, if I get enough good ideas, we will publish them in another article.

0 comments