[Resolved] Drop down box question PHP/MYSQL

[jamesflynn]

Ok I have a table called products and within this table I have a category called cat (short for category) and a category for the actual product name (called product). What I would like to do is make a drop down that pulls from the DB and is sorted by cat listing all of the products for each cat underneath. So to look like this:

Cat1

product1
product2

Cat2

product3
product4

I'm guessing the best way to do this is to make a sperate query for each cat and then display the select option for each cat separate placing the header in manually? Is this the best way to do this or is there a better way?

Thanks,
James Flynn

[stolzyboy]

create a query with a while loop for the cat's, then in that while loop do another query for that certain cat to get the subcat's inside of those

[jamesflynn]

Hey Stolzy,

Thanks alot for your comments. I just spent about an hour righting the code of what i think it might look like in here and hit the reply but. and timeout (on my end not the boards)...
Just my luck :mad:
I'm gonna try to retype it

[stolzyboy]

k

[jamesflynn]

I saw this one:

$sql = "SELECT brand, product, productid FROM tablename order by brand, product";

// Returns result
$mysql_result=mysql_query($sql);
echo"Query run";

// Counts the number of rows

// Tests if the database is empty
if ($num_rows == 0) {
echo"<CENTER>You need to add some products first!</CENTER>";
}

// If there are records....
else {
// Form to pick the product to be ammended
echo "<FORM method=post action=\"view_record.php\">";
echo "Please select a product <BR>";
echo "<select name=\"record\">";

// Loop to populate the menu 
while ($row=mysql_fetch_array($mysql_result)) { 
    $product=$row["product"]; 
    $productid=$row["productid"]; 
	$cat=$row["cat"]; 
    // Display options in the pulldown box 
    echo "<option value=\"$productid\">$product</option>"; 
    } // end while 

echo "</select>"; 
} // end else 

echo "<BR><BR>";
echo "<input type=\"submit\" value=\"View the record\"></FORM>";

?>

and i thought it would be similar to what I'm doing but after looking at it closer I don't think it will. The main thing I'm unsure about is that because the (and I missnamed the categories. I actually named the categoery brand and im gonna sort by brand and product. brand beeing header) brand is its own category and product is a seperate category so how do I SELECT by a certain brand row but then loop $product row based on that.

Thanks for all of your help,
James Flynn

[stolzyboy]

this is kinda generic and using two tables, but you should get the idea

this is also untested, so you may have to tweak it a little

<?
$conn = mysql_connect("localhost", "username", "password") or die ("Could not connect to database!");
$db = mysql_select_db("intranet") or die ("Could not select database");

$query = "select * from table1;";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
	echo $row['fieldnamefromtable1'] . "<br>";
	$query1 = "select * from table2;";
	$result1 = mysql_query($query1) or die(mysql_error());
	while ($row = mysql_fetch_array($result))
	{
		echo $row['fieldnamefromtable2'];
	}
	echo "<br>";
}
?>

[jamesflynn]

Thanks I think that put me on the right track. I modified it some

$query = "select * from table;";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
echo $row['brand'] . "<br>
<select name=url onChange='return jump(this);' size=1>

	<option SELECTED>$brand</option>"; 
$query1 = "select * from table where brand='$brand';"; 
$result1 = mysql_query($query1) or die(mysql_error()); 
while ($row = mysql_fetch_array($result)) 
{ 
    echo $row['product'] . "<br> 


	<option>$product</option> </select>"; 
} 
echo "<br>"; 

}
?>

It displays the first brand then underneath it displays all of the products. Not just the products for the brand listed above. This seemed logical to me
select * from table where brand='$brand'
But it didn't seem to do the trick. Also none of the select items are actually in the select boxes (probably something in my SQL syntax.

Thanks for all your help,
James Flynn

[jamesflynn]

Well I got it working correctly. Only one things that it does strange is that it re-displays each category group as many times as there our records. So if theres 20 products under a certain brand it will display the Brand title list out the products underneath correctly but will then loop through nineteen more times displaying that brand list over and over.

