zen of coding

BETWEEN AND SQL syntax in CakePHP

This is how you’d write a BETWEEN… AND… SQL query in a cake-like way.

The example should be pretty much self explanatory:

$this->Post->find(‘all’, array(‘conditions’=>array(‘Post.id BETWEEN ? AND ?’ => array(1, 10))));

Note, that CakePHP will quote the numeric values depending on the field type in your DB.

  • Great information, I can’t find this even in CakePHP Cookbook..

    Thanks..

  • @Nazieb

    Well, actually it has been added to the book a little while ago, but hey… as long as you’ve found the solution, it’s all good ;)

  • standstill

    any conversion for this for cake 1.1?

  • @standstill

    Don’t remember, sorry. It’s been too long since I’ve “touched” 1.1 :)

  • DieHard

    And how can I compare a value between two values in the database ?

    Ex:
    In the database I have a table sequences with the fields ini_sequence and end_sequence

    I wanna know if the value 200 is in between the ini_sequence and the end_sequence

  • Andrés

    Helo,,, thanks for the info… works!!!

  • @Andrés

    No problem ;)

  • ata

    What about this?
    $date_to_check=’2010-03-22′;
    $this->Event->find(‘all’, array(‘conditions’=>array(‘? BETWEEN ? AND ?’ => array($date_to_check,’Event.date_from’,’Event.date_to’))));

    The above will generate this query:

    Select * from events where ‘2010-03-22’ BETWEEN ‘Event.date_from’ AND ‘Event.date_to’
    In this case ‘Event.date_from’,’Event.date_to’ are interpreted like values, not like fields. Consequently the above query return no error and no results.
    I’m out of ideas… :(

    • Vineet

      $this->Event->find(‘all’, array(‘conditions’=>array(‘Event.date_from $date_to_check, ‘Event.date_to >=’ => $date_to_check)));

  • @ata

    I see what you are trying to do… but I don’t see why you’d expect that work.
    the BETWEEN/AND is a very simple SQL syntax, and there is not cake magic behind it, other than described above.

  • neil

    example: 2008-08-08 to 2008-08-20

    how can i include 2008-08-20 in my result using between?

  • teknoid

    @neil

    Sub the values in the array (1, 10) with the dates that you need.

%d bloggers like this: