Together, PHP & MySQL form the most widely used open source database and scripting technologies on the Web today. Part 1 of our series of excerpts from this SitePoint title covers PHP & MySQL installation and your first PHP script.
Build Your Own Database Driven Website Using PHP & MySQL
is a hands-on guide to learning all the tools, principles and
techniques needed to build a fully functional database-driven
Website using PHP & MySQL.
This book covers everything from installing PHP & MySQL under
Windows or Linux, through to building a live Web-based content
It also covers more advanced topics such as the storage of binary
data in MySQL, and cookies and sessions in PHP, and comes
complete with a set of PHP and MySQL reference tables that
include PHP and MySQL syntax, functions, column types and more.
This book not only provides easy access to all the code samples
demonstrated, but, more importantly, it leaves you with the
confidence and know-how to adapt the principles and techniques to
your own Web design projects.
What Will You Learn?
This book will show you how to:
- Build a working Content Management System
- Add, edit & delete Web content without using HTML
- Setup automatic backups of your database
- Increase your database security
- Optimize the loading time of your Website
- Build an ecommerce shopping cart
- Automatically send email on triggered events
- Handle file uploads using HTML forms
- Build a Web-based file repository or photo gallery
- Utilize sessions and cookies to track site visitors
- Master MySQL Database Administration
- And a whole lot more
Who Should Read This Book?
Build Your Own Database Driven Website Using PHP & MySQL
is an essential resource for budding Web Developers. If you want
to go beyond the limitations of a static site and learn to build
dynamic, database-driven Websites, this book will show you how.
If you've never built a database-driven Website, this book was
written for you. If you have, it's an extremely handy desk
reference and an essential addition to your technical library.
Welcome to the Show
Hi there, and welcome to the first in SitePoint.com's four-part
series on building a database-driven Web site! Over the course of
this series, it will be my job to guide you as you take your
site design. Together we'll explore what it takes to build the
kind of large, content-driven sites that are so successful today,
but which can be a real headache to maintain if they aren't done
Before we get started, you need to gather together the tools
you'll need for the job. In this first chapter, I'll guide you as
you download and set up the two software packages you'll need:
PHP and MySQL.
PHP is a server-side scripting language. You can think of
it as a "plug-in" for your Web server that will allow it to do
more than just send plain Web pages when browsers request them.
With PHP installed, your Web server will be able to read a new
kind of file (called a PHP script) that can do things like
retrieve up-to-the-minute information from a database and insert
it into a Web page before sending it to the browser that
requested it. PHP is completely free to download and use.
To retrieve information from a database, you first need to have a
database. That's where MySQL comes in. MySQL is a relational
database management system, or RDBMS. Exactly what role it plays
and how it works we'll get into later, but basically it's a
software package that is very good at the organization and
management of large amounts of information. MySQL also makes that
information really easy to access with server-side scripting
languages like PHP. MySQL is released under the GNU General
Public License (GPL), and is thus free for most uses on all of
the platforms it supports. This includes most Unix-based
platforms, like Linux and even Mac OS X, as well as Windows
If you're lucky, your current Web host may already have installed
MySQL and PHP on your Web server for you. If that's the case,
much of this chapter will not apply to you, and you can skip
straight to the section entitled "If Your Web Host Provides PHP
and MySQL" to make sure your setup is ship shape.
Everything we'll discuss in this series may be done on a Windows-
or Unix-based server. The installation procedure will differ in
accordance with the type of server you have at your disposal. The
following two sections deal with installation on a Windows-based
Web server, and installation under Linux (and other Unix-based
platforms), respectively. Unless you're especially curious, you
should only need to read the section that applies to you.
As I mentioned above, MySQL may be downloaded free of charge.
Simply proceed to
http://www.mysql.com/downloads/ and choose the recommended
stable release (as of this writing, it is MySQL 3.23). Under the
heading of Standard binary (tarball) distributions (which
basically means the program doesn't need to be compiled, and is
ready to run once you download it), find and click Windows
95/98/NT/2000 (Intel). If you're on a high-speed connection,
you'll probably want to check out one of the download mirrors
http://www.mysql.com/downloads/mirrors.html to get a
reasonable download speed. After downloading the file (it's about
12MB as of this writing), unzip it and run the setup.exe program
Once installed, MySQL is ready to roll (barring a couple of
configuration tasks that we'll look at shortly), except for one
minor issue that only affects you if you're running Windows
NT/2000/XP. If you use any of those operating systems, find a
my-example.cnf in the directory to which
you just installed MySQL. Copy it to the root of your C: drive
and rename it to
my.cnf. If you don't like the idea
of a MySQL configuration file sitting in the root of your C:
drive, you can instead name it
my.ini and put it in
your Windows directory (e.g.
D:\WINNT if Windows
2000 is installed on drive D:). Whichever you choose, open the
file in WordPad (Notepad is likely to display it incorrectly) and
look for the following line:
# basedir = d:/mysql/
Uncomment this line by removing the '
#' symbol at
the start, and change the path to point to your MySQL
installation directory, using slashes (
/) instead of
\). For instance, I changed the line on
my system to read as follows:
basedir = d:/Program Files/mysql/
With that change made, save the file and close WordPad. MySQL
will now run on your Windows NT/2000/XP system! If you're using
Windows 95/98/ME, this step is not necessary -- MySQL will run
just fine as-installed.
Just like your Web server, MySQL is a program that should be run
in the background so that it may respond to requests for
information at any time. The server program may be found in the
"bin" subfolder of the folder into which you installed MySQL. To
make things complicated, however, there are actually several
versions of the MySQL server to choose from:
- mysqld.exe This is the basic version of MySQL if you
run Windows 95, 98, or ME. It includes support for all advanced
features, and includes debug code to provide additional
information in the case of a crash (if your system is set up to
debug programs). As a result of this code, however, the server
might run a little slow, and I've generally found that MySQL is
so stable that crashes aren't really a concern.
- mysqld-opt.exe This version of the server lacks a few
of the advanced features of the basic server, and does not
include the debug code. It's optimized to run quickly on today's
processors. For beginners, the advanced features are not a big
concern. You certainly won't be using them while you complete the
tasks in this series. This is the version of choice for beginners
running Windows 95, 98, or ME.
- mysqld-nt.exe This version of the server is compiled
and optimized like
mysqld-opt, but is designed to
run under Windows NT/2000/XP as a service. If you're using any of
those operating systems, this is probably the server for
- mysqld-max.exe This version is like
opt, but contains advanced features that support
- mysqld-max-nt.exe This version's similar to
mysqld-nt, but has advanced features that support
All these versions were installed for you in the
directory. If you're running on Win98x/ME I recommend sticking
mysql-opt for now -- move to
max if you ever need the advanced features. On Windows
mysqld-nt is my recommendation. Upgrade
mysqld-max-nt when you need more advanced
Starting MySQL is also a little different under WinNT/2000/XP,
but this time let's start with the procedure for Win95/98/ME.
Open an MS-DOS Command Prompt and proceed to the MySQL
bin directory, then run your chosen server program:
Don't be surprised when you receive another command prompt. This
command launches the server program so that it runs in the
background, even after you close the command prompt. If you press
Ctrl-Alt-Del to pull up the task list, you should see the MySQL
server listed as one of the tasks that's active on your system.
To ensure that the server is started whenever Windows starts, you
might want to create a shortcut to the program and put it in your
Startup folder. This is just like creating a shortcut to any
other program on your system.
On WinNT/2000/XP, you must install MySQL as a system service.
Fortunately, this is very easy to do. Simply open a Command
Prompt and run your chosen server program with the
C:\mysql\bin> mysqld-nt -install
This will install MySQL as a service that will be started the
next time you reboot Windows. To manually start MySQL without
having to reboot, just type this command (which can be run from
C:\> net start mysql
To verify that the MySQL server is running properly, press Ctrl-
Alt-Del and open the Task List. If all is well, the server
program should be listed on the Processes tab.
The next step is to install PHP. At the time of this writing, PHP
4.x has become well-established as the version of choice;
however, some old servers still use PHP 3.x (usually because
nobody has bothered to update it). I'll cover the installation of
PHP4 here, so be aware that if you're still working with PHP3
there may be some minor differences.
Download PHP for free from
http://www.php.net/ (or one of its mirrors listed at
http://www.php.net/mirrors.php). You'll want the Win32
Binaries package, and be sure to grab the version that
includes both the CGI binary and the server API
versions if you have a choice.
In addition to PHP itself, you will need a Web server such
as Internet Information Services (IIS), Apache, Sambar or
OmniHTTPD. PHP was designed to run as a plug-in for existing Web
server software. To test dynamic Web pages with PHP, you'll need
to equip your own computer with Web server software, so that PHP
has something to plug into. If you have Windows 2000/XP, then
install IIS (if it's not already on your system): open Control
Panel, Add/Remove Programs, Add/Remove Windows
Components, and select IIS from the list of components. If
you're not lucky enough to have IIS at your disposal, you can
instead use a free Web server like Apache. I'll give instructions
for both options in detail.
First, whether you have IIS or not, complete these steps:
Unzip the file you downloaded into a directory of your
choice. I recommend
C:\PHP and will refer to this
directory from here onward, but feel free to choose another
directory if you like.
Find the file called
php4ts.dll in the PHP
folder and copy it to the
System32 subfolder of your
Windows folder (e.g.
Find the file called
php.ini-dist in the PHP
folder and copy it to your Windows folder. Once there, rename it
php.ini file in your favorite text
editor (use WordPad if Notepad doesn't display the file
properly). It's a large file with a lot of confusing options, but
look for a line that begins with
set it so that it points to the
of your PHP folder:
extension_dir = C:\PHP\extensions
A little further down, look for a line that starts with
session.save_path and set it to your Windows
session.save_path = C:\WINDOWS\TEMP
Save the changes you made and close your text editor.
Now, if you have IIS, follow these instructions:
- In the Windows Control Panel, open Administrative
Tools | Internet Information Services.
- In the tree view, expand the entry labeled local
computer, then under Web Sites look for Default Web
Site (unless you have virtual hosts set up, in which case,
choose the site you want to add PHP support to). Right-click on
the Web Site and choose Properties.
- Click the ISAPI Filters tab, and click Add....
In the Filter Name field, type
PHP, and in
the Executable field, browse for the file called
php4isapi.dll in the
sapi subfolder of
your PHP folder (e.g.
- Click the Home Directory tab, and click the
Configuration... button. On the Mappings tab click
Add. Again choose your
php4isapi.dll file as
the executable and type
.php in the extension box
(including the '
.'). Leave everything else unchanged
and click OK. If you want your Web server to treat other
file extensions as PHP files (
.phtml are common choices),
repeat this step for each extension. Click OK to close the
Application Configuration window.
- Click OK to close the Web Site Properties
window. Close the Internet Information Services window.
- Again, in the Control Panel under Administrative
Tools, open Services. Look for the World Wide Web
Publishing service near the bottom of the list. Right-click
on it and choose Restart to restart IIS with the new
configuration options. Close the Services window.
- You're done! PHP is installed!
If you don't have IIS, you'll first need to install some
other Web server. For our purposes I'll assume you have
downloaded and installed Apache server from
however, PHP can also be installed on Sambar Server (
http://www.omnicron.ab.ca/httpd/), and others.
Once you've downloaded and installed Apache according to the
instructions included with it, open
http://localhost/ in your Web browser, to make sure
it works properly. If you don't see a Web page explaining that
Apache was successfully installed, then either you haven't run
Apache yet, or your installation is faulty. Check the
documentation and make sure Apache is running properly before you
If you've made sure Apache is up and running, you can add PHP
On your Start Menu, choose Programs, Apache
httpd Server, Configure Apache Server, Edit
Configuration. This will open the
file in NotePad.
All of the options in this long and intimidating
configuration file should have been set up correctly by the
Apache install program. All you need to do is add the following
three lines to the very bottom of the file:
LoadModule php4_module c:/php/sapi/php4apache.dll
AddType application/x-httpd-php .php .php3 .phtml
AddType application/x-httpd-php-source .phps
Be sure the
LoadModule line points to the
php4apache.dll file on your system, and note the use
of slashes instead of backslashes.
Save your changes and close Notepad.
Restart Apache by choosing Programs, Apache httpd
Server, Control Apache Server, Restart on the
Start menu. If all is well, Apache will start up again
You're done! PHP is installed!
With MySQL and PHP installed, you're ready to proceed to the
"Post-Installation Setup Tasks" section below.
This section covers the procedure for installing PHP and MySQL
under most current distributions of Linux. These instructions
were tested under the latest versions of RedHat Linux and
Mandrake Linux; however, they should work on other distributions
such as Debian without much trouble. The steps involved will be
very similar, if not identical.
As a user of one of the handful of Linux distributions available,
you may be tempted to download and install the RPM distributions
of PHP and MySQL. RPM's are nice, pre-packaged versions of
software that are really easy to install. Unfortunately, they
also limit the software configuration options available to you.
If you already have MySQL and PHP installed in RPM form, then
feel free to proceed with those versions, and skip forward to the
"Post-Installation Setup Tasks" section. If you encounter any
problems, you can always return here to uninstall the RPM
versions and reinstall PHP and MySQL by hand.
Since many Linux distributions will automatically install PHP and
MySQL for you, your first step should be to remove any old RPM
versions of PHP and MySQL from your system. If one exists, use
your distribution's graphical software manager to remove all
packages with 'php' or 'mysql' in their names ('mod_php' is one
that is often missed).
If your distribution doesn't have a graphical software manager,
or if you didn't install a graphical user interface for your
server, you can remove these from the command line. You'll need
to be logged in as the root user to issue the commands to do
this. Note that in the following commands, "%" represents the
shell prompt, and doesn't to be typed in.
% rpm -e mysql
% rpm -e mod_php
% rpm -e php
If any of these commands tell you that the package in question is
not installed, don't worry about it unless you know for a fact
that it is. In such cases, it will be necessary for you to remove
the offending item by hand. Seek help from an experienced user if
you don't know how. If the second command runs successfully (i.e.
no message is displayed), then you did indeed have an RPM version
of PHP installed, and you'll need to do one more thing to get rid
of it entirely. Open your Apache configuration file (usually
/etc/httpd/conf/httpd.conf) in your favorite text
editor and look for the two lines shown here. They usually appear
in separate sections of the file, so don't worry if they're not
together. The path of the
libphp4.so file may also
be slightly different (e.g.
extramodules instead of
modules). If you can't find them, don't worry
– it just means that the RPM uninstaller was smart enough
to remove them for you.
LoadModule php4_module modules/libphp4.so
These lines are responsible for telling Apache to load PHP as a
plug-in module. Since you just uninstalled that module, you'll
need to get rid of these lines to make sure Apache keeps working
properly. You can comment out these lines by adding a hash (#) at
the beginning of both lines.
To make sure Apache is still in working order, you should now
restart it without the PHP plug-in:
% /etc/rc.d/init.d/httpd restart
With everything neat and tidy, you're ready to download and
install MySQL and PHP.
MySQL is freely available for Linux from
http://www.mysql.com (or one of
its mirrors listed at
http://www.mysql.com/downloads/mirrors.html). Download the latest
stable release (listed as "recommended" on the download page).
You should grab the "Tarball" version under "Source downloads",
With the program downloaded (it was about 10.5MB as of this
writing), you should make sure you're logged in as
root before proceeding with the installation, unless
you only want to install MySQL in your own home directory. To
begin, unpack the downloaded file and move into the directory
that is created:
% tar xfz mysql-3.xx.xx.tar.gz
% cd mysql-version
Next, you need to configure the MySQL install. Unless you really
know what you're doing, all you should have to do is tell it
where to install. I recommend
% ./configure --prefix=/usr/local/mysql
After sitting through the screens and screens of configuration
tests, you'll eventually get back to a command prompt. Be sure
the configuration completed successfully. If you see an error
message just before the configuration quit, you'll need to
address the problem it's complaining about. On Mandrake 8.0, for
example, it complained of "No curses/termcap library found". I
simply installed the 'libncurses5-devel' package in Mandrake's
Software Manager, and the configuration worked fine on a second
attempt. Once configuration is complete, you're ready to compile
After even more screens of compilation (this could take as long
as a half an hour on some systems), you'll again be returned to
the command prompt. You're now ready to install your newly
% make install
MySQL is now installed, but before it can do anything useful its
database files need to be installed too. Still in the directory
you installed from, type the following command:
With that done, you can delete the directory you've been working
in, which just contains all the source files and temporary
installation files. If you ever need to reinstall, you can simply
re-extract the mysql-version.tar.gz file.
With MySQL installed and ready to store information, all that's
left is to get the server running on your computer. While you can
run the server as the root user, or even as yourself (if, for
example, you installed the server in your own home directory),
the best idea is to set up on the system a special user whose
sole purpose is to run the MySQL server. This will remove any
possibility of someone using the MySQL server as a way to break
into the rest of your system. To create a special MySQL user,
you'll need to log in as root and type the following commands:
% /usr/sbin/groupadd mysqlgrp
% /usr/sbin/useradd -g mysqlgrp mysqlusr
By default, MySQL stores all database information in the var
subdirectory of the directory to which it was installed. We want
to make it so that nobody can access that directory except our
new MySQL user. Assuming you installed MySQL to the
/usr/local/mysql directory, use these commands:
% cd /usr/local/mysql
% chown -R mysqlusr.mysqlgrp var
% chmod -R go-rwx var
Now everything's set for you to launch the MySQL server for the
first time. From the MySQL directory, type the following command:
% bin/safe_mysqld --user=mysqlusr &
If you see the message 'mysql daemon ended', then the MySQL
server was prevented from starting. The error message should have
been written to a file called
hostname is your machine's hostname) in MySQL's
var directory. You'll usually find that this happens
because another MySQL server is already running on your computer.
If the MySQL server was launched without complaint, the server
will run (just like your Web or FTP server) until your computer
is shut down. To test that the server is running properly, type
the following command:
% bin/mysqladmin -u root status
A little blurb with some statistics about the MySQL server should
be displayed. If you receive an error message, something has gone
wrong. Again, check the
.err file to see if the
MySQL server output an error message while starting up. If you
retrace your steps to make sure you followed the process
described above, and this doesn't solve the problem, a post to
the SitePoint.com Forums
will help you pin it down in no time.
If you want your MySQL server to run automatically whenever the
system is running (just like your Web server probably does),
you'll have to set it up to do so. In the
share/mysql subdirectory of the MySQL directory,
you'll find a script called
mysql.server that can be
added to your system startup routines to do this.
First of all, assuming you've set up a special MySQL user to run
the MySQL server, you'll need to tell the MySQL server to start
as that user by default. To do this, create in your system's
/etc directory a file called
that contains these two lines:
Now, when you run
mysql.server to start the MySQL server, it will
mysqlusr without your having to tell it
to. All that's left to do is to set up your system to run
mysql.server automatically at startup.
Setting up your system to run the script at startup is a highly
operating system-dependant task. If you're not sure of how to do
this, you'd be best to ask someone who knows. However the
following commands (starting in the MySQL directory) will do the
trick for most versions of Linux:
% cp share/mysql/mysql.server /etc/rc.d/init.d/
% cd /etc/rc.d/init.d
% chmod 500 mysql.server
% cd /etc/rc.d/rc3.d
% ln -s ../init.d/mysql.server S99mysql
% cd /etc/rc.d/rc5.d
% ln -s ../init.d/mysql.server S99mysql
NOTE: Under the current SUSE distribution of Linux, there is no
init.d directory, just a symbolic link pointing back
/etc/rc.d; symbolic links for startup files
should thus be directed to the
ln –s ../mysql.server S99mysql).
That's it! To test that this works, reboot your system and
request the status of the server as before.
One final thing you might like to do for convenience sake, is to
place the MySQL client programs, which you'll use to administer
your MySQL server later on, in the system path. To this end, you
can place symbolic links to
mysqldump in your
% ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
% ln -s /usr/local/mysql/bin/mysqladmin /usr/local/bin/mysqladmin
% ln -s /usr/local/mysql/bin/mysqldump /usr/local/bin/mysqldump
As mentioned above, PHP is not really a program in and of itself.
Instead, it's a plug-in module for your Web server (probably
Apache). There are actually three ways to install the PHP plug-in
- As a CGI program that Apache runs every time it needs to
process a PHP-enhanced Web page.
- As an Apache module compiled right into the Apache program.
- As an Apache module loaded by Apache each time it starts up.
The first option is the easiest to install and set up, but it
requires Apache to launch PHP as a program on your computer every
time a PHP page is requested. This can really slow down the
response time of your Web server, especially if more than one
request needs to be processed at a time.
The second and third options are almost identical in terms of
performance, but since you‘re likely to have Apache
installed already, you'd probably prefer to avoid having to
download, recompile, and reinstall it from scratch. For this
reason, we'll use the third option.
To start, download the PHP Source Code package from
http://www.php.net/ (or one of its
mirrors listed at
http://www.php.net/mirrors.php). At the time of this writing,
PHP 4.x has become well-established as the version of choice;
however, some old servers still use PHP 3.x (usually because
nobody has bothered to update it). I'll be covering the
installation of PHP4 here, so be aware that if you still work
with PHP3 there may be some minor differences.
The file you downloaded should be called
version.tar.gz. To begin, we'll extract the files
% tar xfz php-version.tar.gz
% cd php-version
To install PHP as a loadable Apache module, you'll need the
apxs program. This comes with most versions
of Apache, but if you're using the copy that was installed with
your distribution of Linux, you may need to install the Apache
development RPM package to access Apache
should be able to install this package by whatever means your
software distribution provides. By default, RedHat and Mandrake
will install the program as
/usr/sbin/apxs, so if
you see this file, you know it's installed.
For the rest of the install procedure, you'll need to be logged
in as the
root user so you can make changes to the
Apache configuration files.
The next step is to configure the PHP installation program by
telling it which options you want to enable, and where it should
find the programs it needs to know about (like Apache and MySQL).
Unless you know exactly what you're doing, simply type the
command like this (all on one line):
Again, check for any error messages and install any files it
identifies as missing. On Mandrake 8.0, for example, it
complained that the 'lex' command wasn't found. I searched for
'lex' in the Mandrake package list and it came up with 'flex',
which it described as a program for matching patterns of text
used in many programs' build processes. Once that was installed,
the configuration process went without a hitch. After you watch
several screens of tests scroll by, you'll be returned to the
command prompt. The following two commands will compile and then
install PHP. Take a coffee break: this will take some time.
% make install
PHP is now installed in
/usr/local/php (unless you
specified a different directory with the
option of the
configure script above) and it'll
expect to find its configuration file, named
php.ini, in the
(unless you specified a different directory with the
with-config-file-path option of the
script above). PHP comes with two sample
optimized. Copy these files from your installation work
directory to the directory in which PHP expects to find its
php.ini file, then make a copy of the
dist file and call it
% cp php.ini* /usr/local/php/lib/
% cd /usr/local/php/lib
% cp php.ini-dist php.ini
You may now delete the directory from which you compiled PHP -
it's no longer needed.
We'll worry about fine-tuning
php.ini shortly. For
now, we need to make sure Apache knows where to find PHP, so that
it can load the program when it starts up. Open your Apache
httpd.conf configuration file (usually
/etc/httpd/conf/httpd.conf if you're using your
Linux distribution's copy of Apache) in your favorite text
editor. Look for a line that looks like this:
LoadModule php4_module lib/apache/libphp4.so
Find the new, uncommented line (no # at the start of the line),
not the old line that you may have commented out earlier. It may
not appear along with the other
LoadModule lines in
the file. Once you find it, you might need to change the path to
match all the other
LoadModule lines in the file.
Under RedHat Linux, this means you'll have to change the line to
make it look like this:
LoadModule php4_module modules/libphp4.so
PHP will probably run correctly without this change, but on older
versions of RedHat, Apache won't be able to find the
libphp4.so file until you make this change. If you
prefer, leave the line alone for now -- you can come back and
change it if you run into trouble.
Next, look for the line that begins with
DirectoryIndex. In recent distributions, this may be
in a separate file called
line tells Apache what filenames to use when it looks for the
default page for a given directory. You'll see the usual
index.html and so forth, but you need to add
index.phtml to that list if they're not there
DirectoryIndex index.html ... index.php index.phtml index.php3
Finally, go right to the bottom of the file (again, this should
commonhttpd.conf if you have such a file) and
add these lines, to tell Apache which file extensions should be
seen as PHP files:
AddType application/x-httpd-php .php .php3 .phtml
AddType application/x-httpd-php-source .phps
That should do it! Save your changes and restart your Apache
server. If all things go to plan, Apache should start up without
any error messages. If you run into any trouble, the helpful
folks in the
(myself included) will be happy to help.
No matter which operating system you're running, once PHP is
installed and the MySQL server is in operation, the very first
thing you need to do is assign a "root password" for MySQL. MySQL
only lets authorized users view and manipulate the information
stored in its databases, so you'll need to tell MySQL who is an
authorized user, and who isn't. When MySQL is first installed,
it's configured with a user named "root" who has access to do
pretty much any task without even entering a password. Your first
task should be to assign a password to the root user so that
unauthorized users can't mess around in your databases.
It's important to realize that MySQL, just like a Web server or
an FTP server, can be accessed from any computer on the same
network. If you're working on a computer connected to the
Internet that means anyone in the world could try to connect to
your MySQL server! The need to pick a hard-to-guess password
should be immediately obvious!
To set a root password for MySQL, type the following command in
the bin directory of your MySQL installation (include the
mysqladmin -u root password "your new password"
To make sure MySQL has registered this change, you should tell it
to reload its list of authorized users and passwords:
msqladmin -u root reload
If this command returns an error message to tell you that access
was denied, don't worry: this just means the password has already
To try out your new password, request that the MySQL server tell
you its current status:
mysqladmin -u root -p status
Enter your password when prompted. You should see a brief message
that provides information about the server and its current
status. The "-u root" argument tells the program that you want to
be identified as the MySQL user called "root". The "-p" argument
tells the program to prompt you for your password before it tries
to connect. The "status" argument just tells it that you're
interested in viewing the system status.
If at any time you want to shut down the MySQL server, you can
use the command below. Notice the usage of the same "-u root" and
"-p" arguments as before:
mysqladmin -u root -p shutdown
With your MySQL database system safe from intrusion, all that's
left is to configure PHP. To do this, we'll use a text file
php.ini. If you installed PHP under Windows,
you should already have copied
php.ini into your
Windows directory. If you installed PHP under Linux using the
instructions above, you should already have copied
php.ini into the PHP
/usr/local/php/lib), or wherever you chose to put
php.ini in your favorite text editor and have a
glance through it. Most of the settings are pretty well
explained, and most of the default settings are just fine for our
purposes. Just check to make sure that your settings match these:
register_globals = On
magic_quotes_gpc = On
doc_root = the document root folder of your Web server
extension_dir = the PHP extension directory
Save the changes to
php.ini, and then restart your
Web server. To restart Apache under Linux, log in as root and
type this command:
You're done! Now you just need to test to make sure everything's
If the host that provides you with Web space has already
installed and set up MySQL and PHP for you and you just want to
learn how to use them, there really isn't a lot you need to do.
Now would be a good time to get in touch with your host and
request any information you may need to access these services.
Specifically, you'll need a username and password to access the
MySQL server they've set up for you. They'll probably have
provided an empty database for you to use as well (which prevents
you from messing with the databases of other users who share the
same MySQL server), and you'll want to know the name of your
There are two ways you can access the MySQL server directly.
Firstly, you can use telnet or secure shell (SSH) to log in to
the host. You can then use the MySQL client programs
mysqldump) installed there to interact with the
MySQL server directly. The second method is to install those
client programs onto your own computer, and have them connect to
the MySQL server. Your Web host may support one or both of these
methods, so you'll need to ask which.
If your host allows you to log in by telnet or SSH to do your
work, you'll need a username and password for the login, in
addition to those you'll use to access the MySQL server (they can
be different). Be sure to ask for both sets of information.
If they support remote access to the MySQL server, you'll want to
download a program that lets you connect to, and interact with,
the server. This article series assumes you've downloaded from
http://www.mysql.com/ a binary
distribution of MySQL that includes the three client programs
mysqldump). Free packages are available for Windows,
Linux and other operating systems. Installation basically
consists of finding the three programs and putting them in a
convenient place. The rest of the package, which includes the
MySQL server, can be freely discarded. If you prefer a more
graphical interface, download something like MySQL GUI (also
http://www.mysql.com). I'd really recommend getting
comfortable with the basic client programs first, though, as the
commands you use with them will be similar to those you'll
include in your PHP scripts to access MySQL databases.
Some less expensive Web hosts these days support neither
telnet/SSH access, nor direct access to their MySQL servers.
Instead, they provide a management console that allows you to
browse and edit your database through your Web browser. Although
this is a fairly convenient and not overly restrictive solution,
it doesn't help you learn. Instead, I'd recommend the
installation of a MySQL server on your own system to help,
especially in the next chapter. Once you're comfortable working
with your learning server, you can start using the server
provided by your Web host with their management console. See the
previous sections for instructions on installing MySQL under
Windows and Linux.
It would be unfair of me to help you get everything installed and
not even give you a taste of what a PHP-driven Web page looks
like until Part 3, so here's a little something to whet your
Open up your favorite text or HTML editor and create a new file
today.php. Note that, to save a file with a
.php extension in Notepad, you'll need to either
select 'All Files' as the file type, or surround the filename
with quotes in the Save As dialog; otherwise, Notepad will
helpfully save the file as
today.php.txt. Type this
into the file:
(according to this
Web server) is
echo( date("l, F dS Y.") );
If you prefer, you can download this file along with the rest of
the code in this article series here.
Save this and place it on your Web site as you would any regular
HTML file, then view it in your browser. Note that if you view
the file on your own machine, you cannot use the
File, Open feature of your browser, because your
Web server must intervene to interpret the PHP code in the file.
Instead, you must move the file into the document root folder of
your Web server software (e.g.
in IIS, or
C:\Apache Group\Apache\htdocs\ in Apache
for Windows), then load it into your browser by typing
http://localhost/today.php. This allows the Web
server to run the PHP code in the file and replace it with the
date before it's sent to the Web browser. If you haven't yet had
time to set up PHP on your Web server, click
to see the results on our server.
Pretty neat, huh? If you use the ‘View Source' feature in
your browser, all you'll see is a regular HTML file with the date
in it. The PHP code (everything between
?> in the code above) has been interpreted by the
Web server and converted to normal text before it's sent to your
browser. The beauty of PHP (and other server-side scripting
languages) is that the Web browser doesn't have to know anything
about it – the Web server does all the work!
And don't worry too much about the exact code I used in this
example. Before too long you'll know it like the back of your
If you don't see the date, then something is wrong with the PHP
support in your Web server. Use View Source in your browser to
look at the code of the page. You'll probably see the PHP code
there in the page. Since the browser doesn't understand PHP, it
?> as one
long, invalid HTML tag, which it ignores. Make sure that PHP
support has been properly installed on your Web server, either in
accordance with the instructions provided in previous sections of
this article, or by your Web host.
You should now have everything you need to get MySQL and PHP
installed on your Web Server. If the little example above didn't
work right (for example, if the raw PHP code appeared instead of
the date), something went wrong with your setup procedure. Drop
by the SitePoint.com Forums
and we'll be glad to help you figure out the problem!
In Part 2, you'll learn the basics of
relational databases and get started working with MySQL. If
you've never even touched a database before, I promise you it'll
be a real eye opener! Meanwhile, I'd love to hear what you
thought of the first part of this series of articles. Drop me a
line at email@example.com, or
stop by the SitePoint.com Forums to
speak your mind.