O'Reilly logo

PHP Cookbook by Adam Trachtenberg, David Sklar

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required