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:
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
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
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