Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0

    Obtain data from SQL DB until condition has been met


    Hi,
    I have a SQL Database and I wish to obtain records until the customer's quantity requested has been met. Here is what I mean.
    Request Table
    Code:
    Product     Quantity   Price
    -----------------------
    Apple          10.0      5.00
    here is the SQL query to obtain matches:
    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
    And here is the result:
    Code:
             FName   |   ProductName    |     Description                 |  Quantity  |   Price
            --------------------------------------------------------------------------=
         compny1          Apple                    royal apples fm appleco.      5.0          5.00
            daz            Apple                     sweet apples                6.0          5.50
        company2         Apple                       Apples yum                   8.0          9.00
    I want to display the full requested 10KG quantity by selecting the rows and updating the database i.e. the output should be:

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

    the Database should then show 'Daz' quantity being 1.0 KG left. However, I keep getting suck in doing this. I have tried to do the following:
    Code:
      while ($rows1 = mysql_fetch_assoc($queryQuantity2)){
    
        	if($rows1['Quantity']==$quantityRequested){ //If the first row = 10KG output only this row.
    echo $rows1['FName'];
    echo $rows1['NameProduct'];
    echo $rows1['Quantity'];
    echo $rows1['Price'];
    }else{
    //stuck here check the next rows and see there is 6KG's .. We need 10KG Requested - 5KG from daz(row1) 
    //                                                                            -Remaining amount left(i.e.5KG)
    //hence, print the output specified above and UPDATE Database where quantity has been reduced by X amount.
    }
    I do not know how to do the next step i.e. check the next rows and keep added until the quantity is met. Can anyone please help me of what I need to do?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,617
    Rep Power
    595
    I don't think I understand your question. In the else block, you want to update the database? How do you decide what record to update? How much to you add to the quantity field each time?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Location
    Manchester
    Posts
    10
    Rep Power
    0
    If I've understood you right...you want to update the quantity remaining with the original quantity minus the requested amount...

    PHP Code:
    while ($rows1 mysql_fetch_assoc($queryQuantity2)){
        if(
    $quantityRequested 0){
            echo 
    $rows1['FName'];
            echo 
    $rows1['NameProduct'];
            echo 
    $rows1['Quantity'];
            echo 
    $rows1['Price'];
            
    mysql_query("UPDATE products SET quantity = quantity - ".$quantityRequested) or die (mysql_error());
            
    $quantityRequested -= $rows1['Quantity'];
        }

    Assuming the quantityRequested is set somewhere maybe from request table... just tell it to only output rows and update database while quantityRequested is greater than 0 and decrement the value of quantityRequested by the Quantity required value of the specific row before it moves on to the next row. If it reaches 0 it won't go any further meaning the one at 6 would end up at 1Kg left.

    Hope it helps
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    Originally Posted by gw1500se
    I don't think I understand your question. In the else block, you want to update the database? How do you decide what record to update? How much to you add to the quantity field each time?
    Hey, Okay let me try and explain... This is what the customer has requested (i.e. my request table)

    Code:
     requestid[PK] |   cid[FK]   |   productid[FK]   | Quantity | Price 
    ---------------------------------------------------------------------------
            7                3                 1          10.0         7.00
    here is the sellers table
    Code:
        FName   |   ProductName    |     Description                 |  Quantity  |   Price
            --------------------------------------------------------------------------
         compny1          Apple            royal apples fm appleco.      5.0          5.00
            daz            Apple              sweet apples                6.0          5.50
        company2         Apple            Apples yum                        8.0          9.00
    Ok, So what the problem is. I need to 'Make up' the 10KG requested demand.

    Now the 'Cheapest' way to do this is by sorting the available matches by Asc Price which I have.

    No we can see the First row shows 'compny1' selling 5KG for £5.00... So, we need 10KG hence we display row 1..
    Next, as we have not met the 10KG demand yet (still need another 5KG) we look at the second row. We see 'daz' is selling 6KG. but we only need 5more KG's to make the 10KG demand.
    Hence, what I wish to do is... UPDATE the table so that the first record is copied to another table and the second row is updated to 1.0kg remaining(6KG-5KG). And hence, display to the customer:
    Your 10KG can be purchased by

    compny1 5KG@5.00
    daz 5KG@5.50
    ------
    Total £10.50


    So, that is my problem. I dont know how to keep checking the rows until the quantity requested is met. I guess I need some kind of 'counter' that keeps track of how much quantity has been added added 'So far' and then check how much more is needed from the next row.

    Does this make sense? If you wish I could clarify more for you ??

    Thanks
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    Originally Posted by SWH Admin
    If I've understood you right...you want to update the quantity remaining with the original quantity minus the requested amount...

    PHP Code:
    while ($rows1 mysql_fetch_assoc($queryQuantity2)){
        if(
    $quantityRequested 0){
            echo 
    $rows1['FName'];
            echo 
    $rows1['NameProduct'];
            echo 
    $rows1['Quantity'];
            echo 
    $rows1['Price'];
            
    mysql_query("UPDATE products SET quantity = quantity - ".$quantityRequested) or die (mysql_error());
            
    $quantityRequested -= $rows1['Quantity'];
        }

    Assuming the quantityRequested is set somewhere maybe from request table... just tell it to only output rows and update database while quantityRequested is greater than 0 and decrement the value of quantityRequested by the Quantity required value of the specific row before it moves on to the next row. If it reaches 0 it won't go any further meaning the one at 6 would end up at 1Kg left.

    Hope it helps
    Hi, Thanks for your reply. I think you are along the right lines. I would appreciate if you could read my response I gave on my previous post as this may help calrify and understand what I wish to achieve. If you could please read my reply to 'gw1500se' it might be helpful. Once again thanks
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Location
    Manchester
    Posts
    10
    Rep Power
    0
    Ok, I've added a bit more based on your last post...

    PHP Code:
    $quantityRequested 10// The 10Kgs requested which comes from your requests table

    while ($rows1 mysql_fetch_assoc($queryQuantity2)){
        if(
    $quantityRequested 0){ // This says as long as the $quantityRequested value is still above 0 then check the rows
            
    echo $rows1['FName'];
            echo 
    $rows1['NameProduct'];
            echo 
    $rows1['Quantity'];
            echo 
    $rows1['Price'];
            
            
    mysql_query("INSERT INTO orders (company,product,quantity,price) VALUES (
            '"
    .$rows1['FName']."',
            '"
    .$rows1['NameProduct']."',
            '"
    .$rows1['Quantity']."',
            '"
    .$rows1['Price']."'
            )"
    ) or die (mysql_error()); // inserts the data into a new table
            
            
    mysql_query("UPDATE products SET quantity = quantity - ".$rows1['Quantity']) or die (mysql_error()); // Updates the products table by reducing the available quantity
            
            
    $quantityRequested -= $rows1['Quantity']; // decrements the $quantityRequested value
        
    }

    I've added comments too which explain what each part does.

    Hope it helps

    **By the way, I'm assuming the table is called orders where it inserts the data to
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,617
    Rep Power
    595
    OK, so you don't really mean you want to update the database. You want to keep reading sorted rows until the quantity is met. You need to keep a counter initialized before the loop. Use that counter for your loop not the row data. Untested:
    PHP Code:
     $quantity=0;
     
    $price=0;
     while (
    $rows1 mysql_fetch_assoc($queryQuantity2)){
            if(
    $quantity>=$quantityRequested){
                echo 
    "$price<br />";
                break;
            }else{
              echo 
    $rows1['FName'];
              echo 
    $rows1['NameProduct'];
              echo 
    $rows1['Quantity'];
              echo 
    $rows1['Price']; 
              
    $quantity+=$rows1['Quantity'];
              
    $price+=$rows1['Price'];
       }

    You may need to play with it a bit to get exactly what you need but this should get you close.

    Note the use of [ PHP ] tags for code. See the sticky at the top of this forum.
    Last edited by gw1500se; March 2nd, 2013 at 07:13 PM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    Originally Posted by SWH Admin
    Ok, I've added a bit more based on your last post...

    **By the way, I'm assuming the table is called orders where it inserts the data to
    Hey thanks so much I am also from the UK and as it is late now I shall indeed give this a go tomorrow and it looks really positive from what I can read.

    I have 4 tables at the moment.

    Customers (CID, name, address, email, password)
    Products (PID, Name)
    Sellers (SID,CID,PID,Descp,Price,Quantity)
    Requests (RequestID,CID,PID,QuanitityReqested,PriceRequested)

    CID,PID,SID,RequestID are all primary keys in there own table and forign keys in the other tables.

    So, I have a order table but without any order yet. How would you suggest I suggest I structure this table based on my previous tables?

    Thanks
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,617
    Rep Power
    595
    Not being a DBA my suggestions would be as good as yours. For that I suggest you formulate a separate question and put it on the MySQL forum.

    Also, I forgot to mention that you should change your code and stop using the deprecated MySQL extensions. You should be using PDO.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    there are several big issues with the above suggestions.

    Like gw1500se already said, the old MySQL extension is hopelessly outdated. To be exact, it's obsolete since almost 10 years and will officially be deprecated in the next PHP version 5.5. This means every call to mysql_connect, mysql_query etc. will generate an E_DEPRECATED error. If you got any chance to switch to the "new" extensions, do it.

    A big issue of the old extension is that people constantly forget to escape variables before inserting them into their query strings, which makes the code vulnerable to SQL injections. This also happened above. Check the link in my signature to avoid typical mistakes like that.

    If this is an actual website with real money and real products involved, you also cannot send queries in that naive way. What if two concurrent queries each buy all apples from a certain seller? What if the ordering process crashes between inserting the order and reducing the available amount? etc.

    As you can see, it's not as easy as doing an INSERT and then an UPDATE. This might work on some private homepage, but not with critical data. You'll need a transaction.

    Given all those problems, I strongly suggest using standard ERP software or hiring an actual programmer/DBA to implement the website. Yeah, that will cost you some dollars. But at least you can be pretty sure that your website won't get hacked by some script kiddies stealing your customers' credit card data ("Oops, I didn't know about SQL injection."). And your database won't break down if you happen to get two orders at the same time.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  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 Jacques1
    If this is an actual website with real money and real products involved, you also cannot send queries in that naive way. What if two concurrent queries each buy all apples from a certain seller? What if the ordering process crashes between inserting the order and reducing the available amount? etc.

    As you can see, it's not as easy as doing an INSERT and then an UPDATE. This might work on some private homepage, but not with critical data. You'll need a transaction.

    Given all those problems, I strongly suggest using standard ERP software or hiring an actual programmer/DBA to implement the website.

    Hey, Thanks very much for your response. I throughly appreciate and understand what you are saying. It is not as secure at all with the sql injections and all. At the moment it will not be a 'Real' application. I am just using it as a localhost and dont plan on making it live at all. It is however my project I am working on and since I have started a fair amount of it I dont have that much time to change everything i.e. all the code again and learn the new syntax and way its coded. However, I do fully understand what you mean and thanks for that feedback.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    Originally Posted by gw1500se
    OK, so you don't really mean you want to update the database. You want to keep reading sorted rows until the quantity is met. You need to keep a counter initialized before the loop. Use that counter for your loop not the row data. Untested:
    PHP Code:
     $quantity=0;
     
    $price=0;
     while (
    $rows1 mysql_fetch_assoc($queryQuantity2)){
            if(
    $quantity>=$quantityRequested){
                echo 
    "$price<br />";
                break;
            }else{
              echo 
    $rows1['FName'];
              echo 
    $rows1['NameProduct'];
              echo 
    $rows1['Quantity'];
              echo 
    $rows1['Price']; 
              
    $quantity+=$rows1['Quantity'];
              
    $price+=$rows1['Price'];
       }

    You may need to play with it a bit to get exactly what you need but this should get you close.

    Note the use of [ PHP ] tags for code. See the sticky at the top of this forum.
    Hi,
    Yes what I wish to do is look at the first row see if the quantity can be satisfied. If it can then we dont need to look at any other rows just display to the user you can meet ur quantity by selecting just the first row seller.
    However, if the first row seller is not meeting the quantity requested. Then, add the first row's (sellers) quantity work out how much you still need to meet the requested quantity look at second row quantity + first row. Is this = to the quantity required? if it is then we display the total and use a orders table to show the new reflected quantities i.e. in this case seller 'compny1' quantity will be 0 and 'daz' will 1.0 ...

    I am really stuck on how I can do this. Spend a long amount of time but just dont know what to do. I know the logic of what needs to be done having problems of how I can code it.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    21
    Rep Power
    0
    Originally Posted by SWH Admin
    Ok, I've added a bit more based on your last post...

    PHP Code:
    $quantityRequested 10// The 10Kgs requested which comes from your requests table

    while ($rows1 mysql_fetch_assoc($queryQuantity2)){
        if(
    $quantityRequested 0){ // This says as long as the $quantityRequested value is still above 0 then check the rows
            
    echo $rows1['FName'];
            echo 
    $rows1['NameProduct'];
            echo 
    $rows1['Quantity'];
            echo 
    $rows1['Price'];
            
            
    mysql_query("INSERT INTO orders (company,product,quantity,price) VALUES (
            '"
    .$rows1['FName']."',
            '"
    .$rows1['NameProduct']."',
            '"
    .$rows1['Quantity']."',
            '"
    .$rows1['Price']."'
            )"
    ) or die (mysql_error()); // inserts the data into a new table
            
            
    mysql_query("UPDATE products SET quantity = quantity - ".$rows1['Quantity']) or die (mysql_error()); // Updates the products table by reducing the available quantity
            
            
    $quantityRequested -= $rows1['Quantity']; // decrements the $quantityRequested value
        
    }

    I've added comments too which explain what each part does.

    Hope it helps

    **By the way, I'm assuming the table is called orders where it inserts the data to
    Hey, thanks for helping me.
    Ok, so this is the problem.
    I have my 4 tables. Customers, Products, Requests and sellers. Each on has the fields I mentioned in my previous post.

    No the customer comes on the site and fills in a form to show what they wish to purchase . Hence, fill in the product, quantity, price.
    Now, a seller comes along and says okay I have X product to sell, for X quantity at X price.
    Another seller may also be selling the same product for a different price and quantity.

    Now, In order to meet the customer's quantity I am using the query mentioned to get the suitable 'Available' matches sorted by price.

    so say..
    Request table shows
    Code:
    customer3 apples 10KG for £7.00
    Now we have 3 sellers potentially who can match the customer's request of 10KG. (obtained using the SQL stated)

    Code:
    seller1 5kg £5.00
    seller2 6Kg £5.50
    seller3 15kg £25.00
    so, I am running the while loop to access each record:

    PHP Code:
     while ($rows1 mysql_fetch_assoc($queryPrice2)){ 

     
    //so this checks say if the sorted query the first row matches the requested 
     //then display this result. Hence, the demand has been met the cheapest way.

            
    if($rows1['Quantity']==$quantityRequested){
           echo 
    $rows1['ProductName'];
           echo 
    $rows1['FName'];
           echo 
    $rows1['Quantity'];
           echo 
    $rows1['Price'];
    }else{
        
    // What needs to be done here is. 
        // ADD the first row's quantity. Check how much more is needed. i.e.
        // 5.0 quantity from first row received... need another 5.0
        // The next row shows the quantity as 6.0kg
       // we need 5.0kg fm that row. + 5.0 from first row.  hence quantity has been          met
       //echo row's 1 and 2 in a table 
       //table headings here
                           
                            
    echo "<tr>";
                echo 
    "<td>".$rows1['FName']."</td>";
                echo 
    "<td>".$rows1['ProductName']."</td>";
                echo 
    "<td>".$rows1['Quantity']."</td>";
                echo 
    "<td>".$rows1['Price']."</td>";
                            echo 
    "<td>"."<input type= \"button\" value= \"COMMIT\">";
                echo 
    "</tr>";
    }

    hence, the table would show

    Code:
    FName      |     Product     |    Quantity     |   Price
    ---------------------------------------------------------------
    compny1         apple              5.0                5.00
    daz                apple              5.0                 6.50
     
    total =  £11.50
    And then once the customer 'COMMITS' to the deal, the seller's table is updated and the new rows are added to the order's table.

    Any help at all would be so so helpful as I really am stuck of how I can do this.

    I hope this helps answers any questions
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,617
    Rep Power
    595
    First, as Jacques1 and I advised rewrite your code to use PDO and prepared statements. I won't advise you on code using obsolete extensions leaving yourself open to injections.

    Second, I pretty much gave you the solution. The only change is to check the quantity being added to it does not exceed the requested and adjust the price accordingly. You will need to add hidden fields to your form to tell PHP the quantity taken from each vendor record so the database can be updated accordingly. Keep in mind that PHP is stateless so all the needed information must be provided on each invocation of the script.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  28. #15
  29. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Note that he's crossposted his thread around the whole f*cking internet, so the question probably has been answered 10 times already.

    Don't waste your time repeating other peoples' replies.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo