//, PHP/Script Web Databases Quickly with PHP Scripting Language

Script Web Databases Quickly with PHP Scripting Language

Flexible open-source tool works with seven databases

Find out how to script in PHP, an open-source embedded scripting language for HTML. You’ll learn how to install and configure PHP for use with the Apache Web server on UNIX or the Microsoft Internet Information Server on Windows NT. Then you’ll see by example how to use PHP to route HTML conditionally, to develop a simple database-driven Web application, and to track user sessions with cookies.

Like Microsoft’s Active Server Pages (ASP), PHP is a server-side scripting language for building Web sites. With strong tools for working with more than ten commercial and noncommercial databases, PHP may be the ideal tool if you often rely on databases in your Web development — and especially if you develop for both NT and for UNIX. If you’re ready to try such a flexible open-source tool, read on for details about scripting with PHP. For installation instructions, see Installing PHP version 3 on Windows NT (with the MS Internet Information Server) and Installing PHP version 3 on UNIX (with Apache).

What is PHP?

PHP is an HTML-embedded scripting language for Windows and UNIX designed to help Web developers write dynamically generated pages quickly. Much of its syntax is borrowed from C, Java, and Perl, so developers familiar with those should feel comfortable with PHP right away.

One of PHP’s greatest strengths is its easy interface to a wide variety of databases. Current database modules include:

• Informix

• Microsoft SQL Server

• mSQL

• MySQL

• ODBC

• Oracle

• Sybase

Additionally, a large set of optional components (some of which require installing an external library) provide support for:

• Accessing LDAP servers

• Accessing IMAP mail servers

• Sending SMTP mail

• Accessing SNMP

• Generating PDF documents dynamically

• Generating GIF images dynamically

• Parsing XML documents

• Checking the spelling of words

• Generating WDDX (Web Distributed Data Exchange) data

A short history of PHP

Rasmus Lerdorf originally wrote PHP to manage his home page, hence the name, an acronym for personal home pages. The product quickly gained a following, and later a group of developers rewrote it as PHP 3. PHP development continues today with a core team of developers that include Lerdorf, Andi Gutmans, Zeev Suraski, Stig Bakken, Shane Caraveo, and Jim Winstead.

The current version of PHP is version 3, but a new and faster version has been released this summer in beta. (See “PHP version 4 beta debuts.“)

Simple scripting

Once you install PHP 3, you can try scripting a simple database to get a feel for how it works.

All the programming examples in this article work with both PHP 3 and the beta version of PHP 4. You can download the code examples; see the Resources section of this article.

Standard PHP scripts begin with <?php. And they end with ?>. All PHP variables start with $ just like in Perl.

Also as in Perl, there’s no need to declare variables before using them. You embed all the PHP code within the HTML script, so you can use your favorite text editor to edit PHP files. There’s even optional support for Microsoft’s FrontPage.

You can switch between HTML and PHP scripting as much as you like within a file. For example, note the PHP mixed with HTML in the code in Listing 1, which shows how to create conditional HTML. The ereg function in line 3 of the listing does regular-expression matching on the browser identifier ($HTTP_USER_AGENT) looking to find “MSIE.”

Note that the syntax of the if/else statement is identical to C’s syntax. For more information on PHP functions used in the example listings, check the online PHP Manual (see Resources).

Listing 1. PHP embedded within HTML generates different Web content for users with Microsoft Internet Explorer

Listing 1

 1   <HTML>

2 <?php
3 if ( eregi ( "MSIE", $HTTP_USER_AGENT ) ) {
4 ?>
5 You are using <B>Microsoft Internet Explorer</B>!
6 <?php
7 } else {
8 ?>
9 You are not using Microsoft Internet Explorer.
10 <?php
11 }
12 ?>
13 </HTML>

Switching between HTML and PHP is very handy, but it is sometimes difficult to read. You could rewrite the script in Listing 1 more legibly by using the print function, as shown in Listing 2.

Listing 2. This script performs the same function as Listing 1, but it’s easier to read

Listing 2

 1   <HTML>

