Today I was teaching our Junior Developer at Webdrop all about schemas and drupal_write_record() and he asked me a pretty decent question:

By that he meant the function that retrieves a record, given a schema and a primary key.

I gave it some thought and quickly realized that there is no such function! Maybe I'm just being a stickler but not having this function just seems silly.

So I decided to go ahead and write it:

1
2
3
4
56
7
8
9
1011
12
13
14
1516
17
18
19
2021
22
23
24
2526
27
28
29
3031
32
33
34
3536
37
38
39
4041
42
43
44
4546
47
48
49
5051
52
53
54
5556
57
58
59
60
/**
 * Retrieve a record from the databased based on a schema.
 * 
 * @param string $schema
 * @param mixed $key A single value or an array of values * @return array
 */
function drupal_read_record($schema, $key) {
  $key = (array) $key;
  $schema = drupal_get_schema($schema); 
  if (!$schema) {
    return FALSE;
  }
   $pks = $schema['primary key'];
 
  // make sure we have the same ammount of values as we do primary keys
  $count = count($key);
  if ($count !== count($pks)) {    return FALSE;
  }
 
  // build query
  $query = "SELECT * FROM {$schema['name']} WHERE"; 
  for ($i = 0; $i < $count; ++$i) {
    // escape % signs
    $name = str_replace('%', '%%', $pks[$i]);
    $query .= " {$name} = "; 
    // determine variable type
    if (is_numeric($key[$i])) {
      $quotes = FALSE;
      if (is_int($key[$i]) || ctype_digit($key[$i])) {        $placeholder = '%d';
      }
      else {
        $placeholder = '%f';
      }    } else {
      // primary keys shouldn't really be binary in the first place
      $placeholder = '%s';
      $quotes = TRUE;
    } 
    if ($quotes) {
      $query .= '"';
    }
     $query .= $placeholder;
 
    if ($quotes) {
      $query .= '"';
    }  }
 
  $result = db_query($query, $key);
  return db_fetch_object($result);
}

And there you have it!

You can now easily read and write records like this:

1
2
3
4
56
7
8
9
1011
12
13
$write_record = new stdClass();
$write_record->foo = 'bar';
// write record in the database
drupal_write_record('my_schema', $write_record);
 // the primary key (in this case "id") will be populated by drupal_write_record()
$id = $write_record->id;
 
// we can now retrieve records without writing any SQL:
$read_record = drupal_read_record('my_schema', $id); 
// check equality
var_dump($write_record === $read_record);

Please let me know if you find any bugs and thanks for reading!