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?