[RESOLVED] Mysql and select menus

[Adrian28uk]

Something I cannot get my head around. I am querying my table products to populate the form fields to update a product.

However I also need to query two seperate tables and populate two different select menus in the main while statement.

Now I have solved this by using $row, $row1 & $row2

I know this is completely the wrong way of doing things, so could you please be kind enough to give me some tips on how we do this.

Please note this is rough code below, to give you an idea of what I mean:

===================================================
$result = mysql_query("SELECT * FROM products WHERE product_id='". $_POST['product_id'] .");

while($row = mysql_fetch_array($result))
{
// display form and populate fields

[INDENT]$result = mysql_query("SELECT category_id, category_name FROM category");

while($row1 = mysql_fetch_array($result))	
{
  populate drop down menu
}

$result = mysql_query("SELECT condition_id, condition_name FROM conditions");

while($row2 = mysql_fetch_array($result))	
{
  populate drop down menu
}[/INDENT]

}

[TobyRT]

Am I right in thinking that the two drop downs are merely alternative categories/conditions which can be applied to the product in the first query?

I do this kind of query a lot... i.e. providing many options for a primary object.

I find it easier to assign variables to my $_POST data too - it allows for easier error checking and other such stuff.

I'd do something like...

$product_id = $_POST['product_id'];

$product_data = mysql_query("SELECT * FROM products WHERE product_id='.$product_id.'"); 

while ($product_row = (mysql_fetch_array($product_data)) 
{ //open main while



//start to display table with product data
$catQuery = mysql_query("SELECT category_id, category_name FROM category");
   while($getCat = mysql_fetch_array($catQuery))
   {
   populate drop down menu
   }

$condQuery = mysql_query("SELECT condition_id, condition_name FROM conditions");

  while ($getCond = mysql_fetch_array($condQuery)) 
   {
   populate dropdown menu
   }

}//close main while

I think your problem is that you've got 3 queries, but each query is then put into an array using the variable $result.

People more experienced than me will tell you that your best bet is to probably do an advanced join or subquery, but this is what works for me!

//define the query
$catQuery = "select * from cat";

//run the query
$getCat = mysql_query($catQuery) or die (mysql_error());

//extract the data (here in a while loop)
while ($cat = mysql_fetch_array($getCat)){
echo '<option>'.$cat['cat_description'].'</option>';
}

Using this phrasing methodology helps me a lot when I'm running multiple queries on a page.
I work a lot with books, so a lot of my stuff relies on bibliographic data relating to a specific edition such as title, isbn, format, binding etc. Where I use 'xxx' imagine in the following that xxx="edition". (capitalising each subsequent word)
$xxxQuery = give the query variable an appropriate name
$getXxx = run the query variable with an appropriate name
$xxx = the equivalent of '$row' - but something that makes more sense for the query I'm running.

Hope that makes sense!

[Adrian28uk]

Am I right in thinking that the two drop downs are merely alternative categories/conditions which can be applied to the product in the first query?
Yes you have this spot on.

That's excellent thank you very much. The way you have described it has made perfect sense.