Drupal 6 to 7 upgrades

• from Larry Garfield

The Select builder adds a LOT of overhead. You should not use it unless you need one of the things it offers: Dynamic query structure, hook_query_alter, certain db-unportable features that don’t have a simpler version (random ordering, for instance), etc.

For the run of the mill SQL query, db_query() is several times faster than db_select(). If you need a limit query, db_query_range() is still there.

Here are some useful links for upgrading from Drupal 6 to 7

• db_query select queries, comparing D6 and D7
• blog.worldempire.ch/…ule-dbtng HowTo: Convert a module to DBTNG —excellent resource
• api.drupal.org/…on/db_update/7 API for update queries
• drupal.org/node/310080 Update queries
• www.drupaler.co.uk/…mment/1626 Database updates
• www.group42.ca/…ly_in_drupal_7 Creating and Updating Nodes Programmatically
• drupal.org/node/1214150 Query nodes on location cck data using EntityFieldQuery
• www.piliapp.com/…-syntax-check Paste in your php code and it will do a syntax check

Basic update from D6 to D7 to get a single value

$sql = "SELECT field_facilityid_value
        FROM content_type_facility AS c
        JOIN node AS n ON n.vid = c.vid
        WHERE n.nid = %d";
$facilityid = db_result(db_query($sql, $nodenum));

• New in D7

$sql = "SELECT field_facilityid_value
        FROM content_type_facility AS c
        JOIN node AS n ON n.vid = c.vid
        WHERE n.nid = :nid"; // string placeholders should not be in quotes
$condition = array(':nid' => $nodenum);
$facilityid = db_query($sql, $condition)->fetchField();

Now get multiple values

$sql = "SELECT field_providerid_value FROM content_type_provider
        WHERE field_providerid_value LIKE '%s'
        ORDER BY field_category_value, field_providerid_value";
$results = db_query($sql,$providerid);
while ( $datar = db_fetch_array($results) ) {
  $providers[] = $datar['field_providerid_value'];
}

• New in D7 using a returned object

$sql = "SELECT field_providerid_value
        FROM content_type_provider
        WHERE field_providerid_value LIKE :pid
        ORDER BY field_category_value, field_providerid_value";
$condition = array(':pid' => $providerid);
$results = db_query($sql, $condition);
foreach ($results as $record) {
  $providers[] = $record->field_providerid_value;
}

CCK Selects

• The old model for fetching all the fields of a single node directly:

$sql = "SELECT * FROM content_type_facility AS c
        JOIN node AS n ON n.vid = c.vid
        WHERE field_facilityid_value = '%s'";
$results = db_query($sql,$facilityid);
$datar = db_fetch_array($results);
$f['onlinestatus'] = $datar['field_onlinestatus_value'];
$f['letter']       = $datar['field_letter_value'];
$f['city']         = $datar['field_city_value'];
$f['address']      = $datar['field_address_value'];

• New in D7 using field_get_items() after loading the node:

$node = node_load($nid);
$fld = 'onlinestatus';  $i = field_get_items('node', $node, 'field_'.$fld); $f[$fld] = $i[0]['value'];
$fld = 'letter';        $i = field_get_items('node', $node, 'field_'.$fld); $f[$fld] = $i[0]['value'];
$fld = 'city';          $i = field_get_items('node', $node, 'field_'.$fld); $f[$fld] = $i[0]['value'];
$fld = 'address';       $i = field_get_items('node', $node, 'field_'.$fld); $f[$fld] = $i[0]['value'];