2 <?php
3 if ( eregi ( "MSIE", $HTTP_USER_AGENT ) )
4 print ( "You are using <B>Microsoft Internet Explorer</B>!\n" );
5 else
6 print ( "You are not using Microsoft Internet Explorer.\n" );
7 ?>
8 </HTML>

Setting up the database

To keep this example simple on screen, the address book contains only the person’s first name, last name, and e-mail address. If you download the sample code (see Resources), though, you’ll find additional fields that make the application more useful.

This example uses MySQL, but you could use any database that PHP supports. If you use another database (such as Oracle or ODBC), you will need to adapt the listing because PHP’s API is slightly different for each database. For example, for ODBC connections you would use odbc_pconnect instead of connecting with the mysql_pconnect function.

Note that I’ve used MySQL’s auto-increment feature in these examples. It’s a handy way to generate unique integer keys for a table column. You can do the same thing with other databases by using stored procedures and triggers.

First, you must set up a valid MySQL login and password to use PHP. See the MySQL documentation for details (see Resources).

Then use the SQL commands in Listing 3 to set up the PERSON table within a database called ADDRESS_BOOK and add a single entry into the database. Enter the text using the mysql command-line program that is part of the MySQL distribution.

Listing 3. The commands for setting up the MySQL database and for entering the first contact

Listing 3

 1   CREATE DATABASE ADDRESS_BOOK;

