TalkPHP - check rental item availability PHP MySQL

TalkPHP (http://www.talkphp.com/forums.php)

- Advanced PHP Programming (http://www.talkphp.com/advanced-php-programming/)

- - check rental item availability PHP MySQL (http://www.talkphp.com/advanced-php-programming/4726-check-rental-item-availability-php-mysql.html)

check rental item availability PHP MySQL

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?

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