Why does valid SQL code not run in Drupal??

Hi all,

I am trying to mimick some calendar-like behaviour. I know there is a Calendar module, but it has some flaws, so I'm baking my own, in Drupal 5.1.

I have my node table, and three tables with three publications. Each publication has a “From date” and a “To date”. I would like to show only the nodes for which today’s date is in between these From and To dates.

This code works in PHPMyAdmin:

SELECT n.nid AS thenid, 
n.title AS thetitle, 
e.field_begindate_value AS begindate, 
e.field_begindate_value2 AS enddate, 
c.field_nbc_date_value AS nbcbegindate, 
c.field_nbc_date_value2 AS nbcenddate, 
k.field_cktoon_value AS ckbegindate, 
k.field_cktoon_value2 AS ckenddate
FROM dru_node n
LEFT JOIN dru_content_type_e_newsletter e ON n.nid = e.nid
LEFT JOIN dru_content_type_newsletter_cultuur c ON n.nid = c.nid
LEFT JOIN dru_content_type_article_ck k ON n.nid = k.nid
WHERE (
LEFT( e.field_begindate_value, 10 ) <= '2007-05-26'
AND LEFT( e.field_begindate_value2, 10 ) >= '2007-05-26'
)
OR (
LEFT( c.field_nbc_date_value, 10 ) <= '2007-05-26'
AND LEFT( c.field_nbc_date_value2, 10 ) >= '2007-05-26'
)
OR (
LEFT( k.field_cktoon_value, 10 ) <= '2007-05-26'
AND LEFT( k.field_cktoon_value2, 10 ) >= '2007-05-26'
)

When I run the above code in PHPMyAdmin, I get the records I am looking for. But…

When I “drupalize” this code (i.e. put curly brackets for the table prefix and take care of the PHP quotes and stuff), I get an error. This is the “Drupal” code, which is exactly the same as the PHPMyAdmin code (except for the “drupalizing”, of course):

$today = date("Y-m-d");
$sql = "SELECT 
n.nid as thenid, 
n.title as thetitle, 
e.field_begindate_value as begindate, 
e.field_begindate_value2 as enddate, 
c.field_nbc_date_value as nbcbegindate, 
c.field_nbc_date_value2 as nbcenddate, 
k.field_cktoon_value as ckbegindate, 
k.field_cktoon_value2 as ckenddate 
FROM {node} n 
LEFT JOIN {content_type_e_newsletter} e on n.nid = e.nid 
LEFT JOIN {content_type_newsletter_cultuur} c on n.nid = c.nid 
LEFT JOIN (content_type_article_ck) k on n.nid = k.nid 
WHERE 
(LEFT(e.field_begindate_value, 10) <= '" . $today . "' AND LEFT(e.field_begindate_value2, 10) >= '" . $today ."') 
OR 
(LEFT(c.field_nbc_date_value, 10) <= '" . $today . "' AND LEFT(c.field_nbc_date_value2, 10) >= '" . $today ."') 
OR 
(LEFT(k.field_cktoon_value, 10) <= '" . $today . "' AND LEFT(k.field_cktoon_value2, 10) >= '" . $today ."')";

$result = db_query(db_rewrite_sql($sql));

while ($data = db_fetch_object($result)) {
  $output .= node_view(node_load($data->thenid), TRUE, FALSE, TRUE);
}
echo $output;

When I run this code in a PHP enabled node, I get this error:

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 'k on n.nid = k.nid WHERE (LEFT(e.field_begindate_value, 10) query: eval SELECT DISTINCT(n.nid) as thenid, n.title as thetitle, e.field_begindate_value as begindate, e.field_begindate_value2 as enddate, c.field_nbc_date_value as nbcbegindate, c.field_nbc_date_value2 as nbcenddate, k.field_cktoon_value as ckbegindate, k.field_cktoon_value2 as ckenddate FROM dru_node n LEFT JOIN dru_content_type_e_newsletter e on n.nid = e.nid LEFT JOIN dru_content_type_newsletter_cultuur c on n.nid = c.nid LEFT JOIN (content_type_article_ck) k on n.nid = k.nid WHERE (LEFT(e.field_begindate_value, 10) <= '2007-05-26' AND LEFT(e.field_begindate_value2, 10) >= '2007-05-26') OR (LEFT(c.field_nbc_date_value, 10) <= '2007-05-26' AND LEFT(c.field_nbc_date_value2, 10) >= '2007-05-26') OR (LEFT(k.field_ckto in /www/ck/dru/includes/database.mysql.inc on line 172.

I am at a loss here. What did I do wrong? Why is drupalized code causing this MySQL error???? Any clues would be really appreciated!!

Ludo