2
3 USE ADDRESS_BOOK;
4
5 CREATE TABLE PERSON (
6 ID int(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,
7 LAST_NAME VARCHAR(50) NOT NULL,
8 FIRST_NAME VARCHAR(50) NULL,
9 EMAIL VARCHAR(60) NULL,
10 PRIMARY KEY ( ID )
11 );
12
13 INSERT INTO PERSON VALUES (
14 1, 'Knudsen', 'Craig', 'cknudsen@radix.net' );

Retrieving data

Next you create a directory for the application. A good place for this would be /opt/apache/htdocs/addressbook for an Apache installation, /opt/apache or C:\INETPUB\WWWROOT\ADDRESSBOOK for IIS. The default page for the application will be index.php3; you must configure your Web server to recognize index.php3 as the default page for a directory.

Listing 4 shows the code for index.php3, a script that generates an index page of all entries in the database sorted by last name. Each displayed name serves as a link to a page that allows users to view all fields for the selected name.

Listing 4. PHP script indexes entries by last name

Listing 4: index.php3

 1   <HTML><HEAD>

2 <TITLE>Address Book Index</TITLE>
3 </HEAD><BODY>
4 <H1>Address Book Index</H1>
5 <UL>
6 <?php
7 // Connect to database.
8 $c = mysql_pconnect ( "localhost", "mylogin", "mypasswd" ) ||
9 die ( "Error connecting to database!" );
10 // Get each entry sorted by name.
11 $res = mysql_db_query ( "ADDRESS_BOOK",
12 "SELECT * FROM PERSON ORDER BY LAST_NAME, FIRST_NAME" );
13 while ( $entry = mysql_fetch_array ( $res ) ) {
14 printf ( "<LI><A href=\"view.php3?id=%d\">%s, %s</A>\n",
15 $entry["ID"], $entry["LAST_NAME"], $entry["FIRST_NAME"] );
16 }
17 // Free resources.
18 mysql_free_result ( $res );
19 ?>
20 </UL>
21 <P><HR>
22 <A href="edit.php3">Add New Entry</A>
23 </BODY>
24 </HTML>

In Listing 4 (and in the other listings below), you replace mylogin and mypasswd with the correct login and password for your MySQL database. The first PHP command in Listing 4 is mysql_pconnect on line 8, which establishes a database connection. There is also a mysql_connect function available. The mysql_pconnect command opens a persistent database connection that remains open after the page finishes processing. The subsequent request reuses the connection to improve performance.

Opening a database connection often is the slowest step in a Web application. Connections opened with mysql_connect automatically close after the page finishes processing.

Line 11 of Listing 4 submits to the database an SQL query that asks for all address book entries sorted by last name. A simple while loop, in conjunction with mysql_fetch_array, retrieves each of the entries. The C-style printf statement formats the output for each entry as a link to view the entry. The ID column of each entry serves as a parameter of the URL.

The $res result set variable returned from mysql_fetch_array is an associative array. The values for each table column (LAST_NAME, FIRST_NAME, etc.) are accessed using the name of the column.

Line 18 of Listing 4 frees the resources for the result set. You need not explicitly free the result set, because PHP does that for you automatically after the page is processed, but it’s typically recommended. In some applications, you issue a large number of queries that could use up significant system resources if not they are not freed during execution. (Note: PHP 4.0 automatically frees resources as they are dereferenced.)

Form processing

Form processing under PHP is simple. Listing 5 and Figure 1 show an example of a form for adding a new entry to the address book. A simple table lines up the text-entry fields.

Listing 5. A script to create the form shown in Figure 1

Listing 5

 1   <HTML><HEAD>

2 <TITLE>Address Book Edit</TITLE>
3 </HEAD><BODY>
4 <H1>Edit</H1>
5 <FORM ACTION="add_handler.php3" METHOD="POST">
6 <TABLE border=0>
7 <TR><TD><B>Last Name:</B></TD>
8 <TD><INPUT name="last_name" SIZE=30></TD></TR>
9 <TR><TD><B>First Name:</B></TD>
10 <TD><INPUT name="first_name" SIZE=30></TD></TR>
11 <TR><TD><B>Email:</B></TD>
12 <TD><INPUT name="email" SIZE=40></TD></TR>
13 </TABLE><P>
14 <INPUT TYPE="submit" VALUE="Save">
15 </FORM>
16 <P><HR>
17 <A href="index..php3">Back to Index</A>
18 </BODY></HTML>

The form in Listing 5 specifies the action add_handler.php3, which is shown in Listing 6.

Listing 6 introduces some new topics: form handling, adding new rows to a database, error handling, and redirecting.

The form contains three form elements in new.html : last_name, first_name, and email. To access the value the user entered as the last_name form element, the variable $last_name is used. In this example, I submit the form using the HTTP post method. Use the same technique when using get rather than post .

Line 7 of Listing 6 retrieves the value for the form element last_name, using the following logic:

1. Use the empty function to determine if the user left the last name blank. If so, then set $last_name to “NULL” .

Otherwise, use the addslashes function to add the appropriate backslashes for quotes in the last name and place the last name in double quotes. This prevents the script from submitting invalid SQL requests to MySQL.

2.Conditional syntax just like that found in C, Perl, and Java accomplishes the retrieval on lines 7 and 8. After processing the first name and e-mail data in the same manner, line 14 creates the SQL INSERT command to add the new entry to the database. There’s no value for the ID column because the AUTO_INCREMENT feature of MySQL was used to create the PERSON table. The ID field automatically will have the next available integer.

Line 16 sends the INSERT command to the database. If the request is successful, it returns a positive value, and the user is redirected to the index page index.php3 , using the redirect function on line 19. (Note that the redirect function needs to be called before any HTML output is sent.) The redirect causes processing of this page to end, and an HTTP redirect header will be sent.

The script handles errors by saving the error message to the $error variable, using the mysql_error function, and then not performing the redirect. This allows the HTML at the end of the script to be displayed. In the event of an error, lines 28 through 30 cause the display of an error message and the SQL command that caused the problem.

The downloadable version of the code example (see Resources) includes some additional pages (editing and deleting entries). They do not introduce any additional concepts, so they aren’t included here in the article.

Listing 6. Processing a submitted form

Listing 6

 1   <?php

2 // Connect to database.
3 $c = mysql_pconnect ( "localhost", "mylogin", "mypasswd" ) ||
4 die ( "Error connecting to database!" );
5 // Add a backslash to all characters that would confuse SQL.
6 // Use NULL for values that were left empty.
7 $last_name =
8 empty ( $last_name ) ? "NULL" : "'" . addslashes ( $last_name ) . "'";
9 $first_name =
10 empty ( $first_name ) ? "NULL" : "'" . addslashes ( $first_name ) . "'";
11 $email =
12 empty ( $email ) ? "NULL" : "'" . addslashes ( $email ) . "'";
13 // Add new entry and redirect to index page.
14 $sql = "INSERT INTO PERSON ( LAST_NAME, FIRST_NAME, EMAIL ) ( " .
15 "$last_name, $first_name, $email )";
16 if ( ! mysql_db_query ( "ADDRESS_BOOK", $sql ) ) {
17 $error = mysql_error ();
18 } else {

19 Header ( "Location: index.php3" );
20 exit;
21 }
22 ?>
23 <HTML><HEAD>
24 <TITLE>Address Book Error</TITLE>
25 </HEAD><BODY>
26 <H1>Error</H1>
27 <?php
28 print ( $error );
29 if ( strlen ( $sql ) )
30 printf ( "<P><B>SQL:</B><BLOCKQUOTE><TT>%s</TT></BLOCKQUOTE>", $sql );
31 ?>
32 </BODY>
33 </HTML>

Maintaining sessions

HTTP cookies help track user sessions. For example, the first time a visitor arrives at an online store a session is created. The script then uses the session identifier any time the site needs to store information about the user, such as adding a purchase to an online order. Every request the browser makes to the server then includes the session-identifier cookie, which allows the application to identify the user associated with each request.

You can configure cookies to expire at any specified date and time or after a certain duration. Use the mktime function to generate an expiration based on a specific date or the time function to get the current date and time.

To generate a unique session ID, use the uniqid function. The result will be unique so long as no two IDs are generated at the same microsecond. The uniqid function can optionally be passed the user’s IP address to remove the chance of two users getting the same session identifier.

Because cookies are sent as part of the HTTP header, generating a cookie must take place before any HTML is processed.

Listing 7: Generating a session ID that expires at the end of the session

Listing 7

 1   <?php

2 // Generating cookies must take place before any HTML.
3 // Check for existing "SessionId" cookie
4 $session = $HTTP_COOKIE_VARS["SessionId"];
5 if ( $session == "" ) {
6 // Generate time-based unique id.
7 // Use user's IP address to make more unique.
8 $session = uniqid ( getenv ( "REMOTE_ADDR" ) );
9 // Send session id - expires when browser exits
10 SetCookie ( "SessionId", $session );
11 }
12 ?>
13 <HTML>
14 <HEAD><TITLE>Session Test</TITLE></HEAD>
15 <BODY>
16 Current session id: <?php echo $session ?>
17 </BODY></HTML>

The script starts by checking for an existing session cookie. All cookies are stored in the $HTTP_COOKIE_VARS associative array. Only when there is no existing session identifier will a new one be generated.

The example in Listing 7 provides a session that lasts until the user exits the browser. Listing 8 generates a session cookie that will last until January 1, 2000.

Listing 8: Generating a session ID with a specific expiration date

Listing 8

 1   <?php

2 // Generating cookies must take place before any HTML.
3 // Check for existing "SessionId" cookie
4 $session = $HTTP_COOKIE_VARS["SessionId"];
5 if ( $session == "" ) {
6 // Generate time-based unique id.
7 // Use user's IP address to make more unique.
8 $session = uniqid ( getenv ( "REMOTE_ADDR" ) );
9 // Send session id - expires on Jan 1, 2000
10 SetCookie ( "SessionId", $session, mktime ( 0, 0, 0, 1, 1, 2000 ) );
11 }
12 ?>
13 <HTML>
14 <HEAD><TITLE>Session Test</TITLE></HEAD>
15 <BODY>
16 Current session id: <?php echo $session ?>
17 </BODY></HTML>

Rolling up your sleeves

Now that you’ve seen how to use MySQL and PHP to script Web sites, you have the tools to quickly and efficiently build a database-driven Web site of your own.

Because PHP is an open-source project, product support might not be what you’re accustomed to with a commercial product. However, a number of online resources support developers, including FAQs and a developer-supported annotated online manual. If you run into trouble, you might want to start with the FAQs or the PHP support page.

Installing PHP version 3 on Windows NT

To install and run PHP, in addition to NT you need Microsoft IIS 4, currently distributed as part of the NT 4.0 Option Pack. You also need a database that’s compatible with PHP; I use MySQL for the example listings.

Download the latest binary distribution of PHP version 3 for Win32 (see Resources, at the end of this sidebar). I used PHP 3.0.6 for the example listings in the article, and the distribution file was php-3.0.6-win32.zip. Any 3.x version will work fine for the example listings.

Unzip the zip file into a new directory, such as C:\PHP3.

In the new directory, rename the php3.ini-dist file to php3.ini and edit it with a text editor.

If this is your first time using PHP, you can leave most of the default settings in the .ini file unchanged. You do need to enable one of the database extensions so that PHP can communicate with a database. To follow the examples in the article, choose MySQL, which is readily available for Windows NT and many versions of UNIX. Enable MySQL by removing the semicolon the starts the line extension=php3_mysql.dll.

When you’re finished editing, save a copy of php3.ini in your operating system directory, C:\WINNT. (Be careful if you have used Notepad, which always saves files with the .txt extension.)

Next, you need to edit the NT registry. (Warning: If you damage the registry, you can cause major system problems. Make sure you have backed up before you edit the registry.)

1. From Windows, select Start>Run and type regedit to start the registry editor.

2. Go to the entry for HKEY_LOCAL_MACHINE:System:CurrentControlSet:Services:W3Svc:Parameters:ScriptMap.

3. Choose Edit >New >String Value.

4. In the highlighted area under Name, type .php3 and then press Return.

5. Double-click on the same highlighted area.

6. In the Edit String box that pops up, enter the value C:\PHP3\PHP3.EXE and then click OK.

Next you need to start up the Microsoft Management Console.

1. From Windows, choose Start>Windows NT 4.0 Option Pack>Microsoft Internet Information Server>Internet Service Manager.

2. Right-click the name of the Web server and select Properties from the menu that opens.

3. Click the Home Directories tab and select Script under Permissions.

4. Click Configuration. In the Application Configuration pop-up window that appears, select Add

5. Enter C:\PHP3\PHP.EXE %s %s for Executable, .php for Extension in the popup window.

6. Make sure that Script Engine is enabled.

7.Click OK to close the Add/Edit Application Extension Mapping window.

8. Click OK in the Application Configuration window.

9. Restart the IIS server.

Now that you’ve installed PHP, it’s a good idea to test that it’s working.

1. Enter a test script, such as the one in Win NT Installation Listing 1.

2. Save the script where your browser can access it. For example, you can put it in C:\INETPUB\WWWROOT\PHP\TEST.PHP3.

3. Open a browser.

4. Access the test script from your browser. In the example, you can access the script with the URL http://localhost/php/test.php3.

Listing 9. A simple script to confirm successful installation on Win NT

Listing 9

 <HTML>

<?php phpinfo(); ?>
</HTML>

Installing PHP version 3 on UNIX

To begin installation, you need to first assemble the software you need. including Apache 1.3.x, if you don’t already have it installed (see Resources at the end of this sidebar). For this article, the distribution file was apache_1.3.6.tar.gz. Also download the source code for PHP version 3.. For this article, I used the PHP distribution file php-3.0.9.tar.gz, but any 3.x version will work. You also need a database that’s compatible with PHP; I use MySQL for the example listings.

Put the files apache_1.3.6.tar.gz and php-3.0.9.tar.gz in the same directory. The /tmp directory is a good place, if you have sufficient space on that partition. (I needed 38MB to build Apache and PHP under Linux.)

Next you configure, build, and install in /opt/mysql a PHP-enabled Apache Web server that can access a MySQL database by entering the commands in UNIX Installation Listing 1.

PHP version 4 debuts in beta

Listing 10. Commands for installation and configuration on UNIX

1. gunzip -c

2. gunzip -c

3. cd apache_1.3.6

4. ./configure –prefix=/opt/apache

5. cd ../php-3.0.9

6. ./configure –with-mysql=/opt/mysql –with-apache=../apache_1.3.6 –enable-track-vars –with-config-file-path=/opt/apache/conf

7. make

8. make install

9. cd ../apache_1.3.6

10. ./configure –prefix=/opt/apache –activate-module=src/modules/php3/libphp3.a

11. make

12. make install

Lines 4 and 10 call for Apache to be installed in /opt/apache rather than the default location of /usr/local/apache. Line 6 instructs PHP to build with MySQL, which assumes that you have installed MySQL into /opt/mysql rather than the default /usr/local/mysql. You can build with support for other databases drivers, but the examples in this article call for MySQL. Type configure –help to see the command-line options needed to add support for the other database software.

Once Apache has been installed, you edit the httpd.conf file. If you installed Apache in /opt/apache as shown in the listing above, the configuration file will be /opt/apache/conf/httpd.conf.

In the configuration file, uncomment the line AddType application/x-httpd-php3 .php3.

Note: With PHP version 4 now available in beta release, the PHP authors are encouraging developers to switch from using the file extension .php3 to .php instead. To use the .php extension, add an additional line in the configuration file that says AddType application/x-httpd-php3 .php. (Note that the files distributed in the demo use the php3 extension.)

Now it’s time to start the server. If you installed according to my instructions, use the command /opt/apache/bin/apachectl start.

See your operating system’s documentation to find out how to start Apache automatically at boot time, as the method varies widely among the different versions of UNIX.

The first beta release of PHP version 4 occurred in July. PHP4 uses the Zend scripting language engine, a complete rewrite of the scripting engine for PHP. Although Zend currently is used only with PHP4, Zend eventually will be used as the scripting engine for products other than PHP.

The most ambitious goal of this major update is to improve performance. Preliminary benchmarks performed by the PHP/Zend developers show PHP 4.0 outperforms PHP 3 by a factor of five or more on most applications. The same benchmarks also show that PHP 4.0 compares favorably to ASP’s performance.

PHP4 includes a number of other improvements. The new features of PHP4 include:

• A full-featured debugger, with breakpoints and step-through execution.

• Accessing COM objects, which are abundant for Windows.

• Output buffering to allow you to abort the page in the middle of the script and send a redirect header instead.

• Automatic resource deallocation, which means that there’s no need to remember to free resources in PHP4 because they’re deallocated soon as they are no longer referenced.

• A new foreach loop.

• Boolean terms true and false are predefined.

• Reference support, much like C-style pointers, so that you can use $foo = &$a to make $foo and $a two names to the same variable.

In August, developer Zeev Suraski indicated that PHP 4.0 would most likely be released in November or December. However, the schedule is subject to change based on feedback from the beta releases. As an open-source project, it will be released when the developers decide the product is ready.

Resources

• Download the sample code for this article, including a more complete contact database with additional fields

• Read all about PHP at the PHP home page

• Consult the PHP FAQ

• Read or download the PHP

Manual

• Join the users’ mailing list, search the list archives, and access other PHP support at the PHP support page

• Compare PHP against Active Server Pages benchmarks

• View PHP usage statistics (derived from Netcraft data)

• Read how-to articles and inspect sample code at PHPBuilder.com

• Read the Builder.com Introduction to PHP

• Download the latest version of PHP 3

• Download the latest version of MySQL

• Read the MySQL Reference Manual section on installing on Win NT

• Read or download the MySQL Reference Manual

• Download current Apache Web server software

• Download PHP

• Download the latest stable version (3.22) of MySQL

• Read the MySQL

Reference Manual section on installing

• Read or download the MySQL Reference Manual

• Download the PHP 4.0 beta

• Learn more about the Zend engine

2010-05-26T16:55:57+00:00 October 4th, 2004|MySQL, PHP|0 Comments

About the Author:

Craig Knudsen has been developing Web applications for four years using a variety of tools and languages. Craig currently telecommutes from Fairfax, Virginia as a Web Engineer for ePresence, Inc. of Red Bank, New Jersey. He can be reached at cknudsen@radix.net

Leave A Comment