A function to simplify SQL queries

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

Added new mode

Added new mode QUERY_ARRAY_MODE_SINGLETON_KEY_VALUE because I needed it today.

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.

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.

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".)

That's neat! Welcome Drupal 7

That's neat! Welcome Drupal 7 :-)