Getting results between checkin and checkout (DATETIME) error

Question

I am trying to fetch results between specific dates checkin and checkout. I am gettting the following error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '14:00:00 AND b.end <= 2016-03-31 11:00:00' at line 4

SELECT * FROM properties as a LEFT JOIN reservations as b ON b.room_id = a.room_id WHERE a.client = 7 AND b.start >= 2016-03-01 14:00:00 AND b.end <= 2016-03-31 11:00:00

Filename: controllers/Transactions.php

Line Number: 741

SQL:

$sql = "SELECT *
        FROM properties as a
        LEFT JOIN reservations as b ON b.room_id = a.room_id
        WHERE a.client = $client AND b.start >= $start AND b.end <= $end";
        $query = $this->db->query($sql);

The datetime is $start and $end.. what am i doing wrong? Thanks

I guess you are using CodeIgniter. Look to Active Record for more simplicity for your queries. — Vincent Decaux, Mar 15 '16 at 11:50
@VincentDecaux: agreed, if its CI framework use active record — devpro, Mar 15 '16 at 11:52

score 3 · Answer 1 · answered Mar 15 '16 at 11:48

You need to add quotes for start and end date columns you can not use date columns without quotes as:

b.start >= 2016-03-01 14:00:00 AND b.end <= 2016-03-31 11:00:00

This should be:

b.start >= '2016-03-01 14:00:00' AND b.end <= '2016-03-31 11:00:00'

Modified Query:

SELECT *
FROM properties AS a
LEFT JOIN reservations AS b ON b.room_id = a.room_id
WHERE a.client = '$CLIENT' AND b.start >= '$START' AND b.end <= '$END'

Getting results between checkin and checkout (DATETIME) error

1 Answers1