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

3
  • Try ->where('str_to_date(s.start, \'%%H:%%i\') >= \'15:24\' ');, with the double percent signs
    – Clive
    Aug 22, 2016 at 14:51
  • Thanks for the response @Clive, that doesn't look to have changed anything. :|
    – Kiee
    Aug 22, 2016 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. Aug 22, 2016 at 18:05

1 Answer 1

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.

1
  • 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, 2016 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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