Tips for Building Web Database Applications with PHP and MySQL

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.

Lets discuss a few tips and frequently asked questions about developing with PHP and MySQL.

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 worldecho "$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 nameecho "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, worldecho "$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 1echo "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 1echo "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"].

It is recommended to use 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.

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"; }?>

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.