Correctly accessing CCK fields in SQL queries
Twice today I've had to deal with writing a SQL query that needed data in a CCK field. The naive approach is to just look at the table and field names and plug them into your query:
<?php
$result = db_query("SELECT COUNT(*) AS count FROM {node} n
INNER JOIN {term_node} tn ON n.vid = tn.vid
INNER JOIN {content_type_date} ctd ON n.vid = ctd.vid
WHERE tn.tid = 25 AND ctd.field_date_value > NOW() AND n.changed > %d", $newtime);
?>Often this will work just fine but since CCK can dynamically alter the database schema (when you add a field to a second content type or change the number of values) the query may break.
Fortunately CCK provides functions for finding a field's table and column names so it's simple to do it correctly:
<?php
$field = content_fields('field_date');
$db_info = content_database_info($field);
?>A var_dump($db_info) gives:
array(2) {
["table"]=>
string(17) "content_type_date"
["columns"]=>
array(2) {
["value"]=>
array(6) {
["type"]=>
string(7) "varchar"
["length"]=>
int(20)
["not null"]=>
bool(false)
["sortable"]=>
bool(true)
["views"]=>
bool(true)
["column"]=>
string(16) "field_date_value"
}
["value2"]=>
array(6) {
["type"]=>
string(7) "varchar"
["length"]=>
int(20)
["not null"]=>
bool(false)
["sortable"]=>
bool(true)
["views"]=>
bool(false)
["column"]=>
string(17) "field_date_value2"
}
}
}After noting that the field has two columns and making our choice, we've got the pieces to plug into the query:
<?php
$field = content_fields('field_date');
$db_info = content_database_info($field);
$result = db_query("SELECT COUNT(*) AS count FROM {node} n
INNER JOIN {term_node} tn ON n.vid = tn.vid
INNER JOIN {". $db_info['table'] ."} ctd ON n.vid = ctd.vid
WHERE tn.tid = 25 AND ctd." . $db_info['columns']['value']['column'] . " > NOW() AND n.changed > %d", $newtime);
?>The query is a bit harder to read, but you've future proofed your code so you won't be back to fix six months from now when you reuse that date field on another node type.
Creating a CCK field in hook_install()
My rule of thumb for deciding what to post on this blog has been to document anything I've spent more than an hour trying to figure out. Today I've got a good one for anyone trying to create CCK fields as part of a module's installation process.
Back in Drupal 5 the Station module was made up of lot of custom code to track various values like a playlist's date or program's genre and DJs. During the upgrade to Drupal 6 I migrated that data into locked, CCK fields that were created when the module was installed. As people started to install the 6.x version of module I began getting strange bug reports about the Station Schedule that I couldn't seem to replicate on my machine.
Themeing a specific CCK field
I wasted more time that I want to admit do trying to figure this out. I was trying theme a specific CCK field named field_images on all the nodes where it appears. The devel_themer module was listing content-field-field_images.tpl.php as a candidate:
But after copying CCK's content-field.tpl.php into my theme and renaming it I couldn't seem to get the theme to pick it up. Roger López gave me the frustratingly simple answer on irc: "i think you need to have both templates in place"... duh. Copied content-field.tpl.php into my theme and everything worked great.
Programatically creating a CCK field in Drupal 6
I spent some time today trying to figure out how to create a CCK field as part of an hook_update_N function. Unlike previous versions of CCK, in 6 it's very easy to manipulate the fields from code.
The first step is to create the field using CCK's UI. Once you've got the field setup the way you'd like it use PHP's var_export() to dump the contents of the node's field as an array:
var_export(content_fields('field_translator_note', 'feature'));Moving CCK description field into the node body
Back in Drupal 4.7 if you were using CCK for nodes the node body was left empty. In Drupal 5 CCK nodes now can have a body. I wanted to move data from a field named description into the node body so I came up with the following snippet fit for running in the devel module's execute PHP block.
