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.
- Essentials: The
Essentials package is an MSI installation file intended to meet the needs
of most users. - Complete: The Complete
package, as you might expect, includes more components than Essentials,
including a suite of benchmarking tools. - 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:
- Create a folder called PHP5 on your C:
drive. - 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.
- 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. |
- 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. - 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. - 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. - 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:
- Go to a command prompt.
- 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. - 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. - Type iisreset.
- 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.
- Go to Start | Administrative Tools | Internet
Information Services (IIS) Manager. - Select server name | Web Service Extensions.
- From the right pane, click the “Add a new Web
service extension…” option. - On the resulting screen, in the “Extension name”
field, enter “PHP ISAPI”. - Click the Add button.
- Browse to the C:\PHP5\php5isapi.dll
file. - Click OK.
- Check the “Set extension status to Allowed”
checkbox. (Figure N) - Click OK.
Figure N |
![]() |
Point the new extension at the php5isapi.dll file in the C:\PHP5 folder. |
Add new extension (.php)
- Go to Start | Administrative Tools | Internet
Information Services (IIS) Manager. - Right-click Web Sites and, from the resulting shortcut
menu, choose Properties. - On the Properties page, choose the Home Directory tab.
- Click the Configuration button.
- Click the Add button.
- On the Add/Edit Application Extension Mapping window,
next to the Executable box, click the Browse button and locate C:\PHP5\php5isapi.dll. - In the Extension field, enter “.php”. (Figure O)
- Under the Verbs heading, choose the “Limit to”
option and enter “GET,POST,HEAD” into
the text box. - 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:
- Go to Start | Administrative Tools | Internet
Information Services (IIS) Manager. - Right-click Web Sites and, from the resulting shortcut
menu, choose Properties. - On the Properties page, choose the Home Directory tab.
- 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.
- Go to Start | Administrative Tools | Internet
Information Services (IIS) Manager. - Right-click Web Sites and, from the resulting shortcut
menu, choose Properties. - On the Properties page, choose the Documents tab.
- Click the Add button.
- On the Add Content Page screen, type “index.php”.
(Figure Q) - Click OK.
- 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.
- In your IIS Web root (normally C:\Inetpub\wwwroot),
create a file named phpinfo.php. - 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. - Now, from any machine, visit http://{your server}/phpinfo.php or, from the server, visit http://127.0.0.1/phpinfo.php.
- If you get an error, something is not configured
correctly. - You should get a screen similar to the one shown below.
(Figure R) - 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:
- 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). - 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!
- In your Web root directory (normally C:\inetpub\wwwroot), create a file
named dbtest.php. - 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.