CODEWITHSUNDEEP
phpmysqlwhere-clause
jaypabs
jaypabs

Reputation: 1567

Where Clause in PHP/MySQL

I have the following record in my table called "rooms_prices"

id  room_id     date_from   date_to price
55  11          02/01/2014  12/31/2014  2250

Whenever I tried to execute an SQL statement in my PHP code, I always get a result when the date I queried is not within the date range.

Here's my SQL statement:

SELECT price FROM rooms_prices WHERE room_id = 11 AND DATE_FORMAT( date_from, '%m/%d/%Y' ) <= '12/01/2013' AND DATE_FORMAT( date_to, '%m/%d/%Y' ) >= '12/01/2013'

This SQL statement always output 2250. It supposed to return only record if the date is between 02/01/2014 and 12/31/2014.

Anyone know how to fix this?

Upvotes: 2

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use the date format in Y-M-D order:

SELECT price
FROM rooms_prices
WHERE room_id = 11 AND
      DATE_FORMAT( date_from, '%Y-%m-%d' ) <= '2013-12-01' AND
      DATE_FORMAT( date_to, '%Y-%m-%d' ) >= '2013-12-01';

You are doing the comparisons as strings, which is why your version is failing.

It would actually be better to do the comparisons as dates:

SELECT price
FROM rooms_prices
WHERE room_id = 11 AND
      date_from <= date('2013-12-01') AND
      date_to >= date('2013-12-01');

Or even just using between:

WHERE room_id = 11 AND
      date('2013-12-01') between date_from and date_to

Upvotes: 2

Related Questions