User login

Stumbling through DBTNG, Drupal 7's new database layer

Order matters. condition() before countQuery()

This fails (note it trying to do a subquery):

<?php
return db_select('block')->countQuery()->condition('theme', $theme)->execute()->fetchField();
?>

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'theme' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {block} block) subquery WHERE (theme = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => bartik ) in xray_block_statistics() (line 93 of /home/ben/workspace/dgd7all/sites/default/modules/xray/xray.module).

This works:

<?php
return db_select('block')->condition('theme', $theme)->countQuery()->execute()->fetchField();
?>

Status message

Debug: '16' in _xray_help_admin_structure() (line 40 of /home/ben/workspace/dgd7all/sites/default/modules/xray/xray.module).

Running methods on the same object is not the same as chaining, and will break things.

This incorrect code

<?php
  $select = db_select('block');
  $select->condition('theme', $theme);
  $select->countQuery();
  $result = $select->execute();
  $value = $result->fetchField();
  // The above is wrong, wrong, wrong.
?>

causes this error message:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM block block WHERE (theme = 'bartik')' at line 1: SELECT FROM {block} block WHERE (theme = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => bartik ) in xray_block_statistics() (line 84 of /home/ben/workspace/dgd7all/sites/default/modules/xray/xray.module).

Why? This is incorrect:

<?php
$select->method();
$select->anotherMethod();
$select->stillAnotherMethod();
// The above will fail in strange ways!
?>

It is not the same as this correct syntax:

<?php
$select->method()
       ->anotherMethod()
       ->stillAnotherMethod();
?>

Rather, the absurdly verbose but also correct terminology would be:

<?php
$select = $select->method();
$select = $select->anotherMethod();
$select = $select->stillAnotherMethod();
?>

For the verbose method in action:

<?php
  $select = db_select('block');
  $select = $select->condition('theme', $theme);
  $select = $select->countQuery();
  $result = $select->execute();
  $value = $result->fetchField();
?>

Status message
Debug: '16' in _xray_help_admin_structure() (line 40 of /home/ben/workspace/dgd7all/sites/default/modules/xray/xray.module).

Errors like this are most likely to be noticed on the parts we more commonly put in new statements. We cannot run ->fetchAll() on the $select. We can chain it onto the ->execute(), or we can assign the result of the $select->execute() to another variable – $result = $select->execute; $result->fetchAll;
This applies to all methods.

See also

No fields (* not assumed) error

Searched words: 
chaining

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You can use Markdown syntax to format and style the text. Also see Markdown Extra for tables, footnotes, and more.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <img> <blockquote> <small> <h2> <h3> <h4> <h5> <h6> <sub> <sup> <p> <br> <strike> <table> <tr> <td> <thead> <th> <tbody> <tt> <output>
  • Syntax highlight code surrounded by the {syntaxhighlighter SPEC}...{/syntaxhighlighter} tags, where SPEC is a Syntaxhighlighter options string or "class="OPTIONS" title="the title".
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.