
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 :-)