473,326 Members | 2,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Reducing Quantities Request and Seller table

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:

Expand|Select|Wrap|Line Numbers
  1.   FName          |   ProductName    |     Description                 |  Quantity  |   Price
  2.         --------------------------------------------------------------------------=
  3.      compny1          Apple                    royal apples fm appleco.      5.0          5.00
  4.         daz          Apple                     sweet apples                  6.0            5.50
  5.  
I am running this query :

Expand|Select|Wrap|Line Numbers
  1.     select c.FName,
  2.             p.ProductName,
  3.             s.Description, 
  4.             s.Quantity, 
  5.             s.Price 
  6.           FROM requests r
  7.  
  8.         inner join sellers s on r.ProductID = s.ProductID
  9.         inner join products p on p.ProductID=s.ProductID 
  10.          inner join customers c on c.ID=s.C_ID       
  11.         where r.C_ID = 3 AND r.matchType='Price'
  12.         ORDER BY s.Price ASC 
  13.  
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
Mar 2 '13 #1
2 1440
Luuk
1,047 Expert 1GB
"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
Mar 2 '13 #2
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

Expand|Select|Wrap|Line Numbers
  1. Product     Quantity   Price
  2. -----------------------
  3. Apple    10.0      5.00 
  4.  
That table is outputted in PHP...

here is the potential matches
Expand|Select|Wrap|Line Numbers
  1. Name(seller) |  Product |           Descp                       |     Quantity |     Price    
  2. --------------------------------------------------------------------------------   
  3. compny1          Apple          apples from apple Co.                   5.0             5.00
  4. daz                Apple          Sweet apples.                         6.0              5.50
  5. company2        Apple             apples                                8.0              13.00
  6.  
So, as it is sorted by the cheapest first I am want to display:

Expand|Select|Wrap|Line Numbers
  1. while ($row=mysql_fetch_assoc($queryPotentialMatches){
  2.  
  3.  
  4.  //check first row ... if (first row = request.Quantity(i.e. 10kg) then this is the chepeast hence.. echo row 1){
  5.  
  6. }else{
  7. //echo first row and see 10kg-5kg(requested - how has been added from the first row) = how much needs to be collected.
  8.  
  9. //check row 2 and see if it reuqested quantity makes up the demand. It does... hence output ... echo your request is met by 
  10. // compny1 5kg@ 5.00 + daz 5kg@5.50 = £10.50 
  11. //update the daz row to show new quantity as 1.0 kg
  12.  
  13.  
  14. }
  15.  
  16. }
  17.  
So that is the problem any guidance as to how I can solve this? Many thanks
Mar 2 '13 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: ugo_lavoie | last post by:
HEy, this is normally pretty basic stuff but i dont know if i'm tired or what but right now i just can get it. Heres the problem... I have a form base on a table. 4 field : ID_Seller,...
13
by: Shannan Casteel via AccessMonster.com | last post by:
I set up two tables (one with the regular claim info and another with ClaimNumber, PartNumber, and QuantityReplaced). The ClaimNumber is an autonumber and the primary key in both tables. I made a...
3
by: ollyno1uk | last post by:
Hi there I have a table in MySQL database on my web server that contains stock levels amongst other things. twice a day I get emailed an updated stock list in CSV format. What I need is to...
149
by: jacob navia | last post by:
As a consequence of a heavy discussion in another thread, I wrote this program: #include <stdlib.h> int main(void) { char *p=calloc(65521,65552); if (p) printf("BUG!!!!\n"); }
11
by: Chris Thomasson | last post by:
Consider an an object that that can has 7 or 8 functions. If you create an abstract base class for the "interface" of the object, well, that means 7 or 8 pure virtual functions right? Well, IMHO,...
3
by: seangibson | last post by:
Here's the scenario: I have a list of transactions. I am a middle man in the transaction, so I collect a fee from both the buyer and the seller. My fields are Date,Buyer, Seller, Buyer Fee, Seller...
1
osward
by: osward | last post by:
Hi everyone, Background 1. I have a table that consits 400+ rows of data and is growing by day. The table already has paging links at the bottom but I restricted to display rows of data only >=...
15
ajhayes
by: ajhayes | last post by:
I posted a question here last week about an ordering database I'm trying to set up, and got some excellent help. So I'm hoping some of you might be able to get me on the right track with another...
2
Dormilich
by: Dormilich | last post by:
well, I need some help for designing a table that's showing some DB results and it should be able to sort/select some of the values. (there's no problem in getting the data once I know which ones) ...
11
by: manthiralaya | last post by:
Dear All, This query is related to MSAccess-Forms-VBA coding to adding new records in to a table using recordset. For example, I have a table named as 'Table1' with fields F1, F2, F3, F4. In...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.