#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep 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.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,635
    Rep Power
    1945
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep 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.

    --
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep 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 :'(

    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
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep 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





    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
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    thanks for this. You have been good support.

    Originally Posted by r937
    i've moved this thread over to the php forum so those guys can advise you
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    Anyone here know any idea or guidance please?
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo