Page 1 of 1

how to search for checked out on a day

Posted: Sat Mar 29, 2003 12:20 am
by ee99ee
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:

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)) &#123;
$id&#1111;] = $row&#1111;0];&#125;
$var = 0;
$subl = "yes";
$idnum = count($id);

while($var < $idnum) &#123;
$query2 = mysql_query("SELECT productid FROM orders WHERE '$id&#1111;$var]'=productid AND '$pdate'<=pickupdate AND '$rdate'>=restockdate");
$aff = mysql_num_rows($query2);
	if($aff == 0) &#123;
	unset ($subl);
	print "<p>";
	print "Product avaiable for rental, processing order...";
	break;&#125;
$var++;&#125;
	if (isset($subl)) &#123;
	print "<p>";
	print "Subleaseing requried.";&#125;
?>
Anybody got any ideas? Thanks.

-ee99ee

Posted: Sat Mar 29, 2003 4:10 am
by DocSeuss
Ok I can see that product A and product B are basically clones of each other. If the product is something like a book or video tape then I wouldn't use the overhead of giving them each a unique productid, I would just use a total quantity field and a quantity_in_stock. On the other hand if it something more like heavy equpment where each would itself be serialized and might have a different maint. schedule then the difference may be more concrete.

Now based on your query I have to assume that $pdate and $rdate are the pickup and restock of customer C who is looking to rent, though that isn't included in your code sample.
Your statement says find the product where C.pdate <= the same pickup date of either customer A or B AND C.rdate is >= the same restock date of customer A or B, this is the flaw.
All you really need to find is a product where C.pdate >= A.rdate or b.rdate.

Obviously the => indicates that a product can be rented on the same day as it is being restocked but in the description you said other wise so you would just use the > if that were not possible.

A future problem with your model here is what happens when customer A is late on returning the rented product, or customer B decides he only needs it for 2 days? The program assumes that the restock date is set in stone. I would just add a couple of fields one indicating where a certain product is avail. or not just a simple 1 or 0, and if it isn't the "expected" date it should be available(the restock date).

Hope I didn't confuse the issue for ya just my 2 cents.

Posted: Fri Apr 04, 2003 2:30 am
by ee99ee
Okay I am still not 100% sure what you mean, and how it should be done. Let me reword, maybe this will be more exact as to what I need:

Basicly, the inventory system tracks rentals. You've got an order form, that looks at everything in inventory (rented or not rented). You choose what you want, choose the pickup date, and how many days before the product is back in stock ready to go out again (usually 5).

Now, let's say I've got 5 products that are the same. They are going to be entered in the products table of my MySQL database 5 times, so each one will have an different productid.

Let's say I want to rent this product, for a given date. What should happen is my PHP script pull all the productid's that match the product being rented (5 of them in this case), and put that in an array. Next, it should go through each productid one at a time and see if it's rented out anytime between the pickup date the customer has specified, and the date at which the product will be back (5 days after pickup). This is where I am stuck.

How do I write a SQL query to search through my orders table and see if a given productid is rented out? It will have to calculate if it's rented out by the pickupdate and restockdate listed in the order table for each order that matches the productid it's searching for at the time. Oh, and remember, orders aren't always sequential: meaning, you may have one customer who comes in to plan to rent something 2 weeks from now; then tomorrow, another customer comes in who wants to rent 2 days from now.

If I've got 5 productid's that match the product being rented, I'll loop through the query a max of 5 times (each time with a diff productid). If the last time no product is avaible for the given date range, all I'll need to do is print something like "Sub-Renting Required". Once a productid is found that IS avaible for the given date range, it should then break the loop, and keep going on in the script (which will cause it to place an order and some other minor stuff).