PHP Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Programming Languages > PHP Development
Reload this Page

MYSQL help fulfil customer requests


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old February 16th, 2013, 04:35 PM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
Red face MYSQL help fulfil customer requests
I have a small problem whilst working with sql and php. I have a sql db.. which has 4 tables namely, customers, requests, products and sellers. Now, each table contains self explanatory data. Such as customers tables lists the customers, ids, names, adresses, emails etc., products table has product id and the name, the requests table shows the request of products made by customers with the [MYSQL]request id[pk], custID[fk from customer table], productid[fk in products table], quantites_requested, price_requested[/MYSQL]. The sellers table contains data such as [MYSQL]sellersid[pk], customerid[fk from customer table], productsid[fk in products table], quantites_advertised, price_advertised[/MYSQL]
Now what I wish to do, is say a customer has requested productid= 1(chocolate),customer id=1, 10kg has been requested for £10.00 So, this data is stored in the DB. Now, there might be various sellers of chocolate and what I wish to do is fulfil the customers order i.e. 10kg of choclates in the cheapest way possible. either by combining sellers or selecting one cheapest seller. So, so forexample, this is the sellers table. NOTE: the sellers table is structured by following: sellerid[pk],customerid[fk],productid[fk],quantity_advertised, price_advertised. so here are some example sellers...

1,2,1,4.00,2.00 -- This means customer id 2 is selling choclates, for 4KG's @£2.00
2,3,1,5.00,2.50
3,4,1,1.00,1.00
4,5,1,10.00,6.00
Now we have our requests that state

1,1,1,10.00,5.00 -- i.e. customer1, wants choclates of 10kg for £5.00...
Now I wish to fulfill the customers 10kg order. So what I need to do is select the cheapest price for the customer. This could be done by selecting sellers 2,3 and 4 with the kg's of 5,4 and 1kg=10kg which would cost a total of £2.00+£2.50+£1.00=£5.50 cheaper then seller 5 which can supply 10KG for 6.00. I believe I would be able to do something like this using an SQL query where it first matches the customer reuqests product id to the sellers product id i.e. SELECT c.Name, p.Name, s.quantity,s.price WHERE c.id=s.customerid AND p.id=s.productid AND s.quantity WHERE r.productid=s.productid FROM requests r, sellers s however, how would I be able to select the cheapest option supplier either combined or a single large supplier to take to be the most cost effective? I believe I would need to use the MIN somewhere in the query?

Can someone guide me on how to stucture a query as stated. i.e. the full customer request needs to be met in the cheapest way possible.
Reply With Quote
  #2  
Old February 17th, 2013, 05:26 AM
MrFujin's Avatar
MrFujin MrFujin is offline
Lord of the Dance
Dev Shed Loyal (3000 - 3499 posts)
  
Join Date: Oct 2003
Posts: 3,129 MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level)MrFujin User rank is General 11st Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 23 h 6 m 34 sec
Reputation Power: 1736
You should use [code ] (without space) instead of [MYSQL].

You statement has wrong syntax:
- You have to specify FROM after you SELECT
- SELECT can only have one WHERE clause.
- It is "better" to use the explicit join syntax, e.g.: INNER JOIN.
Reply With Quote
  #3  
Old February 17th, 2013, 09:25 AM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
Thanks for your reply Im sorry about the tags. I am new here and still getting used to it.
I am a little confused... So basically what I wish to do is select the MIN (i.e. the cheapest value to make up the demand)

so, if this is the table:


Code:
request id[pk], custID[fk from customer table], productid[fk in products table], quantites_requested, price_requested
this is the advertised_products table:

Code:
advertiseid[pk], customerid[fk from customer table], productsid[fk in products table], quantites_advertised, price_advertised


if I have this record in my request table:

Code:
1,1,1,10.0,5.00

and the following as advertised quantities:

Code:
1,2,1,4.00,2.00 -- This means customer id 2 is selling choclates(product id 1), for 4KG's @£2.00
2,3,1,5.00,2.50
3,4,1,1.00,1.00
4,5,1,10.00,6.00


How would I be able to use an sql select statement so that: I can make up the customer's request quantity?

I wanted to be able to select the cheapest total that makes up the requested quantity.

so, in this case the output would show:

Code:
2,1,4.00,2.00
3,1,5.00,2.50
4,1,1.00,1.00

