Fetch rows until quantity has been met

[dazase1]

Hi,
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

Product     Quantity   Price
-----------------------
Apple          10.0      5.00

here is the SQL query to obtain matches:

    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

And here is the result:

         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

I want to display the full requested 10KG quantity by selecting the rows and updating the database i.e. the output should be:

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:

  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.
}

Ok, So what the problem is. I need to 'Make up' the 10KG requested demand.

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.

[Rabbit]

Use a variable to keep a running tally of how much of the demand has been met. Loop, output, and update the rows until the demand has been met.

[dazase1]

hi Rabbit,
How would I do this? As this is what I keep getting stuck on. Like what I need is...
while there are 'potential matches' keep looping... I do this using

while ($rows1 = mysql_fetch_assoc($queryQuantity2)){

which would list those matching sellers.

Then check.. If the first row == $requestQuantit y then output only the first matching row .. i.e. quantity has been met..

Else check the second row and see how much quantity has been fullfilled (i.e. the first row quantity + how much more is needed) If the second row + first row = quantity requested then done. the demand has been met and hence the appropriate row is modified to reflect the reduced seller quantity else check the next row and repeat the process..

Hence, need to keep track of how much quantity has been ADDED so far and MINUS from the next row so meet the demand.

Does this make sense?

Thanks so much:)

[Rabbit]

You should not check if the first row matches quantity. The first check is whether the running sum variable has exceeded the quantity requested, if not, then you add and update the row. In pseudo code, it would be something like this

running tally = 0
loop through recordset
   if running tally > quantity requested
      exit loop
   else
      running tally = running tally + row amount
      output row
      update row
   end if
end loop

[dazase1]

Rabbit thank you for your reply. I will try this method out also along with other ways that have been suggested. Thanks