PHP CookBook

Recipe 10.13 Building Queries Programmatically

10.13.1 Problem

You want to construct an INSERT or UPDATE query from an array of field names. For example, you want to insert a new user into your database. Instead of hardcoding each field of user information (such as username, email address, postal address, birthdate, etc.), you put the field names in an array and use the array to build the query. This is easier to maintain, especially if you need to conditionally INSERT or UPDATE with the same set of fields.

10.13.2 Solution

To construct an UPDATE query, build an array of field/value pairs and then join( ) together each element of that array:

$fields = array('symbol','planet','element');

$update_fields = array();
foreach ($fields as $field) {
    $update_fields[] = "$field = " . $dbh->quote($GLOBALS[$field]);
}
$sql = 'UPDATE zodiac SET ' . join(',',$update_fields) 
    . ' WHERE sign = ' . $dbh->quote($sign);

For an INSERT query, construct an array of values in the same order as the fields, and build the query by applying join( ) to each array:

$fields = array('symbol','planet','element');

$insert_values = array();
foreach ($fields as $field) {
    $insert_values[] = $dbh->quote($GLOBALS[$field]);
}
$sql = 'INSERT INTO zodiac (' . join(',',$fields) . ') VALUES ('
       . join(',',$insert_values) . ')';

If you have PEAR DB Version 1.3 or later, use the DB::autoPrepare( ) method:

$fields = array('symbol','planet','element');

// UPDATE: specify the WHERE clause
$update_prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_UPDATE,
                                'sign = ?');
$update_values = array();
foreach ($fields as $field) { $update_values[] = $GLOBALS[$field]; }
$update_values[] = $GLOBALS['sign'];
$dbh->execute($update_prh,$update_values);

// INSERT: no WHERE clause
$insert_prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_INSERT);
$insert_values = array();
foreach ($fields as $field) { $insert_values[] = $GLOBALS[$field]; }
$dbh->execute($insert_prh,$insert_values);

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");
    exit;
}

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');
$dbh->setFetchMode(DB_FETCHMODE_OBJECT);

$fields = array('sign','symbol','planet','element',
                'start_month','start_day','end_month','end_day');

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">',
                $_REQUEST['id']);
     }
     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">,
                $field,$field,$value);
         printf('</td></tr>');
     }
     print '<tr><td></td><td><input type="submit" value="Save"></td></tr>';
     print '</table></form>';
     break;
 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>';
 default:
     $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>',
                $_SERVER['PHP_SELF'],$row->id,$row->sign);
     }
     print '<hr><li> <a href="'.$_SERVER['PHP_SELF'].'?cmd=add">Add New</a>';
     print '</ul>';
     break;
}

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. Adding and editing a record

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.

10.13.4 See Also

Documentation on DB::autoPrepare( ) at http://pear.php.net/manual/en/core.db.autoprepare.php; new versions of PEAR DB are available at http://pear.php.net/package-info.php?package=DB.