The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

How do I create flexible SQL statements?

SQL is a powerful language for manipulating data. Using PHP, we can construct SQL statements out of variables, which can be useful for sorting a table by a single column or displaying a large result set across multiple pages.

Here is a simple example that lets us sort the results of a query by a table column:

Example 3.16. 11.php (excerpt)        
       
// A query to select all articles        
$sql = "SELECT * FROM articles";        
       
// Initialize $_GET['order'] if it doesn't exist        
if (!isset($_GET['order']))        
 $_GET['order'] = FALSE;        
       
// Use a conditional switch to determine the order        
switch ($_GET['order']) {        
 case 'author':        
   // Add to the $sql string        
   $sql .= " ORDER BY author";        
   break;        
 default:        
   // Default sort by title        
   $sql .= " ORDER BY title";        
   break;        
}        
       
// Run the query, identifying the connection        
if (!$queryResource = mysql_query($sql, $dbConn)) {        
 trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);        
}        
?>        
<table>        
<tr>        
<th><a href="<?php echo $_SERVER['PHP_SELF']; ?>?order=title"        
 >Title</a></th>        
<th><a href="<?php echo $_SERVER['PHP_SELF']; ?>?order=author"        
 >Author</a></th>        
</tr>        
<?php        
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {        
 echo "<tr>\n";        
 echo "<td>" . $row['title'] . "</td>";        
 echo "<td>" . $row['author'] . "</td>";        
 echo "</tr>\n";        
}        
?>        
</table>

Within the switch statement, I've generated part of the SQL statement "on the fly," depending on a GET variable the script receives from the browser.

This general approach can be extended to WHERE clauses, LIMIT clauses, and anything else you care to consider with SQL. We'll look at this in more detail when we construct a paged result set in Chapter 9, Web Page Elements.

Persistence Layers: Database Interaction Without SQL

Persistence layers are becoming popular, and are well supported in PHP today. A persistence layer is a collection of classes that represents the tables in your database, providing you with an API through which all data exchanged between the database and the PHP application passes. This generally takes away the need for you to write SQL statements by hand, as the queries are generated and executed automatically by the PHP classes that represent the data.

Because SQL is a fairly well defined standard, it also becomes possible to have a persistence layer generated automatically. A program can examine your database schema and produce the classes that will automatically read and update it. This can be a very significant time saver; simply design your database, run the code generation tool, and the rest is "just" a matter of formatting a little (X)HTML.

A prime example of a persistence layer is PEAR::DB_DataObject, which builds on top of the PEAR::DB database abstraction library, and automatically generates a layer of classes with which to access your tables.

Persistence layers in general, and PEAR::DB_DataObject in particular, are discussed in the section called "Do I really need to write SQL?".

How do I find out how many rows I've selected?

It's often useful to be able to count the number of rows returned by a query before you do anything with them, such as when you're splitting results across pages or producing statistical information. When selecting results, you can use either PHP or MySQL to count the number of rows for you.

Counting Rows with PHP

With PHP, the function mysql_num_rows returns the number of rows selected, but its application can be limited when you use unbuffered queries (see the section called "How do I fetch data from a table?"). The following code illustrates the use of mysql_num_rows:

Example 3.17. 12.php (excerpt)        
       
// A query to select all articles        
$sql = "SELECT * FROM articles ORDER BY title";        
       
// Run the query, identifying the connection        
$queryResource = mysql_query($sql, $dbConn);        
       
// Fetch the number of rows selected        
$numRows = mysql_num_rows($queryResource);        
       
echo $numRows . ' rows selected<br />';        
       
// Fetch rows from MySQL one at a time        
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {        
   echo 'Title: '  . $row['title']  . '<br />';        
   echo 'Author: ' . $row['author'] . '<br />';        
   echo 'Body: '   . $row['body']   . '<br />';        
}

The mysql_num_rows function, demonstrated in the above example, takes a result set resource identifier and returns the number of rows in that result set.

Note that the related function, mysql_num_fields, can be used to find out how many columns were selected. This can be handy when you're using queries like SELECT * FROM table, but you don't know how many columns you've selected.