0

I've written a query to get records that start column is later than a given time. Take the following:

$q = db_select('timetable_slot', 's')
    ->fields('s')
    ->where('str_to_date(s.start, \'%H:%i\') >= \'15:24\' ');

    print_r($q->__toString());
    /* SELECT s.* FROM timetable_slot s WHERE (str_to_date(s.start, '%H:%i') >= '15:24' ) */


$result = $q->execute()
    ->fetchAll();

print_r($result); /* [] */

$result comes back as an empty array. If I run the generated SQL manually I get the results I expect, any reason why drupal is returning an empty set?

I've also tried:

db_query("SELECT s.* FROM {timetable_slot} s WHERE (str_to_date(s.start, '%H:%i') > '15:24')")->fetchAll(); 

This also returns empty, even though the query works in a db client.

Thanks

  • Try ->where('str_to_date(s.start, \'%%H:%%i\') >= \'15:24\' ');, with the double percent signs – Clive Aug 22 '16 at 14:51
  • Thanks for the response @Clive, that doesn't look to have changed anything. :| – Kiee Aug 22 '16 at 14:57
  • Do you really need all fields? Try naming them in your ->fields argument. I know it is supposed to default to * when only given a table alias, but it is worth a try. – DeveloperWeeks Aug 22 '16 at 18:05
1

It's impossible to troubleshoot such a query without access to your environment.

The Devel module has a query inspection tool (Display query log in the Devel settings). that can assist you in determining what query was sent to your database. Use that to determine the exact query sent to your DB and how it differs from the query you're sending to your SQL client.

  • Hi Shawn, thanks for the response. I've enabled Devel, and turned on display query log, and all seems above board. the same query is shown in the query log that is printed out by $q->__toString(). Is it possible to get the DB handle directly so drupal can't fudge my results? – Kiee Aug 23 '16 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.