Cover V07, I07
Figure 1
Figure 2
Figure 3
Table 1
Table 2


PHP, Databases, and the Web

Tony Mobily

The Web is essentially an enormous database, containing many types of information, stored on virtually millions of computers around the world. Usually, however, the information is stored as normal text files, even when the information really should be stored under a DBMS. This is due both to historical reasons and to the fact that people think it is more difficult and expensive to manage a database than to manage a tree of HTML text and image files. Until recently, there have been limited ways to allow browser-based database access from the Web, including:

  • Perl and DBD/DBI drivers

  • Microsoft's ASP

  • Other proprietary technologies

All of these ways have advantages and disadvantages. PHP (PHP: Hypertext Preprocessor), however, adds a new dimension to the concept of interactively accessing data held within conventional database files from a Web page.

PHP is a powerful server-side scripting language that allows you to write simple programs directly in your Web pages. It's comparable to Perl and C (commonly used to write CGI programs), but has a lot of features that make it much better (e.g., you can "mix" HTML code and PHP code). The syntax is simple, memory is managed automatically, and it connects easily to many different DBMSs.

PHP can be used in many different Web situations. It is powerful in both the implementation of a simple bookmark in a Web page and the creation of an online shop, because it is a simple and easily readable language. Databases, though, are its real forte. To give you an understanding of how to use PHP, I will first review how dynamic HTML pages work, then look at how to obtain and compile PHP, and finally look at some sample PHP code that accesses database files.

How Dynamic HTML Pages Work

A Web site is basically a collection of images and text pages. The clients (usually browsers like Netscape Navigator), request a specific page from the Web server (using the HTTP standard protocol), and once they receive it, display the page following the HTML format (see Figure 1). The page contains just text. Sometimes the request page is not a normal HTML page, but a program (usually located in the cgi-bin directory). If a client requests a page like that, the server will execute the program, sending the standard output as a result (see Figure 2). For example, a Perl script that contains:

print("Content-type : text/html\n\n<h1>Hello world !</h1>")

sends data to the browser allowing it to display a page with "Hello world" as the first line. With the CGI mechanism, you can also create a basic database-Web interface. A Perl script, for example, can make queries to a database through DBD/DBI drivers and send the results to the browser. Another way to do this is to configure the Web site's actual server to put the pages through a "filter" program before sending them to the clients (see Figure 3). This last solution is similar to the second one using CGI. The main difference is that a CGI program is written using a general purpose language (like Perl), and the "filter" program is a module created with the sole purpose of filtering Web pages. Whatever method is used, the Web page sent to the client will be manipulated and changed to reach the site's goal. For example, an online shop might have the option to request a list of particular items from the shop database. The request would be interpreted in the CGI program, a query to the database would be made, and then the results sent back to the browser.

What Is PHP?

