Tips for Building Web Database Applications with PHP and MySQL by Hugh E. Williams, co-author of Web Database Applications with PHP & MySQL 04/04/2002

The most popular platform for developing Web database applications is the open source trio of PHP, MySQL, and the Apache Web server. According to SecuritySpace and Netcraft, the Apache Web server is used at about 60 percent of Web sites; almost half of these servers have support for the PHP scripting language.

PHP's popularity stems from its power and flexibility: it is easy to include PHP scripts in HTML documents; it has a powerful performance-oriented library for accessing MySQL; and it shares syntax with other popular programming languages.

As a backend database management system, MySQL is the perfect partner for PHP. It has a well-deserved reputation for speed in the Web environment, where the commonest class of queries are simple SELECT queries that read from a database.

In this article, I discuss a few tips and frequently asked questions about developing with PHP and MySQL. For more information, get your hands on a copy of Web Database Applications with PHP and MySQL, which I co-authored with David Lane.

1. How to use arrays in PHP.

PHP has numerically- and associatively-indexed arrays. When you're working with databases, the associative array is your friend. I'll show you why later in my third tip, but for now let's chat about arrays and how to use them.

You're probably familiar with the numerically-indexed array. Here's an example of how to create one and print out each element:

<?php
  $temp[0] = "richmond";
  $temp[1] = "tigers";
  $temp[2] = "premiers";

  // Outputs: richmond tigers premiers
  for($x=0;$x<count($temp);$x++)
  {
    echo $temp[$x];
    echo " ";
  }
?>

PHP has an elegant way to compact our example:

<?php
  $temp = array("richmond", "tigers", "premiers");

  // Outputs: richmond tigers premiers
  foreach ($temp as $element)
     echo "$element ";
?>

The foreach statement is great because you can use it to easily extract the elements of an array into a variable, and iterate through each element. I'll show you how double-quoted strings work in my second tip.

An associative array allows arrays to be accessed using a string key rather than a number. Consider this example:

<?php
  $temp["club"] = "richmond";
  $temp["nickname"] = "tigers";
  $temp["aim"] = "premiers";

  // Outputs: tigers
  echo $temp["nickname"];
?>

You can print out both the key and the value using foreach, and you can also use array() to create the array:

<?php
  // Creates the same array as in the last example
  $temp = array("club" => "richmond", 
                "nickname" =>"tigers", 
                "aim" => "premiers");

  // Outputs: club: richmond nickname: tigers aim: premiers
  foreach ($temp as $key => $value)
     echo "$key : $value ";
?>

The PHP manual describes about 50 functions that can be used to process arrays.

2. Including variables in strings in PHP.

Strings are flexible in PHP, and they've become even more so in PHP 4. You'll find that you're always using strings when you work with databases, particularly when you're authoring SQL queries and working with results that come back from MySQL. It's therefore useful to know about the power of the double-quoted string.

The double-quoted string syntax allows variables to be directly included in a string. For example:

<?php
$temp = "hello"
// Outputs: hello world
echo "$temp world";
?>

A double-quoted string can also include several escape sequences:

• \n outputs a linefeed
• \r outputs a carriage return
• \t outputs a tab
• \" outputs a double-quote
• \\ outputs a backslash
• \$ outputs a dollar sign

There are a few catches to the syntax. Variables can't be directly included if the variable can't be unambiguously parsed. For example, this won't work:

<?php
$temp = "hello";
// Won't work, since "s" is included in the variable name
echo "Big $temps from all of us!";
?>

However, it does work when the additional character is invalid in a variable name:

<?php
$temp = "hello";
// Outputs: hello, world
echo "$temp, world";
?>

However, if there is ambiguity, then the new braces syntax adds extra power:

<?php
$temp = "hello";
// Outputs: Big hellos from all of us!
echo "Big {$temp}s from all of us!";
?>

The braces syntax can even be used with arrays:

<?php
$temp = array("one" => 1, "two" => 2);
// Outputs: The first element is 1
echo "The first element is {$temp["one"]}.";
?>