so customer 2,3 and 4 would be displayed as their quantities of 4+5+1=10.0 makes up the requested quantity... and it is also cheaper then seller number 5.

Is there any such query I can use so that I would be able to look up the SQL table and cumulatively add up the quantities until the requested amount and then check which is the cheapest one to use?

Thanks for your help and time.

--
Quote:
Originally Posted by MrFujin
You should use [code ] (without space) instead of [MYSQL].

You statement has wrong syntax:
- You have to specify FROM after you SELECT
- SELECT can only have one WHERE clause.
- It is "better" to use the explicit join syntax, e.g.: INNER JOIN.
Reply With Quote
  #4  
Old February 17th, 2013, 01:29 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,373 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140
Quote:
Originally Posted by dazase1
I wanted to be able to select the cheapest total that makes up the requested quantity.
don't use MIN

instead, select the products and use ORDER BY price ASC

then, in your application layer (php or whatever), read off the top few rows of the results until you get the quantity you want

you will exhaust the cheapest first, then the second cheapest, and so on

if you feel better about it, use LIMIT to limit the results to 10 rows or whatever, some number where you feel comfortable that you'll make up the user's request within that number of rows
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5  
Old February 17th, 2013, 02:06 PM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
Hi, yes I am indeed using PHP and that helps put things into perspective a lot. I kept on focusing on using SQL to select the records.

I think what I need to do is...:

1)Query the requests table and see where the productId = sellers product id (i.e. identify the matches to the demands)
Code:
$query1 = mysql_query("select r.ProductID, s.ProductID FROM requests r, sellers s WHERE r.ProductID=s.ProductID)
and then
Code:
if (mysql_num_rows ($query1) <1) 
echo "no sellers are selling  the product requested"

however if there query returns results then...
2)check if the quantity requested can be cumulatively added up from the list of matching products in the cheapest way. And then display this to the user. However, this is what I am really stuck on. I have tried so hard but dont know how to do this. I mean say the requested quantity was 10KG
and the sellers had
Code:
1,4.00,4.00,1
2,5.00,5.50,1
3,1.00,1.00,1
4,10.0, 14.00,1


where the values represent (sellerNO, Quantity, Price(£), productID) respectively. Then in what way would I be able to output..

Your 10KG demand for product id1 can be met
by sellecting the cheapest option of:
seller 1, 4kg @ £4.00
seller 2, 5kg @ 5.50
seller 3, 1KG @ 1.00

total to pay = £10.50


I would be so glad if someone can help and guide me on how to do step two of fulfilling the quantity requested by looking at the sellers table and displaying the cheapest option. Spent 20 hours this weekend trying to do it and I just keep going in circles. Dont know what to do :'(

Quote:
Originally Posted by r937
don't use MIN

instead, select the products and use ORDER BY price ASC

then, in your application layer (php or whatever), read off the top few rows of the results until you get the quantity you want

you will exhaust the cheapest first, then the second cheapest, and so on

if you feel better about it, use LIMIT to limit the results to 10 rows or whatever, some number where you feel comfortable that you'll make up the user's request within that number of rows
Reply With Quote
  #6  
