MySQL query

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

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.

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>

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.

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

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.