Reducing Quantities Request and Seller table

[dazase1]

I have a small problem.

I have 4 tables. Customers (holding customer data e.g. id, names, address email, password etc), products table holding product info(product id, name), a requests table where the customer 'Requests' a product i.e. shows intrest (request id, customerID[forignkey], productid[FK] quantity and price they wish to purchase at ) and finally a sellers table where sellers can say (ID, customerid[fk], productid[FK], descrip, quantity, price )

The problem is say a customer (ID 3) has requested 10KG of apples which they want for £ 10.00

Now you have two sellers (customer ID's 1 and 2).. They are selling apples for the following:

  FName          |   ProductName    |     Description                 |  Quantity  |   Price
        --------------------------------------------------------------------------=
     compny1          Apple                    royal apples fm appleco.      5.0          5.00
        daz          Apple                     sweet apples                  6.0            5.50

I am running this query :

    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

The last two rows show the supplier compny1 and daz are both selling apples . Now to make up the 10kg demand what I need to do is select all the 5.0 quantity from compny1 and then select 5.0 quantity from daz.
Hence output

apples @5kg from compny1 = 5.00
apples @5kg from daz = 5.50
total = 10.50

and then delete the compny1 record from database as there stock is now sold and no longer advertised and then update the daz row so it shows the new quantity as 1.0kg remaining.

So, thats the main problem. Now, I think will need to do most of this in PHP but after the query I dont know where to do.

Does anyone have any idea of how I can do this?

I think what i need to do is

I run my query using mysql_query (..run stated query ubove)
check the mysql_num_rows( $query) >1 (i.e. records are found.)

and then check each row until the 10KG's is met ... i.e. keep fetching rows until Quantity is met??

Anyone know what to do?

Please help me. Thanks

[Luuk]

"and then check each row until the 10KG's is met ... i.e. keep fetching rows until Quantity is met??"
Check the while statement in PHP

[dazase1]

Hi, Luuk

Yes I believe this is what I need to do but that is precisly the problem I dont know how I should go abouts doing this.

Here is an example: Requests table

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

That table is outputted in PHP...

here is the potential matches

Name(seller) |  Product |           Descp                       |     Quantity |     Price    
--------------------------------------------------------------------------------   
compny1          Apple	      apples from apple Co.               	5.0	         5.00
daz    	        Apple	      Sweet apples.        	             6.0              5.50
company2        Apple             apples                                8.0              13.00

So, as it is sorted by the cheapest first I am want to display:

while ($row=mysql_fetch_assoc($queryPotentialMatches){


 //check first row ... if (first row = request.Quantity(i.e. 10kg) then this is the chepeast hence.. echo row 1){

}else{
//echo first row and see 10kg-5kg(requested - how has been added from the first row) = how much needs to be collected.

//check row 2 and see if it reuqested quantity makes up the demand. It does... hence output ... echo your request is met by 
// compny1 5kg@ 5.00 + daz 5kg@5.50 = £10.50 
//update the daz row to show new quantity as 1.0 kg


}

}

So that is the problem any guidance as to how I can solve this? Many thanks