Drupal 7 - a request using BETWEEN does not work

Question

I have a query in Drupal 7 that is looking for a user 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 request does not work because I believe that it does not recognize the BETWEEN function correctly and returns end_time because it exists. Is there a way to show drupal or db api that this is a BETWEEN clause? Thanks.

Answer 1

You can use the Drupal 7 database API and add the BETWEEN argument as follows:

 $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(); 

Much easier to read in my opinion :)

See Dynamic Queries for more information and examples.

Answer 2

Use >= and <= instead:

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

Answer 3

Well, I came up with an answer. First off, my original code works fine. I had to change the data type to int in the database, and after that it worked fine. Thanks.

The good news is that all the answers have worked. Thanks.

Answer 4

I am sure this is the default or error in db_query / db_select.

Suppose you saved your timestamp in a database with the following format 2014-12-15 09:38:04 or just 2014-12-15

Now suppose you want to search for all rows related to one day. If you selected a raw MySQL query using BETWEEN, it will work just fine. Example:

 select * from table where timestamp between "2014-12-15" and "214-12-15"; 

However, if you create a query using db_query or db_select, the expression below will not work: Example:

 $query->condition('timestamp', array("2014-12-15", "2014-12-15"), 'BETWEEN'); 

For some reason, Drupal seems to be comparing from 2014-12-15 0: 0: 0 to 2014-12-15 0: 0: 0.

If you create a condition in the following format, it will work

 $query->condition( 'timestamp', array("2014-12-15", "2014-12-15" . ' 23:59:59'), 'BETWEEN' ); 

Using real filters, the operator will look like this:

 $query->condition( 'timestamp', array($stardate, $endate . ' 23:59:59'), 'BETWEEN' ); 

I hope that I am wrong about this Drupal problem. Because it is really lame.

Regards, Sharif.

Answer 5

 $query->condition('timestamp', array("2014-12-15", "2014-12-15"), 'BETWEEN') 

does not work on drupal because it does not have a real filter. the solution given by @Sharif El Shobkshy is a working solution.

 $query->condition( 'timestamp', array($stardate, $endate . ' 23:59:59'), 'BETWEEN' ); 

Note that $ stardate and endate must be in php date(Yml) format. But if the date is stored as an integer, change it to date(Yml,$_Post['date']) and change the real filter too.

Answer 6

If you use COUNT() and another field selection in your query, you should also use the GROUP BY