PHP (http://www/ is a module that works as a Web page filter (see Figure 3). By configuring the Web server, all pages with the .php3 extension are processed by the module, before being sent to the client. PHP is a complete and reliable programming language that allows webmasters to write scripts inside normal HTML pages, using special tags. Everything between the tags <? and ?> is processed by the module as PHP code. In this way, it's easy to mix PHP script with standard HTML. Take this "for loop", for example:

<? for($i=0; $i<10; $i++){ ?>
<H3> This message will be repeated ten times </H3>
<? } ?>

The language is similar to C or Perl and, to some extent, to Basic (depending on how you use it). Both of the following syntax examples can be used in PHP:

if ($a==0):
print "A is zero!\n";
print "A is NOT zero !";

print("A is ZERO!\n") ;
}else {
print("A is NOT zero !");

For people who already use Perl or C, it is easy to start programming in PHP. Debugging is also simple thanks to the functionality of code highlighting, provided by the same module. Memory management is completely automatic. PHP manages automatically the variables set using either the POST or GET methods in a form. Thus if a form looks like this:

<INPUT NAME=trial TYPE=TEXT  SIZE=20> Insert text here

when the script script.php3 is started, the variable trial is already set by the interpreter. The interpreter is very fast. With a typical 486 running Linux, with complex scripts (more than 800 lines) the answer from the server is almost immediate, even when sending multiple requests of the script at the same time. One of the main purposes of PHP is to work as an interface between databases and the Web. It supports:

  • Oracle
  • ODBC
  • Adabas D
  • MySQL
  • mSQL
  • PostgresSQL
  • Solid
  • Sybase
  • Raima Velocis
  • dBase
  • filePro (read only)

PHP does not have native drivers to connect to these databases. When you compile it, you must specify the location of the library of functions provided with the database engine. PHP can be compiled as an Apache Module (with better performance) or a normal CGI program.

Downloading and Compiling PHP 3

First, take a look at the PHP site: Reading the FAQ is a good first step toward understanding PHP. Then, download the package (the easiest way is through your Web browser), and decompress it using:

tar xvzf php-3.0RC4.tar.gz

A directory called php-3.0RC4, containing the source code of PHP, will be created. It is a good idea to read the file INSTALL, in the exploded directory. In this article, to keep it simple, I will discuss using PHP as an external filter, without having to recompile Apache. You can read how to build the Apache module in the file INSTALL. These instructions are detailed and well done. To compile PHP, you must first create the Makefile. To do this, you can run the script "configure" in the PHP source directory. The command is fairly complicated, especially if you are not sure about the options.

To help administrators configure PHP more easily, there is a script called "setup". This asks a series of questions about the configuration and runs the configuration script with the right parameters. Each question comes with an explanation. The first two questions ask whether you want to build PHP as an Apache or an fhttpd module. In our sample installation, we say "no" to both of these. The next questions are about database support. You must provide the home directory of your database server, to allow PHP to link with the right libraries. If you have Mysql installed, for example, you answer like this:

Build PHP with MySQL support?
More info about MySQL can be found at
If you answer 'yes', the default directory is /usr/local.
MySQL support? ('yes', 'no' or dir) [no] : /usr

I installed Mysql through a RPM, so its home directory is /usr. (/usr/local is the usual target directory when installing Mysql and compiling it "by hand.") After the questions about databases, the script will ask you other questions, which I've listed below:

Default config directory? ('yes', 'no' or dir) [yes] : /etc
Use the system regex library? (yes/no) [no] : no
Compile with debug information? (yes/no) [yes] : no
Enable safe mode by default? (yes/no) [no] : no
Default safe mode exec dir? (dir) [/usr/local/bin] : /usr/local/bin
Enable variable tracking by default? (yes/no) [no] : no
Enable magic quotes by default? (yes/no) [no] : no
Enable PHP remote debugger? (yes/no) [no] : no
Enable bc style precision math functions (yes/no) [no] : no
Enable redirect checking? (yes/no) [no] : no
Enable discard path? (yes/no) [no] : no
Enable memory limit? (yes/no) [no] : no
Allow short tag by default? (yes/no) [yes] : yes
Enable URL fopen wrappers? (yes/no) [yes] : yes
Install php in: (dir) [/usr/local/bin] :

The answers really depend on your own personal needs and preferences. For example, I put all the configuration files in /etc. I also suggest disabling the safe mode at the first installation, and enabling it when you have a bit more experience with PHP. When the last question is answered, the "setup" script will run automatically. Then, you just have to type "make", to build the PHP executable. The compilation should succeed without any trouble. By running "make install", the executable is copied into /usr/local/bin. Of course, you have to copy it into the cgi-bin directory of your Web server in order to use it.

After compiling PHP, you have to configure Apache so that it will use PHP as a default filter for all .php3 files. Using Apache 1.2.4. as an example, you must first add a mime type by adding this line to the conf/mime-types configuration file:

application/x-httpd-php php3

Then, you have to tell Apache the location of the filter file, by adding this line to conf/srm.conf:

Action application/x-httpd-php /cgi-bin/php

Kill and restart Apache to make sure that the new configuration files are re-read. Now, your PHP filter should be working properly. To test it, create a file called test.php3, that contains the line:

<? Phpinfo(); ?>

Now, load the file through a browser. The result should be a page containing various information about the PHP version in use. To execute a more satisfying command, try the following program:

Echo("Hello, world !<BR>\n");

The most implemented program in the world should work! Your httpd server is now PHP-enabled and is ready to connect to your database server and create dynamic Web pages.

PHP: The Language

It is hard to make a comparison between PHP and other languages, but if you know Perl or C, you will probably feel fairly at home with it. Every instruction must be terminated by a semicolon. Now I will discuss the basic PHP constructions. To test these short listings, you can either run php filename from the UNIX command line, or create a Web page and call it through the configured Web server. In both cases, it's important to put the PHP code between <? and ?>.

Embedded Functions

There are a number of embedded functions available with PHP. The function echo, for example, is used to print something on the screen (or on a Web page):

< echo("Test <BR> \n"); echo "Test <BR> \n"; # Equivalent to the previous

The embedded functions have fairly "flexible" syntax. You can also use the printf function, which works exactly like the equivalent function in C. Both in echo() and in printf(), the escape characters are the same as in C (in the example, I use \n to mean carriage return). Variables Assigning a variable can be done like this:

$a=5; echo("Value: $a<BR>\n");

In this case, $a is a variable, and it's initialized with the value "5" (variables don't have to be declared). You can assign any type of value to a variable: integer, double, or string. The type of the variable depends on the assigned value. The following, for example, is perfectly legal in PHP:

$a="Hello readers !" ; # the previous value of $a is lost

You can also use type casts:


Like this, $a will be a string, containing the characters "1" and "0". Another interesting type of variable in PHP is the vector. You can write:

$a[10]="Hello" ;
$a["something"]="don't know";
Operations on Variables

For numerical variables, integer and double, all the operators are equivalent to those in the C language. For example:

$c=10;     # $c is 10
$c ++;     # $c is 11
$c += 10;  # $c is 21
$c *= 2;   # $c is 42

If you use an arithmetical operator with a string variable, the cast to numerical type is automatically executed. For example:


The result is 11. There is an exception. With an expression like this:


the result is the concatenation of all the strings, since all of the variables are strings. If $var2 were a numerical variable, both $var1 and $var2 would be converted to integer. To concatenate string variables, you should use the "." operator. The last line of the last code should be:


Cycles and Flow Control The flow control instructions are the same as in C. In Listing 1, you can see some examples of using if, for, and while statements. It is interesting that for if and while, you can use syntax similar to that in Basic. It can be useful to have an endwhile statement when, for example, your script is long and you don't want anonymous "}" to close your statements. (All listings for this article are available at in /pub/sysadmin or from the Sys Admin Web site:

User-Defined Functions

Even though PHP is a script language, you can still declare your own functions. The syntax is, again, similar to C. You can see an example in Listing 2. The variable $c is not "visible" outside of the routine sum(); it is a local variable, with a local scope. Both user- defined functions and embedded functions are case insensitive. This means that writing echo() or Echo() gives the same results.

Regular Expressions

The regular expression support is given through the following functions:

  • EReg()
  • EReg_Replace()
  • ERegI()
  • ERegI_Replace()

You need prior knowledge of regular expressions in order to use these functions. You can read the regexp man page to get more information about them.

Comments and HTML Inside the Code

The character # comments the characters that follow it on the same line. C-style comments can also be used: they start with /* and end with */. Something like the following, in PHP, is also perfectly legal:

<? for($i=0;$i<10;$i++){ ?>

<TR> <TD>

<? echo("Value: $i"); ?> <BR>


<? } # end of the for ?>

You can mix PHP code with HTML code without limitation, keeping the HTML code clean. This is why endif and endwhile can be useful: sometimes you may have a closed bracket, without really understanding what it closes!

The External World

Until now, I have just been talking about PHP scripting; now I will discuss how you can use PHP to write Web applications. There are several advantages to using the PHP language for Web development. For example, if a PHP script is called from a form, the interpreter will automatically create some local variables corresponding to the <INPUT> variables in the form. An example of this is shown in Listing 3a and Listing 3b. In the first, there is a form with a hidden field and an input line; in the second, there is a simple PHP script that shows the values of those variables. Both GET and POST methods can be used. The environment variables are also set automatically. In Listing 3, for example, the variable REMOTE_ADDR is set to "". The value of an environment variable can be obtained through the GetEnv() function.

The Database Functions

The ability to connect to a database is a key feature for a Web-oriented language. Using PHP, you can easily connect to, and retrieve data from, a number of database servers. PHP does not contain any drivers; during the compilation, you statically link the C API (provided by the database vendors) to PHP. Therefore, there are different functions for different types of database servers, since those functions come directly from the C API. This means that you have to consider the database manager in use when you implement the programs. In Table 1 you can see the differences between the Mysql and Sybase functions. Using Mysql, a typical cycle to make a query looks like this:

$result mysql("article","SELECT * \
from example"); for($i=0; $i<mysql_numrows($result); \
$i++) { $name = mysql_result($result,$i,"name"); $surname = mysql_result($result,$i,"surname"); $telephone = mysql_result($result,$i,"telephone"); echo("Name : $name<BR>\n"); echo("Surname : $surname<BR>\n"); echo("Telephone: $telephone<BR>\n"); } mysql_freeResult($result);

This script is simple: $result is the pointer to the retrieved data. It is created by the function mysql(), specifying the database name (article) and the SQL query. The result is then shown to the user through a for cycle.

A new feature of PHP 3.0 is "persistent connections". It means that if PHP is compiled as an Apache module, it will not close the connection to the database server when the script ends. That connection will then be available for the next script execution, if the database name, login, and password are the same.

A Sample Script

To demonstrate how to write PHP scripts, let's look at a simple PHP script that searches a database. The database server I used in this example is Mysql from Tcx (, which is downloadable from the site. Let's create a table like the one in Table 2. As you can see, it's an elementary phone book. Here you can search on the surname of someone and get their first name and telephone number. The HTML form is shown in Listing 4a. The PHP script, including the Web interface for the output, is in Listing 4b. The $filter variable is passed from the previous page (as user input), and the query looks like this:

SELECT * from example WHERE surname LIKE '%$filter%'

The results are displayed through a HTML table. I used the mysql_pconnect() function, instead of mysql_connect(). In this way, the link between PHP and Mysql will remain active, even when the script finishes. You can do this only if PHP has been compiled as an Apache Module.

More Complexity?

Using PHP, it is easy to write complex scripts that remain extremely readable and easy to maintain. Suppose, for example, that we have to browse a database table with 10,000 records. The browser lets you see 20 rows of data at a time. You can then "scroll" the data using four buttons: scroll up or down by one page (one page is 20 rows of data) and jump up or down (by two or three pages). This is what Listing 5 is about. The secret of how to scroll the data is in the use of a state variable ($s_start_record), which is changed by clicking on the browser buttons. When the user clicks on the "down" button, the PHP page is reloaded, with $s_start_record incremented by 20. Clicking on the "up" button decrements $s_start_record by 20, and so on. A large part of the script is dedicated to the calculation of the value of $up, $down, $jumpup, and $jumpdown, which can then be assigned to $s_start_record when the corresponding button is pressed. The script can also be easily configured, so that it can scroll any database just by changing the variable with the name starting with "WIZ_".


On a medium-sized project, I found PHP to be robust and easy to work with. The language is less cryptic than Perl, and it is easier to pick up than C. The scripts are readable, and they execute quickly (there is no comparison with Microsoft's ASP, for example). The support through the mailing list is good, and the application is reliable. Based on these factors, I believe your time learning PHP will be well rewarded.

About the Author

Tony "Merc" Mobily is the technical editor of Dev. and Login, and is a technical writer for Edizioni Infomedia of Italy. He is currently located in Sydney, Australia and can be reached at: