Running Web Applications with MySQL and PHP - Network Services - Running Linux, 5th Edition (2009)

Running Linux, 5th Edition (2009)

Part IV. Network Services

In this part of the book, we introduce you to the things that really make Linux powerful and popular: such tools as a web server, email delivery tools, a database, and some of the other features on the cutting edge of system use. We also show you ways to protect your system and hedge against potential disasters.

Chapter 25: Running Web Applications with MySQL and PHP

Chapter 26: Running a Secure System

Chapter 27: Backup and Recovery

Chapter 28: Heterogeneous Networking and Running Windows Programs

Chapter 25. Running Web Applications with MySQL and PHP

Just writing a couple of lines of HTML code is not enough for most web sites; dynamic content is what people want today. To tell the truth, most commercial web sites offer more dynamic content than visitors really want—Flash-driven animations that greet you instead of useful information, for instance, or interactive JavaScript menus that make information harder to retrieve instead of easier—but in this chapter we give you an introduction to offering basic dynamic content that's really useful.

Linux is—you guessed it—an excellent platform for serving dynamic content. A bazillion web sites serving dynamic content are already running on Linux today; this is one of the foremost application areas where Linux excels.

Dynamic content can be achieved by two entirely different ways of programming: server-side programming and client-side programming. JavaScript, Java applets, and the Microsoft-specific ActiveX platform are the most common ways of producing interactive HTML pages with client-side programming.

Because of limitations in these technologies, however, most sites with substantial information to deliver use server-side programs. You can use them in many different flavors with many different software packages, but one combination has become ubiquitous for implementing these techniques. This combination is so common nowadays that it even has received a phony acronym: LAMP, which is short for Linux-Apache-MySQL-PHP. We have already talked about the Apache web server, and this whole book is about Linux, so what we have left to talk about here are the latter two packages—MySQL and PHP—as well as how the four packages go together.

To obtain a working LAMP installation, you will need to have Apache set up as described in "Configuring Your Own Web Server" in Chapter 22, as well as to install MySQL and PHP. We will cover how to get the latter two applications running in this chapter.

Before we get into the technical details, however, we should review why you might want to bother setting up and learning how to use a LAMP system.

LAMP makes it easy to provide a large amount of content and allow users of your web site to navigate through it easily.

Let's say you have a site with lots of JPEGs of photographs you've taken on numerous occasions. Visitors may want to view photographs using a number of different criteria. That is, some visitors want to see photographs of historic buildings, whenever you took them. Others might want to see photographs taken on your latest trip, whenever that was.

To make navigation and retrieval easy, you start by inserting the information about your JPEGs into a MySQL database. (The JPEG files will remain on the filesystem where Apache can get to them quickly.) You organize them any way you want (by subject matter, by trip, and so on) and store all this information in tables within the database. In other words, data is stored in tables, and a number of related tables make up a database.

Now you provide a form on your web site that visitors can fill out to indicate the dimension along which they want to view photographs. The form could be as simple as that shown in Figure 25-1.

A simple input form

Figure 25-1. A simple input form

Your next page is a dynamic one, along the lines of that which we describe in this chapter. A bit of PHP code retrieves the visitor's request and determines what is displayed in the page. This could look like Figure 25-2.

Where is the MySQL in all this? It's not immediately visible, but it plays a crucial role behind the scenes because it is queried by the PHP code. The combination of inline PHP code and a fast database makes the whole experience fairly pleasant for the visitor.

A dynamic web page generated by PHP

Figure 25-2. A dynamic web page generated by PHP

MySQL

MySQL is an open source database that is very fast and comparatively easy to administer. If you need the most advanced database features, such as replication or distributed databases, or if you plan to store gigabytes of data, the big-iron databases such as Oracle might be a better choice, but for most intents and purposes, MySQL is an excellent database choice (and it is in fact catching up even when it comes to those very advanced features). It is dual-licensed. If you are using the GPL version, your application must be licensed under the GPL as well; otherwise, you need to buy the commercial version.

MySQL Installation and Initial Configuration

It is very likely that your distribution contains an installable MySQL system, but if you'd rather have the latest and greatest, you can go to http://www.mysql.com/downloads and download the package yourself. At the time of this writing, the latest

On the Choice of Databases

We should probably point out that MySQL is not the only choice you have when selecting a database as the backend for dynamic web sites. For example, Postgres, which is installed by default on Red Hat systems, is a very good open source database as well. So, if you want to deploy a "LAPP" system and use Postgres instead, you can very well do that. Most of the information in this chapter will still apply. However, MySQL can be considered the standard database for dynamic web sites on Linux systems. This is due to its ease of use, speed, and versatility. Also, if you are looking for additional documentation on deploying dynamic web sites on Linux (which we strongly recommend you do), you are more likely to find this information (e.g., in the form of dedicated books on this topic) for MySQL than for Postgres.

stable version was 4.1.13. Version 5.0 was stabilizing quickly. If you plan to use MySQL for real-life applications, make sure your Linux kernel is at 2.4 or better.

A problem that can occur with MySQL versions compiled with gcc 2.96 is random crashes. This gcc version is not an official stable version, but at least one distributor (Red Hat) unfortunately shipped it as the default compiler in one version. So if you experience strange crashes in the database server and are using gcc 2.96, try using one of the precompiled binaries or install a more stable (and more recent) compiler version, such as 3.3.5.

If you want to build MySQL on your own, you need to download the source package, unpack it, and install with the following commands:

owl$ ./configure --prefix=/usr/local/mysql

owl$ make

owl#make install

Note that depending on your system, you might select a different installation path. You will also probably need to be root for the third step. You need to remember the installation path because you will need it for the PHP configuration later on.

For the next step, we recommend that you create a user and a group called mysql as described in "Creating Accounts" in Chapter 11. Change to this user with su - mysql and execute:

owl$ scripts/mysql_install_db

For security reasons, it might be a good idea to disable logins by the mysql user. You can simply do this as root by putting a star in the second (password) column in /etc/password or /etc/shadow, or both.

After this step, you need to perform just one more command before returning from root to your normal user account. The following command starts the MySQL server:

owl# /usr/local/mysql/bin/mysqld_safe &

You might also want to add either the option -- log or the option -- log-long-format in order to get a logfile about what is going on in the database server.

To check whether your server was started correctly, you can try (as a normal user again) the following command (you need to change the path, of course, if you have installed MySQL in a different location):

owl$ /usr/local/mysql/bin/mysqladmin version

mysqladmin Ver 8.41 Distrib 4.1.13, for suse-linux on i686

Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license.

Server version 4.1.13

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 43 days 11 hours 39 min 17 sec

Threads: 1 Questions: 142 Slow queries: 0 Opens: 160 Flush tables: 1 Open

tables: 13 Queries per second avg: 0.000

This should work without entering a password. We would like to point out, though, that it is not a good idea to have a database without a password because that increases the odds that a possible intruder will get at your potentially valuable data in the database. You might perhaps want to leave the database without a password for testing, but you should make sure that you do not forget to set a password after you are done with your tests. Retest to see whether everything works with the password in place. If you have created a password for the root database user (or if your distribution has done so for you — check your documentation in case of any problems), you must specify the -p option, which makes mysqladmin ask you for your password.

We should add here that most distributions include a startup script for the MySQL server that you can use instead of starting the server manually (especially if you have installed MySQL from your installation media). Often, this script is in /etc/init.d/mysql.

With the database server started and working, we can start to define database users and create new databases. We would like to point out that a usable tutorial is included with the MySQL sources. You can also find lots of documentation on http://www.mysql.com, so we just cover the very basics here to get you started.

Initial Tasks: Setting Up Users and SQL

There are three ways of communicating with the MySQL engine: you can use a console-based database client, you can write so-called SQL scripts and feed them to the database in order to execute many SQL commands at once, and you can use one of the many bindings to programming languages to access the MySQL database in the programming language of your choice (depending on the access library that you use, this may even mean that you do not have to enter SQL statements at all). SQL stands for Structured Query Language and is the database language used with relational databases; we cover its use later in this chapter. All three ways of executing SQL commands assume that you have the correct username/password combination.

An important thing you need to know about MySQL is that Linux user accounts are different from MySQL user accounts. In other words, MySQL has its own account management. Most people give their MySQL user accounts the same names as their Linux user accounts in order to avoid confusion, though.

