how to search for checked out on a day
Posted: Sat Mar 29, 2003 12:20 am
I'm writeing a rental management system. I have basicly an orders table that shows every order ever. This has the $custid, $orderid (auto incriment), $productid (being rented), $pdate (pickupdate), and $rdate (restock date -- date it'll be ready to be re-rented.
I am really stuck on this one though. Let's say customer A comes in and rents product A for 5 days starting on march 01/01/2000. Customer B comes in and rents product B (same TYPE as A, but differnet physical product, so it has a seperate product entry in products table) for 4 days starting on 01/03/2000.
Now, customer C comes in and trys to rent the product that both product A and B are on 01/05/2000. Obviously, product A will still be out on this day, since it's the last day. And product B is also rented out on this date. So I only need to print "Sublease" in this case. If product A or B were avaible on this date, place the order.
Where I'm stuck is in my SQL statement. Here's what I have currently:
Anybody got any ideas? Thanks.
-ee99ee
I am really stuck on this one though. Let's say customer A comes in and rents product A for 5 days starting on march 01/01/2000. Customer B comes in and rents product B (same TYPE as A, but differnet physical product, so it has a seperate product entry in products table) for 4 days starting on 01/03/2000.
Now, customer C comes in and trys to rent the product that both product A and B are on 01/05/2000. Obviously, product A will still be out on this day, since it's the last day. And product B is also rented out on this date. So I only need to print "Sublease" in this case. If product A or B were avaible on this date, place the order.
Where I'm stuck is in my SQL statement. Here's what I have currently:
Code: Select all
<?php
include "dbconnect.php";
$query = mysql_query("SELECT productid FROM products WHERE style='$style' AND size='$size'");
$id = array();
while($row = mysql_fetch_array($query)) {
$idї] = $rowї0];}
$var = 0;
$subl = "yes";
$idnum = count($id);
while($var < $idnum) {
$query2 = mysql_query("SELECT productid FROM orders WHERE '$idї$var]'=productid AND '$pdate'<=pickupdate AND '$rdate'>=restockdate");
$aff = mysql_num_rows($query2);
if($aff == 0) {
unset ($subl);
print "<p>";
print "Product avaiable for rental, processing order...";
break;}
$var++;}
if (isset($subl)) {
print "<p>";
print "Subleaseing requried.";}
?>-ee99ee