Notice how the double-quotes used for associative array access aren't escaped using the backslash character. Once you're inside the braces, the escape sequences aren't needed.

There's also an alternative to handling associative arrays in double-quoted strings:

<?php
$temp = array("one" => 1, "two" => 2);
// Outputs: The first element is 1
echo "The first element is $temp[one].";
?>

Note the syntax I've used in the above example to access the array element won't work outside of a double-quoted string. Anywhere else the element must be referenced as $temp["one"].

I recommend using the braces syntax almost everywhere. It always works, and it saves you remembering the rules for including variables in strings.

3. Using associative arrays to access query results.

In my first tip, I discussed associative and numeric arrays. Associative arrays are a powerful tool for accessing the results of a query. Consider an example code fragment:

<?php
  // Establish a connection to the MySQL DBMS
  $connection = mysql_connect("localhost", "fred", "shhh");
  // Use the winestore database
  mysql_select_db("winestore", $connection);

  // Run a query through the connection
  $result = mysql_query("SELECT cust_id, surname, 
        firstname FROM customer", $connection);

  // Fetch each row of the results into an array $row
  while ($row = mysql_fetch_array($result))
  {
     echo "ID:\t{$row["cust_id"]}\n";
     echo "Surname\t{$row["surname"]}\n";
     echo "First name:\t{$row["firstname"]}\n\n";   
  }
?>

Related Reading Web Database Applications with PHP, and MySQL By Hugh E. Williams, David Lane Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

The function mysql_fetch_array() retrieves a row of query results into an array. Without its optional second parameter, the returned array is both numerically- and associatively-indexed. Using associative access, the data can be accessed using the attribute names from the query and the underlying database tables. For example, in the script above the attribute data in the cust_id attribute is accessed as $row["cust_id"]. Alternatively, a numeric index of $row[0] can be used to access the same element (the cust_id is numbered zero, since it is the first attribute listed in the SELECT statement).

Associative access is an excellent feature, as the script is more readable than with numeric access, and the access to elements is independent of the order of the attributes in the query. This means we can reorganize the query statement without affecting our processing of the query output. If we throw in the foreach statement that I discussed in my first tip, we've got a powerful set of tools for query result manipulation.

However, there are a few tricks to using associatively-indexed arrays for query processing:

• When two or more attributes in the query have the same name, you need to use aliases so that all of the attributes can be accessed associatively. Without aliases, only the last-named attribute in the SELECT statement is available via the associative array, and the other attributes with identical names must be accessed using numeric indexes. Another approach (and it's the one I advocate in Web Database Applications with PHP and MySQL) is to design your database tables so that attribute names are unique across all tables, thus avoiding the problem altogether.

  For now, let's assume you're stuck with a query that has duplicate attribute names. These attributes can be aliased, as in the following example:

  SELECT winery.name AS wname, 
         region.name AS rname,
  FROM winery, region
  WHERE winery.region_id =  region.region_id;

  The attributes can then be accessed in $row as $row["wname"] and $row["rname"].

• When both a table and attribute name are used in a query, only the attribute name is used to access the data. Consider this example:

  SELECT winery.region_id
  FROM winery

  The attribute is accessed as $row["region_id"].

• Aggregate functions are associatively referenced using their function name. Here's an example:

  SELECT count(*)
  FROM customer;

  The aggregate count() is accessed as $row["count(*)"].

4. Beware of common PHP bugs.

One of the most common problems in debugging PHP scripts is seeing:

• No page rendered by the Web browser when much more is expected

• A pop-up dialog stating that the "Document Contains No Data"

• A partial page when more is expected

Most of these problems are caused not by a bug in script programming logic, but by a bug in the HTML or the HTML produced by the script. For example, if the closing tag is omitted from a </table>, a </form>, or a </frame>, the page may not be rendered. The HTML problem can usually be identified by viewing the HTML page source using the Web browser. With Netscape, the complete output of the erroneous example is shown in the page source view and the HTML problem can hopefully be easily identified.

For compound or hard-to-identify HTML bugs, the W3C validator at http://validator.w3.org/ retrieves a page, analyzes the correctness of the HTML, and issues a report. It is an excellent aid for debugging and last-minute compliance checks before delivery of an application.

Making a mistake with a variable name can cause a script to misbehave. A classic example is the never-ending loop. The result of a never-ending loop is that the Web browser eventually times out and alerts the user that the Web server is not responding to a HTTP request.

The following loop never ends, and no output is produced:

<?php
for($counter=0; $counter<10; $Counter++)
  myFunction();
?>

The variable $counter is never incremented. Instead, another variable $Counter is, and so $counter is always less than 10. Common bugs often result from subtle changes in variable names through changing case, omitting or including underscores, or simple typographic errors.

These errors can usually be detected by setting the PHP error reporting level to a higher sensitivity. Adding this:

<?php
error_reporting(E_ALL);  

for($counter=0; $counter<10; $Counter++)
  myFunction();
?>

to the top of each PHP script will cause PHP to report the use of undefined variables.

5. Using the header() function for one-component querying.

The PHP header() function is a useful tool for controlling the behavior of a Web browser. Let's look at one way it can be used.

In many Web database applications, functionality is included that allows the user to click on a link that performs an action but allows the user to remain on the same page. I call this one-component querying, where the query input component is displayed, but there is no corresponding page that shows output of the query.

One-component querying works as follows. A calling page is shown to the user. This page contains an embedded link or a <form> that is used to request a second resource. Here's an example calling a page that's saved in the file calling.php:

<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD HTML 4.0 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd" >
<html>
  <head>
    <title>Calling page example</title>
  </head>
<body>
<a href="action.php">Click here!</a>
</body>
</html>

When the user clicks on the link, the resource action.php is requested. Here's action.php:

<?php
  // Database functionality goes here

  // Redirect
  header("Location: $HTTP_REFERER");
  exit;
?>

At the server, the script action.php is interpreted by the PHP script engine, and after carrying out the database actions in the script, no output is produced. Instead (and this is the key to one-component querying) an HTTP Location: header is sent as a response to the Web browser, and this header causes the browser to request the original calling.php script. The result is that the calling page is redisplayed, and the user gets the impression that they never left it. Note that I've left out the main body of the action.php script, in a complete example it would typically write data to the database.

There are two common gotchas to watch for:

• You should include an exit statement after a call to header() if you want the script to stop. If you leave out the exit statement, several additional statements may be executed before the headers are sent and this can lead to unpredictable behavior.

• The header() function is the source of a common error message:

  Warning: Cannot add header information - headers already sent...

  The header() function can only be called before HTML is output, and this includes any white space at the top of the file (the output control functions can be used to create an exception to this rule but I won't discuss that here). For example, if there's a blank line or single space character before the script begin tag <?php, then HTML has been output and the error occurs. If you see this error, check your script carefully for characters outside the PHP script tags.

6. The reload problem and how to avoid it.

In the last tip, I showed you how to use the header() function to build a one-component querying module. In this tip, I'll show you how to use the header() function to avoid another common problem that you'll see in many Web database applications, what I'll call the reload problem.

Let's imagine you've authored the following script that's stored in the file addcust.php:

<?php
  $query = "INSERT INTO customer
            SET surname = $surname,
                firstname = $firstname";

  // Establish a connection to the MySQL DBMS
  $connection = mysql_connect("localhost", "fred", "shhh");
  // Use the winestore database
  mysql_select_db("winestore", $connection);

  // Run a query through the connection
  $result = mysql_query($query, $connection);
?>
<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD HTML 4.0 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd" >
<html>
  <head>
    <title>Customer insert</title>
  </head>
<body>
I've inserted the customer for you.
</body>
</html>
?>

The script adds a new customer to the customer table and reports its success by outputting an HTML page. Assuming the script is hosted by the Web server at www.webdatabasebook.com, you can request it with the following URL:

http://www.webdatabasebook.com/addcust.php?surname=Smith&firstname=Fred

If you request it once, then the customer is added once. But what happens if you then press refresh or reload? The answer is that a duplicate customer is added! In fact, even resizing the browser or printing the page has the same effect. This is the reload problem.

The reload problem can be solved with the header() function. Here's a new version of addcust.php:

<?php
  $query = "INSERT INTO customer
            SET surname = $surname,
                firstname = $firstname";

  // Establish a connection to the MySQL DBMS
  $connection = mysql_connect("localhost", "fred", "shhh");
  // Use the winestore database
  mysql_select_db("winestore", $connection);

  // Run a query through the connection
  $result = mysql_query($query, $connection);
  header("Location: cust_receipt.php");
?>

This new script adds the customer and then redirects the browser to a new resource cust_receipt.php. This new resource looks like this:

<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD HTML 4.0 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd" >
<html>
  <head>
    <title>Customer insert</title>
  </head>
<body>
I've inserted the customer for you.
</body>
</html>

The overall effect is that the new custadd.php script adds the customer to the database and then the browser immediately redirects to the receipt page. The user can then reload the receipt page (or print it, resize it, or bookmark it), and there are no side effects. The reload problem is solved.

7. Locking for performance in Web database applications.

Locking is primarily used to ensure that database reads and writes can execute safely. Tables can be locked with MySQL's default table type by issuing a LOCK TABLES with either the READ or WRITE option. When tables are locked with READ, other users can also read, but no one can WRITE. When a table is locked with WRITE, no one else can read or write the table. Web Database Applications with PHP and MySQL discusses the situations where locks should and shouldn't be used in Web database applications.

Locking can also be a useful performance tool to optimize database operations. Imagine a situation where a report is urgently needed. With other users running queries and using system resources, the report may be slow to run. A solution is to use LOCK TABLES with the WRITE option to stop other users running queries or database updates, and to have exclusive access to the report tables for the query duration. This permits better optimization of the query processing by the DBMS, dedication of all of the system resources to the query, and faster disk access.

The downside of locking for performance is that other users can't use the locked tables and, therefore, locking for performance should be sparingly used.

8. Developing fast scripts with mysql_unbuffered_query().

This performance-oriented query function was introduced as an alternative to mysql_query() in PHP 4.0.6. Both run an SQL query through a connection, and return a result resource handle that can be used to process the query results. The difference between the two functions is that mysql_unbuffered_query() executes a query and then immediately returns -- the script doesn't block waiting for the query to finish and the answers to be buffered. This means that a slow query can be started, and then script processing can continue while the query runs at the DBMS.

This is a nice feature and, with the few exceptions I discuss next, a call to mysql_query() can simply be replaced with a call to mysql_unbuffered_query().

There are a few tricks to using mysql_unbuffered_query():

• The number of rows produced by the query can't be checked with mysql_num_rows() because the total size of the output isn't known.

• You must finish processing a query on a connection before you issue another. This doesn't mean you have to retrieve all of the results produced by the first query; rather, it means you won't be able to access the results after you run the next query. A workaround is to use two or more DBMS connections opened with mysql_connect() or mysql_pconnect().

• A script won't finish until its DBMS connections are no longer active. This is a trap: function calls to mysql_unbuffered_query() will return immediately, but the script won't end until all of the queries finish running. There's a workaround to this for SELECT queries. If your connection was opened with mysql_connect() then you can close it with mysql_close() and the script will end before the queries do.

  If your connection was opened with mysql_pconnect() or the query is an UPDATE, INSERT, or DELETE, then this workaround won't work, and you'll have to wait for the query to complete.

Finding Out More

I've presented a few tips here to help you develop better Web database applications with MySQL and PHP. There's a lot more that PHP and MySQL can do, and our new book, Web Database Applications with PHP and MySQL, covers much of the theory and practice. There's also plenty of information on the Web. Have a look at the PHP Web site as a starting point and, in particular, their Links section.

Hugh E. Williams is a software design engineer at Microsoft's Windows Live Search in Redmond, WA. Previously, he was the Associate Professor in Information Retrieval at RMIT University in Melbourne, Australia.

Return to the ONLamp.com.

Copyright © 2007 O'Reilly Media, Inc.