By default, there is one MySQL account called root, which has no password (talk about "security by default"). This means that you can access the database server with the interactive command-line tool mysql as follows:

owl$ mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13 to server version: 4.1.13.

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The -u option specifies the database user to use. If this does not work, maybe your MySQL installation has a password set for the root user. Try to find this password in the documentation and start the mysql program with:

owl$ mysql -u root -p

which will prompt you for the password.

Assuming that you have been able to log in to the database server, let's try to issue a command:[*]

mysql> show databases;

+-------------+

| Database |

+-------------+

| mysql |

| test |

+-------------+

2 rows in set (0.11 sec)

This tells you that two databases are managed by this database server. One is called mysql and contains MySQL's internal configuration information, including the usernames, and the other one is called test and can be used by you for your experiments. It's also no problem at all to create additional databases ; we'll show you how in a minute. As you can see, all SQL commands need to be terminated with a semicolon—probably in order to make the C programmers happy.

Now you should give the root account a password (in case it does not have one already). This is done with two SQL commands:

mysql> SET PASSWORD FOR root=PASSWORD('new_topsecret_passwd');

mysql>FLUSH PRIVILEGES

;

Notice again the semicolon at the end of these commands; if you forget to type them before pressing the Enter key, MySQL will just stare at you, waiting for you to enter more.

By the way, SQL commands are case-insensitive; we have written them in uppercase here because that makes it a bit easier to see where the command keywords and the variable parameters are in a SQL script.

Also note the use of the FLUSH PRIVILEGES command. This is important because only after this command has been executed will MySQL update its user database.

Now we want to create a new user called olof, which has the same access rights as root, except that it cannot create new users. Apart from that, olof may use and manipulate all MySQL databases on this database server:

mysql> GRANT ALL PRIVILEGES ON *.* TO olof@localhost IDENTIFIED BY 'olof_passwd';

mysql>FLUSH PRIVILEGES;

The user olof can log in to the database only from the local machine. This is a good idea since it leaves one less security issue to think about. We recommend that you only allow access from the local machine unless you have a very good reason not to do it this way. Even in the LAMP combo, local access is enough, because the web server process is running on the local machine, and this is the process that connects to the database, not the user's web browser process.

But if you really require access to the database over the network, you could use these commands instead:

mysql> GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'user_passwd';

mysql>FLUSH PRIVILEGES;

If you think that having all access rights except creating new users is a bit too much, let's create another user that may execute the SELECT, INSERT, UPDATE, DELETE, and DROP operations, but only on the database called test (and only when connected from the local machine):

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON test.* TO gonzo@localhost

IDENTIFIED BY 'gonzo_passwd';

mysql>FLUSH PRIVILEGES;

If you haven't worked with SQL databases before, these operations will probably not make much sense to you. Since you are going to need to use them anyway when setting up your LAMP system, we might as well shortly describe them here:

SELECT

This is the most commonly used SQL command. It queries the database for data with certain properties—for example, you could ask for all customers in a certain town. SELECT never changes anything in the database.

INSERT

This SQL command inserts new records into a database table. You use this (either interactively or, more likely, as part of a program) to insert a customer record into the customer table in your database, for example.

UPDATE

This SQL command changes existing records in a database. You could use this to, for example, increase the retail prices of all articles in the database by 15%. (Talk about inflation!)

DELETE

This SQL command deletes entire records from the database. Be careful with this command, as there is no way of restoring the data short of restoring from a (hopefully available) backup tape.

There are even more SQL commands and corresponding privileges (such as DROP, which lets you delete entire tables or even entire databases), but these are used less often than the "big four" listed here.

Now we want to create a new database, which we can then fill with tables and data later. This is done with the SQL command CREATE DATABASE :

mysql> create database test_database;

Query OK; 1 row affected (0.03 sec)

The output from MySQL already indicates that everything went fine, but to be really sure, we can ask anew which databases the server manages:

mysql> show databases;

+-----------------+

| Database |

+-----------------+

| mysql |

| test |

| test_database |

+-----------------+

6 rows in set (0.00 sec)

Creating and Populating a Database

Now we want to define a table in our new database, but the first thing we need to do is tell the MySQL server that we actually want to use this database:

mysql> use test_database

Database changed

As you can see, we didn't use a semicolon at the end here, since this is again not a SQL command, but rather a control statement for the MySQL console client. It wouldn't hurt to add a semicolon here, too.

You define a table, which is ultimately where your data will be stored, by means of the SQL command CREATE TABLE. Here is an example:

mysql> CREATE TABLE comment_table(

-> id INT NOT NULL auto_increment,

-> comment TEXT,

-> PRIMARY KEY(id));

Query OK, 0 rows affected (0.10 sec)

Here we defined a table called comment_table with two columns—that is, there are two data fields in each record. One is called id. This serves as a unique identifier for each record and is therefore marked as the primary key, which is just a fancy term in database-speak for "unique identifier." The other column is a variable of type TEXT that can store up to 65,535 characters.

Now we can check which tables we have within our database test_database:

mysql> show tables;

+-------------------------+

| Tables_in_test_database |

+-------------------------+

| comment_table |

+-------------------------+

1 row in set (0.00 sec)

Now we know that everything is all right and can start to add data records to our table. This is done with the SQL command INSERT:

mysql> INSERT INTO comment_table VALUES ('0','comment');

Query OK, 1 row affected (0.06 sec)

Finally, we can check which data our table contains:

mysql> SELECT * FROM comment_table;

+----+---------+

| id | comment |

+----+---------+

| 1 | comment |

+----+---------+

1 row in set (0.01 sec)

Here we ask for all (*) columns in the table comment_table. But you might have noticed something odd here: we have asked MySQL to insert a 0 in the first column, but instead there is a 1 now. That's because we have defined this column to be of the type INT NOT NULL auto_increment, which means that the column value cannot be NULL and that MySQL will automatically choose the next available value. This is nice because we can insert new records into the table without having to ensure that we pick unique values for the first column:

mysql> INSERT INTO comment_table VALUES ('0','comment1');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM comment_table;

+----+----------+

| id | comment |

+----+----------+

| 1 | comment |

| 2 | comment1 |

+----+----------+

2 rows in set (0.00 sec)

As you can see, we have specified 0 as the value for the first column again, but MySQL has automatically selected the next available valid value.

At this point, you know already enough about MySQL to experiment yourself or start reading another book about databases and dream about building the next hugely successful e-commerce web site.

But before you enter your dream, take a moment and let us finish our MySQL discussion by letting you in on one more useful feature: SQL scripts.

You do not necessarily need to type in all commands at MySQL's own command-line prompt. You can also execute batch files with SQL commands by piping them to the mysql program. For example, if you save the following SQL code as create_db.sql:

DROP DATABASE IF EXISTS test_database;

CREATE DATABASE test_database;

USE test_database;

CREATE TABLE comment_table( id INT NOT NULL auto_increment,\

comment TEXT,PRIMARY KEY(id));

INSERT INTO comment_table VALUES ('0','comment');

INSERT INTO comment_table VALUES ('0','comment1');

you can execute this script from the ordinary Linux command line with:

mysql -u root -p < create_db.sql

The line:

DROP DATABASE IF EXISTS test_database;

is of course pretty dangerous; you should use it only if you don't have important data in your database.

To tell the truth, it is not absolutely necessary (albeit strongly recommended) to create a new database for each project. In theory, you could lump all your data into the test database that is preinstalled with MySQL as long as you make sure the table names are all different. In practice, this would be a maintenance nightmare if you had more than a handful of tables.


[*] This is not a real SQL command, but rather a MySQL administration command.

PHP

To complete our combo of Linux, Apache, PHP, and MySQL, we still need the PHP language interpreter. PHP is a recursive acronym that expands to PHP: Hypertext Preprocessor. It has been in development for several years now; the versions most commonly used are Version 4 and Version 5. We use PHP4 in this chapter, because it was the most often used version at the time of writing. The changes between Versions 4 and 5 are either in underlying implementation or advanced features that will interest you only when you pile up a large number of PHP files.

Some Sample PHP

