Script Web Databases Quickly With PHP Retrieving Data

Script Web databases quickly with PHP

By Craig Knudsen - 1999-09-01 Page: 1 2 3 4 5 6 7 8

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.)

View Script Web databases quickly with PHP Discussion

Page: 1 2 3 4 5 6 7 8 Next Page: Form Processing

First published by IBM developerWorks