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?

dschreck · 07-15-2009, 09:30 AM

ever so quickly i came up with this example:

SQL:

Code:

CREATE TABLE rentals (
 rental_id INT unsigned NOT NULL AUTO_INCREMENT,
 customer_name VARCHAR(120) NOT NULL,
 phone_number VARCHAR(20),
 message TEXT,
 PRIMARY KEY(rental_id)
);

CREATE TABLE rental_dates (
	rental_id INT UNSIGNED NOT NULL,
	toy_id INT UNSIGNED NOT NULL,
	rental_out DATETIME,
	INDEX `rental_toy` ( `rental_id` , `toy_id` )
);

CREATE TABLE toys (
	toy_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	toy_desc TEXT,
	PRIMARY KEY(toy_id)
);


INSERT INTO rentals values (1, 'Drolex', '(555) 555 5555', 'This is a message');
INSERT INTO toys values (1, 'test toy 1');
INSERT INTO rental_dates values (1, 1, '2009-07-15 00:00:00');
INSERT INTO rental_dates values (1, 1, '2009-07-16 00:00:00');
INSERT INTO rental_dates values (1, 1, '2009-07-22 00:00:00');
INSERT INTO rental_dates values (1, 1, '2009-07-21 00:00:00');
INSERT INTO rental_dates values (1, 1, '2009-07-30 00:00:00');

PHP:

PHP Code:


			
<?php



mysql_connect();

mysql_select_db();



$toys = array(1);



$datesOfRental = array('2009-07-15 00:00:00','2009-07-25 00:00:00');



$data = array();



foreach($toys as $toyId)

{

    $sql = "SELECT 

                r.rental_id as rental_id, r.customer_name, r.phone_number, rd.rental_out, t.toy_desc, t.toy_id 

            FROM 

                rental_dates AS rd

                    JOIN rentals AS r

                         USING(rental_id)

                    JOIN toys AS t

                        USING(toy_id)

            WHERE

                rd.toy_id = {$toyId}

            ";

    

    $get = mysql_query($sql) or die(mysql_error());

    while($row = mysql_fetch_assoc($get))

    {

        $data[$row['rental_out']] = $row;

    }

    foreach($datesOfRental as $day)

    {

        

        if(isset($data[$day]) && array_key_exists($day,$data))

        {

            echo "Rental of {$data[$day]['toy_id']} ({$data[$day]['toy_desc']}) is out to {$data[$day]['customer_name']}, {$data[$day]['phone_number']} on ".date("F j, Y", strtotime($data[$day]['rental_out']))."<br />";

        }

        else 

        {

            echo "Rental of {$toyId} is available on ".date("F j, Y", strtotime($day))."<br />";

        }

    }

}

wouldn't scale well though - but at least you get the idea :o