07-15-2009, 09:30 AM
|
#2 (permalink)
|
|
The Contributor
Join Date: Nov 2007
Location: California
Posts: 82
Thanks: 0
|
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 |
|
|
|