Three Quirks of Drupal Database Syntax

Database query syntax in Drupal can be finicky, but doing it right - following the coding standards as a matter of habit - is very important. Here are three "gotchas" I've run into or avoided recently:

1. Curly braces around tables: Unit testing with SimpleTest absolutely requires that table names in all your queries be wrapped in {curly braces}. SimpleTest runs in a sandbox with its own, clean database tables, so you can create nodes and users without messing up actual content. It does this by using the existing table prefix concept. If you write a query in a module like this,$result = db_query("SELECT nid from node");
when that runs in test, it will load from the regular node table, not the sandboxed one (assuming you have no prefix on your regular database). Having tests write to actual database tables can make your tests break, or real content get lost. Instead, all queries (not just in tests) should be written like:

$result = db_query("SELECT nid from {node} node");
(The 2nd node being an optional alias to use later in the query, for example as node.nid JOINed to another table with a nid column.) When Drupal runs the query, it will prefix {node} by context as site_node, or simpletestXXnode, to keep the sandboxes separate. Make sure to always curly-brace your table names!

2. New string token syntax: Quotation marks around string tokens are different in Drupal 6 and 7. D7 uses the new "DBTNG" abstraction layer (backported to D6 as the DBTNG module). In Drupal 6, you'd write a query with a string token like this:$result = db_query("SELECT nid from {node} where title='%s'", 'My Favorite Node');
Note the single quotation marks around the placeholder %s.

With D7 or DBTNG, however, the same static query would be written:$result = db_query("SELECT nid from {node} WHERE title = :title", array(':title' => 'My Favorite Node'));
No more quotes around the :title token - DBTNG puts it in for you when it replaces the placeholder with the string value.

3. Uppercase SQL commands: Make sure to use UPPERCASE SQL commands (SELECT, FROM, ORDER BY, etc) in queries. Not doing so is valid syntax 99% of the time, but will occasionally trip you up. For example: the db_query_range function (in D6) does not like lowercase from. I was using it recently to paginate the results of a big query, like select * from {table}. The pagination was all messed up, and I didn't know why. Then I changed it to SELECT * FROM {table} and it worked. Using uppercase like that is a good habit, and in the few cases where it matters, I'll be glad I'm doing it from now on.

Original Article:

Three Quirks of Drupal Database Syntax