Thread: MySQL query

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

    Join Date
    Feb 2000
    Location
    Chicago IL, USA
    Posts
    16
    Rep Power
    0

    I am trying to build a form based upon a query to MySQL. The form is a list of products, each with a quantity listbox (HTML <select> field). Each product may have a different set of allowable qty's (i.e., product 1 can be ordered in these qty's: 100, 200, 300 while product 2 can be ordered in qty's of 250, 500, or 1000).

    My solution is to have a products table and a qty table. Qty table has two columns, prod_id and qty.

    Can I do something like this:
    select p.prod_name, q.qty from products p, qty q where p.id = q.id

    and then loop through to populate the qty listbox for the first product, then get the next product and all qty's, and so forth? I'm confused about how to get the qty values for each product, then get the $row pointer to the next product and get all qty's again.

    I suppose I could do this by executing a query to select all products, then begin a loop executing another query for each product to get the qty values for that product, then move to the next product and do the same. This seems to be terribly inefficient as it could cause a large number of query's to be executed, even with a relatively small product list.

    Any ideas?

    thanks,

    michael
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Your query isn't bad, might need some tweaking. Are the various quantities available for each product going to be an even multiple? If not, how do you plan on using one field for quantities? (Can be done, just curious).

    As for the result set, yes, you can loop thru with something along these lines:

    while ($prod_data=mysql_fetch_row($result))
    {
    // display product info and quantity box
    }

    This will loop thru the products one at a time.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Chicago IL, USA
    Posts
    16
    Rep Power
    0
    Rod,
    Thanks for the quick reply. I'm still unsure about how to achieve the result.

    When you state "Are the various quantities available for each product going to be an even multiple?", do you mean will each product have the same number of qty options? If so, no. Some may have 2 options, while others may have 5 or 6 options.

    I'm sorry if I am missing something simple, but I am confused about your example:
    while ($prod_data=mysql_fetch_row($result))
    {
    // display product info and
    quantity box
    }

    If my query returns 5 rows that contain the following:

    product 1,100
    product 1,200
    product 2,250
    product 2,500
    product 2,750

    How do I get it to loop though, giving me this:
    <tr>
    <td>Product 1</td>
    <td>
    <select name='qty_1'>
    <option value='0'>Quantity</option>
    <option value='100'>100</option>
    <option value='200'>200</option>
    </select>
    </td>
    </tr>
    <td>Product 2</td>
    <td>
    <select name='qty_2'>
    <option value='0'>Quantity</option>
    <option value='250'>100</option>
    <option value='500'>200</option>
    <option value='750'>300</option>
    </select>
    </td>
    </tr>
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Ok, now I see how you are setting up the tables. I'm not sure that's the best way but let's see what we can do with it.

    $result=mysql_query("select p.product_name as pn, p.id as id, q.qty as qty from products p, qty q where p.id=q.id order by p.product_name");

    $pn="";
    while($data=mysql_fetch_array($result))
    {
    if ($pn!=$data[pn]) // start new line
    {
    if ($pn!="") print "</select><p>";
    $pn=$data[pn];
    print "$data[pn]: ";
    print "<select name='qty[".$data[$id]."]'>";
    print "<option value=0 default>0</option>";
    }
    print "<option value=$data[qty]>$data[qty]</option>";
    }

    This will give you the quantity selected in the array $qty with the product id as the index of the array.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Chicago IL, USA
    Posts
    16
    Rep Power
    0
    Rod,

    Thanks for the code. It works great.

    As for it not being the best way to do it, I'm completely open to suggestions to make it more efficient. Care to share?

    Thanks again for the help.

    michael
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Well, I said I wasn't sure it was the best way. Not sure if there is a better way with the limitations you describe, either.

Similar Threads

  1. MySQL COUNT query - help needed please.
    By rycrostud in forum MySQL Help
    Replies: 1
    Last Post: August 29th, 2003, 08:33 AM
  2. Efficient way to query MySQL
    By kingmike in forum PHP Development
    Replies: 7
    Last Post: July 30th, 2002, 03:19 PM
  3. Need help with a mysql query
    By mrdon in forum MySQL Help
    Replies: 3
    Last Post: July 23rd, 2002, 04:22 PM
  4. Replies: 2
    Last Post: July 22nd, 2002, 01:23 PM
  5. Can't get most recent date from MySQL query
    By jpluttme in forum MySQL Help
    Replies: 4
    Last Post: October 9th, 2001, 12:27 AM

IMN logo majestic logo threadwatch logo seochat tools logo