One of the nice things about PHP is that PHP code can be entered directly into HTML code. The web server will pass everything between <?php and ?> to the PHP module, which will interpret and execute the commands. Here is a very simple example for some PHP code in an HTML page; if you already have set up PHP, you could run this directly from your web server (if not, we'll tell you how to set up PHP shortly):

<html>

<body>

<?php

echo

"Hi, ";

?>

LAMP enthusiasts.

</body>

</html>

As you probably already have expected, your browser will output the following text:

Hi, LAMP enthusiasts.

This extremely simple example shows how Apache works together with the PHP interpreter: the code between <?php and ?> is passed to the PHP interpreter, which executes the echo command, which in turn outputs its parameters to the web browser. In addition to this, the line LAMP enthusiasts is simply added as ordinary HTML text (and since it doesn't have any markup, it doesn't look like HTML).

Of course, PHP can do much more. Like most programming languages, it can use variables and make decisions, as in the following script (we leave out the HTML framework here for brevity):

<?php

echo "Dear friends, today's date is: ";

echo date("F d, Y")."\n";

echo "<br>";

echo "We are in the ";

if ( date ("m") <= 6 ) {

echo "first ";

} else {

echo "second ";

}

echo "half of the year ".date("Y");

?>

You have probably already guessed that this script makes its decision in the if statement depending on the current month. Notice that we have used an HTML tag (<br>) in the PHP output; this is completely acceptable and a very common technique when using PHP. Your web browser will receive the following data (of course, with other dates, unless your computer clock is wrong or you were trapped in a time warp):

Dear friends, today's date is: May 04, 2002

<br>We are in the first half of the year of 2002

The web browser will then break the line at the position of the <br> tab.

In order to modularize your code, PHP also supports functions, as do most other programming languages. These functions enable you to execute a piece of code in many different places without having to rewrite it over and over again.

Tip

PHP comes with very extensive function libraries, and you can download even more from the Net. To include a function library, you can use the include() and the require() statements, which differ only marginally.

If you want to program with PHP, you should familiarize yourself with the documentation of the function libraries that are shipped together with the PHP interpreter, since their use means you do not have to reinvent the wheel when performing common tasks.

Here is the definition of two simple functions--show_date, which outputs the current date in a hardcoded date format and appends a line break, and show_halfyear, which outputs first or second depending on the current month:

<?php

function show_date() {

echo date("F d, Y") . "\n <br>";

}

function show_halfyear() {

if (date("m") <= 6) {

echo "first ";

} else {

echo "second ";

}

}

?>

Let's call this script functions.php and rewrite our initial script using these functions:

<?php

require("functions.php");

echo "Dear friends, the date today is: ";

show_date();

echo "<br>";

echo "We are in the ";

show_semester();

echo "semester of " . date("Y");

?>

The require() statement tells the PHP interpreter to load our function script and make the functions contained therein available to the current script.

Of course, we have only scratched the surface of what PHP can do. If this has whetted your appetite, you might want to look into Programming PHP, by Rasmus Lerdorf, the original author of PHP, and Kevin Tatroe (O'Reilly).

Until PHP3, PHP was an interpreted language, the code of which was kept in a buffer. Loops and other often-run pieces of code were parsed over and over again before executing the code. Of course, this led to suboptimal performance.

PHP4 is a complete rewrite and consists of the language core (called "Zend") and the function modules (which are very flexible and extensible). Unlike PHP3, PHP4 can be used in multithreaded environments, which also makes it possible to use PHP as a module in various web servers. PHP5 was yet another rewrite (using the "Zend2" engine) with a new, more intuitive object model, improved performance, and support for exceptions. http://www.php.net/manual/en/migration5.php gives you the scoop on the differences between PHP4 and PHP5.

Besides PHP itself, it may be a good idea to download and install phpMyAdmin. phpMyAdmin is a database administration tool written in PHP that simplifies your daily work when it comes to the administration of MySQL, and handles tasks such as creating and dropping databases and tables. It also can manage privileges, keys and fields, and even more. Besides, it is an excellent example of source code that accesses MySQL databases from PHP code! You can download phpMyAdmin from http://www.phpmyadmin.net. When you have installed it according to the instructions, you can open the URL http://localhost/phpMyAdmin in your browser, and you will see the screen shown in Figure 25-3.

In addition to running PHP4 as a module, you can run it as a CGI program started by the web server, at the expense of some additional overhead. When running PHP as a CGI program, each new page that contains PHP code requires starting a new instance of the PHP interpreter, which in turn requires creating a new process and

Administering MySQL databases with phpMyAdmin

Figure 25-3. Administering MySQL databases with phpMyAdmin

loading the PHP interpreter into this process. When the interpreter is finished creating the page, its process ends, the memory is freed, all file handles are closed, and all database connections are shut down.

As a web server module, the PHP interpreter becomes part of the web server and is always loaded into memory. In addition, it can keep resources such as database connections alive across different pages, which can bring huge performance benefits.

All big-ticket PHP sites use PHP as a module, mostly because of the better performance it affords.

PHP4 as an Apache Module

As we have already said, running the PHP interpreter as a web server module is best for getting good performance. Today, most distributions (including Slackware, Debian, SUSE, and Red Hat) ship both Apache and the PHP4 module for Apache, so it is generally not necessary to build the PHP4 module yourself. It may be a good idea to do so anyway, however.

Because of its vast amount of functionality, the PHP4 module needs quite a number of additional libraries or modules. If you install the module from your installation CDs, the installation program will have automatically installed the necessary modules. However, the modules shipped with distributions are typically loaded with functionality to satisfy all needs and tastes. The result can be a system that's heavier and slower than it needs to be.

Thus, the advantage of building PHP4 by yourself is that you can decide which functionality you want to go into the module. Check the documentation to see which additional libraries you might need to install.

Since we firmly believe that you should know what goes on behind the scenes, even if you use the more comfortable ready-made solutions, we will give you some hints regarding how to work from scratch and how the pieces work together.

To load the PHP4 module into the Apache web server at runtime, you need to have the Apache module mod_so. You can check whether this module is presented by issuing:

owl$ httpd -l

Compiled-in modules:

http_core.c

mod_so.c

If this module is not available, please check whether you may have missed installing some of the additional Apache packages in your distribution. If you have built Apache from scratch, follow the documentation to get this module.

It is also possible to compile the PHP4 module directly into Apache, but this requires some very intertwined building of both Apache and PHP4 and does not really give you a big advantage, so we won't cover this here.

Now we need to build PHP and make a dynamic shared object (DSO) out of it. Luckily, this is not as involved as it sounds. Download PHP4 from http://www.php.net/download.php. You will end up with a package called php-4.4.0.tar.gz (the actual version number may differ slightly). Unpack the tar file and configure PHP with:

owl$ ./configure \

--with-mysql=/usr/lib/mysql\

--with-ldap=yes\

--with-gd=yes\

--with-zlib=yes\

--with-config-file-path=/etc/\

--with-apxs=/usr/lib/apache/apxs\--enable-versioning\

--enable-track-vars\

--enable-thread-safety

You can read about numerous additional options in the extensive PHP documentation, but for starters, this will do. Note that you might need to replace some of the paths here with the actual locations on your system. After configure is finished, do a make and then a make install to install PHP (you may need to do the make install as root).

Next, edit the httpd.conf file, Apache's configuration file. If you have installed Apache from your installation CDs, chances are the following lines are already there and you just need to uncomment them. In any case, you should have the following lines in your httpd.conf:

LoadModule php4_module libexec/libphp4.so

AddModule mod_php4.c

AddHandler application/x-httpd-php .php

You may also want to find your existing DirectoryIndex line and change it to allow PHP files to be used as default pages:

DirectoryIndex index.html index.php

Now restart Apache:

owl# apachectl restart

(The command apachectl may be called differently on your distribution; try rcapache.) Once the server is restarted, you should test whether the PHP4 module can be loaded correctly. You can do this by writing a small PHP program, such as the following:

<?php

phpinfo();

?>

Save this file as phpinfo.php in the htdocs directory of your Apache installation (often /usr/local/httpd/htdocs). Now you should be able to browse this file with your web browser by accessing http://localhost/phpinfo.php. If everything is OK, you should see the configuration of the PHP4 module.

The LAMP Server in Action

Now you have all the components for your LAMP server in place; it is time to run a few examples.

If you haven't done so already while following the last section, we suggest that you test your setup now with a very simple PHP file. Save the PHP that you have seen in the last listing in the previous section into a file called info.php.

Now place this file in the directory where your Apache web server is looking for its contents files. Often, this is /usr/local/httpd/htdocs, and it may already contain the files that your distribution has installed for you during installation (at least if you have installed Apache from the installation media). If this doesn't work for you, you should look for the Apache configuration file httpd.conf. Often, this file is in the /etc/httpd/ directory, but if this is not the case on your system, you can search for it with

locate httpd.conf

In this file, look for the line starting with DocumentRoot. You should find a directory listed here, and a subdirectory named htdocs should be under that directory; put the file info.php here. Now you can use any web browser to access the URL http://localhost/info.php. This will give you some information about the setup of your PHP module.

PHP comes with a number of built-in functions that manipulate and manage the data stored in MySQL (and other databases).

A relational database consists of a number of tables. If you have sufficient access rights, PHP can query and manipulate data in these tables. We can now write a few PHP scripts to use the database tables. We assume here that you have created the database test_database and the tablecomment_table, as well as the user olof as described earlier.

Use your favorite text editor and enter the following code, which creates a small HTML page that lets you add data to this table by means of an HTML form:

<html>

<?php

if (isset($_REQUEST["comment"])) {

$conn = mysql_connect("localhost", "olof", "secret")

or die("Could not connect to MySQL as olof");

mysql_select_db("test_database", $conn)

or die("could not select the test_database");

if (get_magic_quotes_gpc()) {

$comment = stripslashes($_REQUEST["comment"]);

} else {

$comment = $_REQUEST["comment"];

}

$query = "INSERT INTO comment_table VALUES ('0', '"

. mysql_real_escape_string($comment) . "')";

mysql_query($query)

or die(mysql_error());

}

?>

<form action="" method="POST">

<input type="text" name="comment" size="80"><br>

<input type="submit">

</form>

</html>

When you work with a database, you must take precautions not to allow user input to manipulate your SQL queries. If you don't do this, a malicious user could simply hijack your database. You can make yourself safe by transforming the input data before using it to construct SQL queries. Normally, it is enough to put user input through the mysql_real_escape_string() function. In some situations, you may need to apply the stripslashes() function first. This is because of the special PHP feature called magic_quotes_gpc, which was meant to make all input data safe for the database automatically. Although the idea was noble, the feature does not provide sufficient protection and creates other problems for programmers. We recommend you turn this feature off in your configuration. Otherwise, you first need to detect whether it is enabled, and neutralize its effects if you discover that it is.

You can execute this script by saving it as a file with the extension .php, copying it into the document directory of your web server, and accessing the script with your web browser. For example, if you have saved it as edit.php, you could access the URL http://localhost/edit.php to execute this script. The web server knows that it needs to run everything between <?php and ?> through the PHP module. Thus, the PHP code can be directly embedded into an HTML page.

Now that we can enter comments into our database, we also want to review them. Thus, next up is a script to read from the database:

<html>

<?php

$conn = mysql_connect("localhost", "olof", "secret")

or die("Could not connect to MySQL as olof");

mysql_select_db("test_database", $conn)

or die("could not select the test_database");

$query = "SELECT * FROM comment_table";

$result = mysql_query($query)

or die(mysql_error());

$numbers_cols = mysql_num_fields($result);

print "<b>query: $query</b>";

print "<table border=1>\n";

print "<tr>";

print "<td>ID</td>";

print "<td>Comment</td>";

print "</tr>";

while (list($id, $comment) = mysql_fetch_array($result)) {

print "<tr>";

print "<td>" . htmlspecialchars($id, ENT_QUOTES) . "</td>";

print "<td>" . htmlspecialchars($comment, ENT_QUOTES) . "</td>";

print "</tr>";

}

print "</table>";

?>

</html>

As you can see, we are using the HTML tags for laying out tables in order to display the contents of the database, which is a very natural and obvious thing to do. Also note that we did not print the data from the database directly to the HTML page. This would have allowed a potential adversary to hijack the page by using improper input. Instead, we used the htmlspecialchars() function to make the data HTML safe.

It was our intention to keep these examples as simple as possible so as not to overload you with too much information. If you want to dive deeper into the wonderful world of LAMP, we recommend that you read a good book such as Web Database Applications with PHP & MySQL (O'Reilly) or MySQL/PHP Database Applications (John Wiley & Sons).