Drupal 7 - query using BETWEEN not working

Question

I have a query in Drupal 7 that is searching a custom table:

$query5 = "SELECT COUNT(reservation_id) as rcount5, reservation_id FROM {reservations} WHERE resource_id = :resource_id AND reservation_date = :reservation_date AND start_time BETWEEN :start_time AND :end_time";
$result5 = db_query($query5, array(':resource_id' => $resource_id, ':reservation_date' => $reservation_date, ':start_time' => $start_time, ':end_time' => $end_time));

The query is not working because I believe that it is not recognizing the BETWEEN function properly and returning the end_time because it exists. Is there a way to show drupal or the db api that this is a BETWEEN statement? Thanks.

Answer 1

You can use the Drupal 7 database API and add a BETWEEN argument like this:

$query5 = db_select('reservations', 'r')
  ->fields('r', array('reservation_id'))
  ->condition('resource_id', $resource_id)
  ->condition('reservation_date', array($start_time, $end_time), 'BETWEEN');

$query5->addExpression('COUNT(reservation_id)', 'rcount5');
$result5 = $query->execute();

It's far easier to read in my opinion :)

Take a look at Dynamic queries for more info and examples.