Recipe 10.12. Building Queries Programmatically


10.12.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.12.2. Solution

To construct an UPDATE query, build an array of field/value pairs and then implode( ) together each element of that array, as shown in Example 10-31.

Building an UPDATE query

<?php // A list of field names $fields = array('symbol','planet','element'); $update_fields = array(); $update_values = array(); foreach ($fields as $field) {     $update_fields[] = "$field = ?";     // Assume the data is coming from a form     $update_values[] = $_POST[$field]; } $st = $db->prepare("UPDATE zodiac SET " .                    implode(',', $update_fields) .                    'WHERE sign = ?'); // Add 'sign' to the values array $update_values[] = $_GET['sign']; // Execute the query $st->execute($update_values); ?>

For an INSERT query, do the same thing, although the SQL syntax is a little different, as Example 10-32 demonstrates.

Building an INSERT query

<?php // A list of field names $fields = array('symbol','planet','element'); $placeholders = array(); $values = array(); foreach ($fields as $field) {     // One placeholder per field     $placeholders[] = '?';     // Assume the data is coming from a form     $values[] = $_POST[$field]; } $st = $db->prepare('INSERT INTO zodiac (' .                    implode(',',$fields) .                    ') VALUES (' .                    implode(',', $placeholders) .                    ')'); // Execute the query $st->execute($values); ?>

10.12.3. Discussion

Placeholders make this sort of thing a breeze. Because they take care of escaping the provided data, you can easily stuff user-submitted data into programatically generated queries.

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

pc_build_query( )

<?php function pc_build_query($db,$key_field,$fields,$table) {     $values = array();     if (! empty($_POST[$key_field])) {         $update_fields = array();         foreach ($fields as $field) {             $update_fields[] = "$field = ?";             // Assume the data is coming from a form             $values[] = $_POST[$field];         }         // Add the key field's value to the $values array         $values[] = $_POST[$key_field];         $st = $db->prepare("UPDATE $table SET " .                    implode(',', $update_fields) .                    "WHERE $key_field = ?");     } else {         // Start values off with a unique ID         // If your DB is set to generate this value, use NULL instead         $values[] = md5(uniqid());         $placeholders = array('?');         foreach ($fields as $field) {             // One placeholder per field             $placeholders[] = '?';             // Assume the data is coming from a form             $values[] = $_POST[$field];         }         $st = $db->prepare("INSERT INTO $table ($key_field," .                            implode(',',$fields) . ') VALUES ('.                            implode(',',$placeholders) .')');     }     $st->execute($values);     return $st; } ?>

Using this function, you can make a simple page to edit all the information in the zodiac table, shown in Example 10-34.

A simple add/edit record page

<?php $db = new PDO('sqlite:/usr/local/data/zodiac.db'); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $fields = array('sign','symbol','planet','element',                 'start_month','start_day','end_month','end_day'); $cmd = isset($_REQUEST['cmd']) ? $_REQUEST['cmd'] : 'show'; switch ($cmd) {  case 'edit':  try {     $st = $db->prepare('SELECT ' . implode(',',$fields) .                        ' FROM zodiac WHERE id = ?');     $st->execute(array($_REQUEST['id']));     $row = $st->fetch(PDO::FETCH_ASSOC);  } catch (Exception $e) {      $row = array();  }  case 'add':      print '<form method="post" action="' .            htmlentities($_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 = htmlentities($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':      try {        $st = pc_build_query($db,'id',$fields,'zodiac');        print 'Added info.';      } catch (Exception $e) {        print "Couldn't add info: " . htmlentities($e->getMessage());      }      print '<hr>';  case 'show':  default:      $self = htmlentities($_SERVER['PHP_SELF']);      print '<ul>';      foreach ($db->query('SELECT id,sign FROM zodiac') as $row) {          printf('<li> <a href="%s?cmd=edit&id=%s">%s</a>',                 $self,$row['id'],$row['sign']);      }      print '<hr><li> <a href="'.$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 text boxes 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.

Adding and editing a record


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 and uses that new ID in an INSERT query that adds a row to the table. To have pc_build_query( ) respect a database's AUTOINCREMENT setting, start $values off with null instead of md5(uniqid( )).

10.12.4. See Also

Recipe 10.7 for information about placeholders, prepare( ), and execute( ); documentation on PDO::prepare( ) at http://www.php.net/PDO::prepare and on PDOStatement::execute( ) at http://www.php.net/PDOStatement::execute([2009][2009] ).




PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net