home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

Book HomePHP CookbookSearch this book

10.13. Building Queries Programmatically

10.13.3. Discussion

The DB::autoPrepare( ) method is concise and easy to use if you have a recent version of DB. PHP 4.2.2 comes with DB 1.2. Newer versions of DB can be downloaded from PEAR. Use method_exists( ) to check whether your version of DB supports autoPrepare( ):

if (method_exists($dbh,'autoPrepare')) {
    $prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_UPDATE','sign = ?');
    // ...
} else {
    error_log("Can't use autoPrepare");

If you can't use DB::autoPrepare( ), the array-manipulation techniques shown in the Solution accomplish the same thing. If you use sequence-generated integers as primary keys, you can combine the two query-construction techniques into one function. That function determines whether a record exists and then generates the correct query, including a new ID, as shown in the pc_build_query( ) function in Example 10-1.

Example 10-1. pc_build_query( )

function pc_build_query($dbh,$key_field,$fields,$table) {

    if (! empty($_REQUEST[$key_field])) {
        $update_fields = array();
        foreach ($fields as $field) {
            $update_fields[] = "$field = ".$dbh->quote($_REQUEST[$field]);
        return "UPDATE $table SET " . join(',',$update_fields) .
               " WHERE $key_field = ".$_REQUEST[$key_field];
    } else {
        $insert_values = array();
        foreach ($fields as $field) {
            $insert_values[] = $dbh->quote($_REQUEST[$field]);
        $next_id = $dbh->nextId($table);
        return "INSERT INTO $table ($key_field," . join(',',$fields) . 
               ") VALUES ($next_id," . join(',',$insert_values) . ')';

Using this function, you can make a simple page to edit all the information in the zodiac table:

require 'DB.php';

$dbh = DB::connect('mysql://test:@localhost/test');

$fields = array('sign','symbol','planet','element',

switch ($_REQUEST['cmd']) {
 case 'edit':
     $row = $dbh->getRow('SELECT ' . join(',',$fields) . 
                         " FROM zodiac WHERE id = ?",array($_REQUEST['id']));
 case 'add':
     print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">';
     print '<input type="hidden" name="cmd" value="save">';
     print '<table>';
     if ('edit' == $_REQUEST['cmd']) {
         printf('<input type="hidden" name="id" value="%d">',
     foreach ($fields as $field) {
         if ('edit' == $_REQUEST['cmd']) {
             $value = htmlspecialchars($row->$field);
         } else {
             $value = '';
         printf('<tr><td>%s: </td><td><input type="text" name="%s" value="%s">,
     print '<tr><td></td><td><input type="submit" value="Save"></td></tr>';
     print '</table></form>';
 case 'save':
     $sql = pc_build_query($dbh,'id',$fields,'zodiac');
     if (DB::isError($sth = $dbh->query($sql))) {
         print "Couldn't add info: ".$sth->getMessage();
     } else {
         print "Added info.";
     print '<hr>';
     $sth = $dbh->query('SELECT id,sign FROM zodiac');
     print '<ul>';
     while ($row = $sth->fetchRow()) {
         printf('<li> <a href="%s?cmd=edit&id=%s">%s</a>',
     print '<hr><li> <a href="'.$_SERVER['PHP_SELF'].'?cmd=add">Add New</a>';
     print '</ul>';

The switch statement controls what action the program takes based on the value of $_REQUEST['cmd']. If $_REQUEST['cmd'] is add or edit, the program displays a form with textboxes for each field in the $fields array, as shown in Figure 10-1. If $_REQUEST['cmd'] is edit, values for the row with the supplied $id are loaded from the database and displayed as defaults. If $_REQUEST['cmd'] is save, the program uses pc_build_query( ) to generate an appropriate query to either INSERT or UPDATE the data in the database. After saving (or if no $_REQUEST['cmd'] is specified), the program displays a list of all zodiac signs, as shown in Figure 10-2.

Figure 10-1

Figure 10-1. Adding and editing a record

Figure 10-2

Figure 10-2. Listing records

Whether pc_build_query( ) builds an INSERT or UPDATE statement is based on the presence of the request variable $_REQUEST['id'] (because id is passed in $key_field). If $_REQUEST['id'] is not empty, the function builds an UPDATE query to change the row with that ID. If $_REQUEST['id'] is empty (or it hasn't been set at all), the function generates a new ID with nextId( ) and uses that new ID in an INSERT query that adds a row to the table.

Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.