check rental item availability PHP MySQL

drolex · 07-13-2009, 03:09 AM

I am making a scheduling application for a rental company. They make reservations and rent out items. When they try to make a reservation, I need to check each item's availability. I've tried multiple ways. Let me show you my latest attempt.

An example row from the database looks like this:
id: "1"
customer: "Drolex"
dateList: "20090710 20090711 20090712 20090713"
toys: "1 2"
phone: "5555555"
message: "This is a message."

They enter the first and last date the item will be in use. I create a string with those dates and all dates between. The dates are separated by a space in the string and they are of the form year.month.day. I do a similar thing with the selected items (toys).

PHP check availability code:

PHP Code:


			
asort($toyList,SORT_NUMERIC);

$numToys = count($toyList);



for($i=0;$i<$numToys;$i++){

    $dateCount = $dateFirst;

    while($dateCount <= $dateLast){

        $sql_check = "SELECT id FROM scheduledRentals WHERE toys IN('$toyList[$i]') AND dateList IN('$dateCount')";

        $result = mysql_query($sql_check);

        if(mysql_num_rows($result) > 0){

            echo "Availability problem exists.";

            exit();

        }

        $dateCount++;

    }

}

It's not working because I never get the message about an availability problem and the reservation is submited.

I think the problem is within the MySQL search (WHERE) technique. It doesn't give an error or warning.

Do you see an easy fix? Do you have a better method for either storing or checking date overlaps based on first and last date?