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

2. 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.
3. 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.
4. 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.
5. 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.