I'm confused about searching between dates in mysql

Question

Here, I changed one of my table's column type to standard mysql date format.

     <form action="'.$self.'?action=search" method="post">
        <ul>
            <li>Oda No:</li>
            <li><input type="text" name="ro"/></li>
            <li>Check in:</li>
            <li><input type="text" name="cn"/></li>
            <li>Check out:</li>
            <li><input type="text" name="ct"/></li>
            <li><input type="submit" value="Search" name="rsubmit" class="int"/></li>
        </ul>
      </form>

I changed my query to this:

$sql = "SELECT * FROM reservation WHERE  room = $roomno 
 AND dateStarted > '$checkin'  AND dateEnded < '$checkout'";
mysql_num_rows($query) == 0

However, let's say there is a row in the table like this:

dateStarted =2010-08-10 and dateEnded = 2010-08-18

And I run this query:

SELECT * FROM reservation WHERE room = 100
AND dateStarted > '2010-08-12' AND dateEnded < '2010-08-14'

Then I get:

mysql_num_rows($query) == 0 !!

Is this query wrong?

Answer 1

Query looks correct. Manually breakdown and see how many rows are preset for

SELECT * FROM reservation WHERE room = 100 AND dateStarted > '2010-08-12'

and

SELECT * FROM reservation WHERE room = 100 AND dateEnded < '2010-08-14'

Most probably there is no record satisfying your query.

And assuming that there are more records in your database other then one mentioned by you.