Old February 17th, 2013, 05:58 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,373 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140
Quote:
Originally Posted by dazase1
1)Query the requests table and see where the productId = sellers product id (i.e. identify the matches to the demands)
Code:
$query1 = mysql_query("select r.ProductID, s.ProductID FROM requests r, sellers s WHERE r.ProductID=s.ProductID)
you really need to return the price and quantity columns as well as just the product codes

and you did forgots the ORDER BY i suggested
Code:
SELECT r.ProductID
     , r.quantites_requested
     , r.price_requested
     , s.customerID
     , s.quantites_advertised
     , s.price_advertised 
  FROM requests AS r
INNER
  JOIN sellers AS s 
    ON s.ProductID = r.ProductID
 WHERE r.requestID = 12345 -- the request you're working on 
ORDER
    BY r.ProductID
     , s.price_advertised ASC


also, please learn INNER JOINs instead of the comma style joins, and also please, for your own sanity, write your sql queries with line breaks and indentation
Reply With Quote
  #7  
Old February 18th, 2013, 03:40 PM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
Hi,
Firstly, I would like to say thank you for all the help and advice you are providing it really is helping me solve this problem.

Here is the SQL query I decided to use:

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


Okay, so what this query is doing as you may already know is listing the currently 'Available products' the customer has requested for sale sorted by the cheapest first.

so an an example. This is the requests table
Code:
 NOTE: CID 2=DAZ(generally a seller), 3=Paul (Generally purchase guy) and 5= compny1 (generally a seller) NOTE: product id 1= apple, product id 2=pear, productid 3= berries, product id4=orange

The Request table looks like this after selecting records MatchType=Price and cust ID=3:

 requestid |   cid   |   productid   | Quantity | Price  | matchtype
    ------------------------------------------------------------------
    1          3            1            3.0     2.00        price
    3          3            4            4.0     2.50        price
    4          3            3            2.5     2.00        exact  
    5          3            2            3.0     3.50        exact
    6          3            3            3.0     2.00        exact
    7          3            1            10.0    7.00        price



and here is the available sellers table:
Code:
promotionID |   cid   |   productid   | Quantity | Price | description  
    ------------------------------------------------------------------
        1          2            4          5.0     2.99        oranges
        2          2            3          1.5     1.00        hand strawberries        
        3          2            3          2.5     2.00        l stawberries  
        4          2            2          3.0     3.00        pear       
        5          5            1          5.0     5.00        royal apples fm appleco.         
        6          2            1          6.0     5.50          sweet apples


After running the query the output is shown below:

Code:
FName |  ProductName    |     Description             |  Quantity  |   Price
-------------------------------------------------------------------------------------
daz          Oranges           Fresh and sweet oranges.       5.0       2.99
compny1     Apple               royal apples fm appleco.      5.0        5.00
daz          Apple             sweet apples                   6.0      5.50



Okay, so now what I want to do is: There is only one match found for oranges. Only one seller is selling oranges. so we have no choice but to display that option in order to match his request of oranges.
The only other request left for this customer is Apples since the matchType is by 'Price'
The user wants 10.0kg of apples.

Now we can see from out search result

Code:
 FName |  ProductName    |     Description             |  Quantity  |   Price
-------------------------------------------------------------------------------------
daz          Oranges           Fresh and sweet oranges.       5.0       2.99
compny1     Apple               royal apples fm appleco.      5.0        5.00
daz          Apple             sweet apples                   6.0      5.50


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.

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

Then I am stuck after this step. How can i acutally keep adding the quantities until the request.quantity is met for that perticular product requested?


I know this is a bit long to read but I didnt know how else to explain it. Any help at all would be most appreciated. And sorry for the incorrect formatting previous time round ...

Once again thanks a lot of reading, everyones advice and help provided. Much thanks





Quote:
Originally Posted by r937
you really need to return the price and quantity columns as well as just the product codes

and you did forgots the ORDER BY i suggested
Code:
SELECT r.ProductID
     , r.quantites_requested
     , r.price_requested
     , s.customerID
     , s.quantites_advertised
     , s.price_advertised 
  FROM requests AS r
INNER
  JOIN sellers AS s 
    ON s.ProductID = r.ProductID
 WHERE r.requestID = 12345 -- the request you're working on 
ORDER
    BY r.ProductID
     , s.price_advertised ASC


also, please learn INNER JOINs instead of the comma style joins, and also please, for your own sanity, write your sql queries with line breaks and indentation
Last edited by dazase1 : February 18th, 2013 at 05:01 PM. Reason: database values
Reply With Quote
  #8  
Old February 18th, 2013, 04:15 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,373 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140
Quote:
Originally Posted by dazase1
Now, I think will need to do most of this in PHP but after the query I dont know where to do.
i've moved this thread over to the php forum so those guys can advise you
Reply With Quote
  #9  
Old February 18th, 2013, 04:21 PM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
thanks for this. You have been good support.

Quote:
Originally Posted by r937
i've moved this thread over to the php forum so those guys can advise you
Reply With Quote
  #10  
Old February 20th, 2013, 11:33 AM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
Anyone here know any idea or guidance please?
Reply With Quote
  #11  
Old February 27th, 2013, 03:24 PM
dazase1 dazase1 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 21 dazase1 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 45 m 58 sec
Reputation Power: 0
Quote:
Originally Posted by r937
i've moved this thread over to the php forum so those guys can advise you


Hi, it has been a while and I still dont know how I can solve this problem. Can you suggest something for me please like where else I could ask maybe?


Thanks
Reply With Quote
Reply

Viewing: Dev Shed Forums > Programming Languages > PHP Development > MYSQL help fulfil customer requests

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap