$query = "SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, " . "(SELECT COUNT(*) FROM {comments} c WHERE c.timestamp >= %d AND c.nid=nc.nid) AS comments_today, " . "cs.comment_count " . "FROM {node_counter} nc " . "INNER JOIN {node} n ON n.nid=nc.nid " . "LEFT JOIN {node_comment_statistics} cs ON cs.nid=nc.nid " . "WHERE nc.daycount > 0 " . "ORDER BY nc.daycount DESC"; $result = pager_query($query, $page_limit, 0, NULL, $today); This comes out looking like: SELECT nc.nid, n.title, nc.daycount AS views, nc.totalcount, (SELECT COUNT(*) FROM {comments} c WHERE c.timestamp >= %d AND c.nid=nc.nid) AS comments_today, cs.comment_count FROM {node_counter} nc INNER JOIN {node} n ON n.nid=nc.nid LEFT JOIN {node_comment_statistics} cs ON cs.nid=nc.nid WHERE nc.daycount > 0 ORDER BY nc.daycount DESC user warning: 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 ') AS comments_today, cs.comment_count FROM node_counter nc INNER JOIN node n ON ' at line 1 query: SELECT COUNT(*) FROM comments c WHERE c.timestamp >= 1270699200 AND c.nid=nc.nid) AS comments_today, cs.comment_count FROM node_counter nc INNER JOIN node n ON n.nid=nc.nid LEFT JOIN node_comment_statistics cs ON cs.nid=nc.nid WHERE nc.daycount > 0 in C:\www\webapps\drupal6\sites\all\modules\mymodule\mymodule.pages.inc on line 672. Nancy E. Wichmann, PMP ________________________________ From: Aaron Winborn <winborn@advomatic.com> To: development@drupal.org Sent: Thu, April 8, 2010 1:20:38 PM Subject: Re: [development] Strange query problem Can you post your Drupal query? Hard to troubleshoot w/o the original code. nan wich wrote: I have the following query, which works fine in PhpMyAdmin, but Drupal upchucks on it. Why?
SELECT nc.nid, n.title, n.created, nc.daycount AS views_today, nc.totalcount AS total_views, (SELECT COUNT(*) FROM comments c WHERE c.timestamp >= 1270699200 AND c.nid=nc.nid) AS comments_today, cs.comment_count FROM node_counter nc INNER JOIN node n ON n.nid=nc.nid LEFT JOIN node_comment_statistics cs ON cs.nid=nc.nid WHERE nc.daycount > 0 ORDER BY nc.daycount DESC
Yes, in Drupal, I do put the brackets around the table names.
The error message says there's a problem at " AS comments_today, cs.comment_count FROM...". Nancy E. Wichmann, PMP Sometimes the questions are complicated and the answers are simple. - Dr. Suess
-- Aaron Winborn Advomatic, LLC http://advomatic.com/ Drupal Multimedia available in September! http://www.packtpub.com/create-multimedia-website-with-drupal/book My blog: http://aaronwinborn.com/