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

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.

Pages: 1, 2