Here's the code:

$query = "select * from table WHERE brand='Panasonic';";
$result = mysql_query($query) or die(mysql_error());

$query1 = "select * from table WHERE brand='Xerox';";
$result1 = mysql_query($query1) or die(mysql_error());

$query2 = "select * from table WHERE brand='Riso';";
$result2 = mysql_query($query2) or die(mysql_error());

$query3 = "select * from table WHERE brand='Kyocera';";
$result3 = mysql_query($query3) or die(mysql_error());

$query4 = "select * from table WHERE brand='Panafax';";
$result4 = mysql_query($query4) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
echo ("<option></OPTION>");
echo ("<OPTION>");
echo $row["brand"];
echo ("</OPTION>");
echo ("<option>----------------------------</OPTION>");

$query0_0 = "select product_id,product from table WHERE brand='Panasonic';"; 
$result0_0 = mysql_query($query0_0) or die(mysql_error()); 

while ($row = mysql_fetch_array($result0_0))
{ 
echo ("<OPTION VALUE=showresults.php?make=".urlencode($row["product_id"]).">");
echo $row["product"]; 
echo ("</OPTION>"); 
} 
echo "<br>"; 

}

while ($row = mysql_fetch_array($result1))
{
echo ("<option></OPTION>");
echo ("<OPTION>");
echo $row["brand"];
echo ("</OPTION>");
echo ("<option>----------------------------</OPTION>");

$query1_1 = "select product_id,product from table WHERE brand='Xerox';"; 
$result1_1 = mysql_query($query1_1) or die(mysql_error()); 

while ($row = mysql_fetch_array($result1_1))
{ 
echo ("<OPTION VALUE=showresults.php?make=".urlencode($row["product_id"]).">");
echo $row["product"]; 
echo ("</OPTION>"); 
} 
echo "<br>"; 

}

while ($row = mysql_fetch_array($result2))
{
echo ("<option></OPTION>");
echo ("<OPTION>");
echo $row["brand"];
echo ("</OPTION>");
echo ("<option>----------------------------</OPTION>");

$query2_2 = "select product_id,product from table WHERE brand='Riso';"; 
$result2_2 = mysql_query($query2_2) or die(mysql_error()); 

while ($row = mysql_fetch_array($result2_2))
{ 
echo ("<OPTION VALUE=showresults.php?make=".urlencode($row["product_id"]).">");
echo $row["product"]; 
echo ("</OPTION>"); 
} 
echo "<br>"; 

}

while ($row = mysql_fetch_array($result3))
{
echo ("<option></OPTION>");
echo ("<OPTION>");
echo $row["brand"];
echo ("</OPTION>");
echo ("<option>----------------------------</OPTION>");

$query3_3 = "select product_id,product from table WHERE brand='Kyocera';"; 
$result3_3 = mysql_query($query3_3) or die(mysql_error()); 

while ($row = mysql_fetch_array($result3_3))
{ 
echo ("<OPTION VALUE=showresults.php?make=".urlencode($row["product_id"]).">");
echo $row["product"]; 
echo ("</OPTION>"); 
} 
echo "<br>"; 

}

while ($row = mysql_fetch_array($result4))
{
echo ("<option></OPTION>");
echo ("<OPTION>");
echo $row["brand"];
echo ("</OPTION>");
echo ("<option>----------------------------</OPTION>");

$query4_4 = "select product_id,product from table WHERE brand='Panafax';"; 
$result4_4 = mysql_query($query4_4) or die(mysql_error()); 

while ($row = mysql_fetch_array($result4_4))
{ 
echo ("<OPTION VALUE=showresults.php?make=".urlencode($row["product_id"]).">");
echo $row["product"]; 
echo ("</OPTION>"); 
} 
echo "<br>"; 

}

[zonkd]

Did you get a final answer, jamesflynn?

I want to have a drop down selection box for 4 items - but I'm struggling to find an example of how it's done. Is there somewhere where I could see (admire) yours please?