User login

Looking at SQL to fix broken queries in a Drupal environment

Alternate title: Apparently I don't understand SQL (and so need all the help I can get)

The easiest way to get a better look at your SQL in Drupal is to install the devel module. (Hint: It's for use on development sites, not production sites!)

With devel enabled you can see your query (and many, many of its Drupal-created friends) on every page that triggers it, printed below the web page. The query is printed exactly as it is actually generated once the table names have been expanded from the curly braces syntax to include any table prefixing (for instance, {agaric_table} to {drupal_agaric_table}) and all variables have been inserted in place of placeholders. (This will all get a lot more exciting and easier and complicated in Drupal 7, with the PDO-style database abstraction layer...)

But for now, you can see your query running as:

SELECT * FROM drupal_changent_activity_ticker cat INNER JOIN drupal_og_uid ou USING(uid) WHERE nid = 11054 ORDER BY cat.created DESC LIMIT 5

And entirely failing to produce any result (or error, for that matter).

Pasting it into phpMyAdmin's query window for your database you can play with it rapidly and massage it into something that works, such as:

FROM drupal_changent_activity_ticker cat
LEFT JOIN drupal_og_uid ou ON cat.uid = ou.uid
WHERE ou.nid =11054
ORDER BY cat.created DESC

The next, recommended step is understanding why INNER and USING did not work (yet did not through an ambiguous column error, so it was just never actually matching our two tables up?) but as you can see I can't help you there.

I can help you with translating it back to Drupal:

  $result = db_query("SELECT * FROM {changent_activity_ticker} cat
  LEFT JOIN {og_uid} ou ON cat.uid = ou.uid
  WHERE ou.nid = %d
  ORDER BY cat.created DESC LIMIT 5", $gid);
  while ($row = db_fetch_object($result)) {
    // ...


Searched words: 
drupal mysql explain mysql print query Drupal see SQL statements debug MySQL query not working as expected


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>
  • Lines and paragraphs break automatically.

More information about formatting options

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