I have a SQL Database and I wish to obtain records until the customer's quantity requested has been met. Here is what I mean.
Request Table
Expand|Select|Wrap|Line Numbers
- Product Quantity Price
- -----------------------
- Apple 10.0 5.00
Expand|Select|Wrap|Line Numbers
- select c.FName,
- p.ProductName,
- s.Description,
- s.Quantity,
- s.Price
- FROM requests r
- inner join sellers s on r.ProductID = s.ProductID
- inner join products p on p.ProductID=s.ProductID
- inner join customers c on c.ID=s.C_ID
- where r.C_ID = 3 AND r.matchType='Price'
- ORDER BY s.Price ASC
Expand|Select|Wrap|Line Numbers
- FName | ProductName | Description | Quantity | Price
- --------------------------------------------------------------------------=
- compny1 Apple royal apples fm appleco. 5.0 5.00
- daz Apple sweet apples 6.0 5.50
- company2 Apple Apples yum 8.0 9.00
apples @5kg from compny1 = 5.00
apples @5kg from daz = 5.50
total = 10.50
the Database should then show 'Daz' quantity being 1.0 KG left. However, I keep getting suck in doing this. I have tried to do the following:
Expand|Select|Wrap|Line Numbers
- while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
- if($rows1['Quantity']==$quantityRequested){ //If the first row = 10KG output only this row.
- echo $rows1['FName'];
- echo $rows1['NameProduct'];
- echo $rows1['Quantity'];
- echo $rows1['Price'];
- }else{
- //stuck here check the next rows and see there is 6KG's .. We need 10KG Requested - 5KG from daz(row1)
- // -Remaining amount left(i.e.5KG)
- //hence, print the output specified above and UPDATE Database where quantity has been reduced by X amount.
- }
Now the 'Cheapest' way to do this is by sorting the available matches by Asc Price which I have.
Now we can see the First row shows 'compny1' selling 5KG for £5.00... So, we need 10KG hence we display row 1..
Next, as we have not met the 10KG demand yet (still need another 5KG) we look at the second row. We see 'daz' is selling 6KG. but we only need 5more KG's to make the 10KG demand.
Hence, what I wish to do is... UPDATE the table so that the first record is copied to another table and the second row is updated to 1.0kg remaining(6KG-5KG). And hence, display to the customer:
Your 10KG can be purchased by
compny1 5KG@5.00
daz 5KG@5.50
------
Total £10.50
So, that is my problem. I dont know how to keep checking the rows until the quantity requested is met. I guess I need some kind of 'counter' that keeps track of how much quantity has been added added 'So far' and then check how much more is needed from the next row.