PHP Cookbook

10.12. Building Queries Programmatically

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.

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, ...