Problems with drupal Database Query

Question

Hello,

i try to fetch a result with this request, which works in phpayadmin:

 $result_med = db_query("SELECT node.nid AS nid,
   node.created AS node_created
 FROM dr_wiwe_node node 
 LEFT JOIN dr_wiwe_content_type_classified node_data_field_classified_valid_till ON node.vid = node_data_field_classified_valid_till.vid
 WHERE ((node.type in ('classified')) AND (node.status <> 0))
    AND (DATE_FORMAT(STR_TO_DATE(node_data_field_classified_valid_till.field_classified_valid_till_value, '%Y-%m-%dT%T'), '%Y-%m-%d\T%H:%i:%s') >= '2010-09-16T22:34:05')
   ORDER BY node_created DESC LIMIT 1");
    var_dump($result_med);
    while ($node = db_fetch_object($result_med)) {
    //var_dump ($node);}

In the hardcoded php Version it returns nothing. If I var_dump $result_med, I am getting: resource(552) of type (mysql result)

Where is my error?

Answer 1

The problem is probably caused by db_query() treating parts of your datetime formatting strings as query parameters, which it tries to replace.

So you'll need to add additional '%' characters to your existing ones to escape them, thus preventing the parameter substitution process from trying to replace them.

See the "If a query that has % in them" comment from the db_query api documentation for an example.

A cleaner/more readable solution might be to just use '%s' placeholders for the formatting strings in the query and then add the actual formatting strings as arguments to the db_query call, as suggested by Eli.