Direct DB access with custom (CCK) node types in Drupal

Submitted by boaz on Thursday, February 7, 2008.

Hi,

I had this experience a few days ago which I could definitely spare: my code that runs custom query against a Drupal site's DB failed completely. One day, out of the blue, my code topped working and I got those SQL problems errors. It appeared as if the DB has changed under my feet! C$%p! - DB corruption???
Not really... Had I known a bit more... .

Here's the deal:
You work with Drupal, you have your own node types, created with CCK module of course.
Now, for whatever reason it might be (performance, in my case; see more below), you need to have direct access to fetch data that is stored in your custom content types. Cool.

So, you peek in your DB back and forth. You probably already have some experience and knowledge about it (or not). And you learn your DB schema, especially the relevant tables for about your content types. Next, with your sharpened knowledge you head straight to writing your code that fetches the info you need.

In my case, the needed fields and data were stored in a content type named "user sales data" and in the table "content_type_user_sales_data". I thought that that's were it ends - I got the needed info, I know what to query, the world is mine. Mine! :-)
Cool. Or not really. See, the problem is that CCK can change the DB schema behind the scenes, without your permission. I does so in cases such as using the same field in more than one content type (I think) and it result in taking out the field data from the content type table, to an external table which holds only the field data (along with the needed key - NID). This way, CCK creates new tables behind the scenes in the form of "content_field_*".
Such a change happened to me and caused my code to break. I was puzzled the explore the "new" DB and see the actual tables changes. I guessed it wasn't a corruption cause it seemed too "clean".

What I was not aware is that CCK provides the developer API with which he/she should get the table names and column names in the DB in which certain field data for a certain content type is stored. I consulted with a colleague and was told of this API (thanks Zohar!). I searched it on the web and I find that there's little info about it on the web. That's why I publish this blog post :-) .

So what is this API? Here's a quick overview:

In the end, you'd want to have such a dynamic query to run:

SELECT $field_columnName from $table_name;

With this query you can't go wrong. Now, we just need to fill those variables in this "dynamic query".
We will use two API functions for that:

content_fields($field_name = NULL, $content_type_name = NULL) and content_database_info($field)

The first is used to fetch a "field array", which is a standard data structure describing a field in the system. The second API function needs this data structure and it fetches the actual needed info - the table name and column name inside that table in which the info is stored.
Some woking example is in place:

// below we get the needed field array, for our specific content type (the second parameter is not a mandatory one):
$field_info = content_fields("field_assigned_sales_person", "user_sales_data");
// Next, we'll used the fetched "field array" to get the database info:

$db_info = content_database_info($field_info);
// Now, I'm taking the needed table name and field name. You might need to
debug a bit here. Other field names are not always found in the same
array elements as below (i.e, in ['columns']['uid']['column']). You'd need to var_dump()/var_export()/use-debugger here to
get the array details.

$assigned_sales_prs_tblName = $db_info['table'];
$assigned_sales_prs_fieldName = $db_info['columns']['uid']['column'];
// Now I can do the query itself:
$result = db_query("select nid, $assigned_sales_prs_fieldName from {$assigned_sales_prs_tblName} where nid in ($nids_csv)");

// next, is simple db_fetch_array() loop... .


The key thing to remember is that the content_database_info() needs a field array, not a field name.

Now, there are guides like this on the web, and in drupal.org site itself, but it was tough to find them in my googling. Here are some links on this topic:

Boaz.

Leave a Comment

Fields with * are required.