Home

A function to simplify SQL queries

Thu, 02/11/2010 - 17:13 — infojunkie

Every time I have to write one of these SQL query loops, I feel unclean:

<?php
  // Oh no, not another db_query loop!
  $resource = db_query("SELECT nid, title FROM {node}");
  while ($row = db_fetch_array($resource)) {
    $result[$row['nid']] = $row['title'];
  }
  // Get on with your normal life here...
  do_something_useful($result);
?>

So I decided to create a function that encapsulates the common scenarios for SQL queries. Here it is:

<?php

define('QUERY_ARRAY_MODE_STRAIGHT', 0);
define('QUERY_ARRAY_MODE_SINGLETON', 1);
define('QUERY_ARRAY_MODE_PAIR_KEY_VALUE', 2);
define('QUERY_ARRAY_MODE_SINGLETON_KEY_VALUE', 3);

/**
 * Perform a SQL query and return the results in an array.
 *
 * @param $query
 *   The SQL query
 * @param $mode
 *   The array creation mode:
 *   - QUERY_ARRAY_MODE_STRAIGHT: Array entries are result rows.
 *   - QUERY_ARRAY_MODE_SINGLETON: Query only returns one column per result, and array entries are this column's values.
 *   - QUERY_ARRAY_MODE_PAIR_KEY_VALUE: Query returns a (key, value) pair per result, and array entries are keyed values.
 *   - QUERY_ARRAY_MODE_SINGLETON_KEY_VALUE: Query returns one column per result, and array entries are *both* keyed and filled with this column's values. 
 * @param $args
 *   The query arguments
 * @return
 *   An array containing the results organized according to $mode.
 */
function db_query_array($query, $mode = QUERY_ARRAY_MODE_STRAIGHT, $args = array()) {
  $result = db_query($query, $args);
  $return = array();
  if (!$result) return $return;

  switch ($mode) {
  case QUERY_ARRAY_MODE_STRAIGHT:
    while ($row = db_fetch_array($result)) {
      $return[] = $row;
    }
    break;
  case QUERY_ARRAY_MODE_SINGLETON:
    while ($row = db_fetch_array($result)) {
      $return[] = array_shift($row);
    }
    break;
  case QUERY_ARRAY_MODE_PAIR_KEY_VALUE:
    while ($row = db_fetch_array($result)) {
      $key = array_shift($row);
      $return[$key] = array_shift($row);
    }
    break;
  case QUERY_ARRAY_MODE_SINGLETON_KEY_VALUE:
    while ($row = db_fetch_array($result)) {
      $value = array_shift($row);
      $return[$value] = $value;
    }
    break;
  }
  return $return;
}
?>

The example above would be rewritten as:

<?php
$result = db_query_array("SELECT nid, title FROM {node}", QUERY_ARRAY_MODE_PAIR_KEY_VALUE);
?>

If you have other common scenarios please let me know and I will update this function. Happy coding!

Comments

Tue, 02/16/2010 - 13:22 — infojunkie

Added new mode

Added new mode QUERY_ARRAY_MODE_SINGLETON_KEY_VALUE because I needed it today.

Fri, 02/12/2010 - 06:47 — agentrickard (not verified)

Argument order

Nice! The $query argument should go first, since the other arguments are optional. Probably $query, $args, $mode, since $mode is the least likely to change.

Fri, 02/12/2010 - 10:19 — infojunkie

That's how I originally had

That's how I originally had it, but then I didn't know how to make the arguments have a default value. I don't think the mode would be least likely to change though, as each of these modes is pretty common.

Thu, 02/11/2010 - 18:33 — Larry Garfield (not verified)

You'll like Drupal 7

Most of these sorts of utilities are now baked into the DB layer itself in Drupal 7. To wit: http://drupal.org/node/310072 (Scroll down to "result sets".)

Thu, 02/11/2010 - 19:06 — infojunkie

That's neat! Welcome Drupal 7

That's neat! Welcome Drupal 7 :-)