Page 1 of 1

Category Conundrum

Posted: Tue Oct 31, 2006 8:50 pm
by trent2800
I am attempting to make a multiple select statement that uses optgroup to create categories. Im popluating it through a database. For this I will be using two tables: clothing, and categories. Clothing is a streightforward table using basic fields for price, QOH, etc... It also has a field called CategoryID which is related to the PK in category. Category has three fields: CategoryID, Name, and Parent. If a category is a main category, the parent field is null. Otherwise, the parent field contains the ID of the category it's in. I've hit a wall in trying to go through each category and listing out the products with the category name contained in optgroup tags. I would appreciate any hints/guidance you have in this area.

Posted: Wed Nov 01, 2006 12:15 am
by aaronhall
A recursively called function would do the trick. The function would take parent ID as an argument, and a loop inside the function would be cycling through categories of distinct parent IDs. For each loop, the function would check if that category had any children. If a child was found, it would recursively call itself with that category's parent ID as the argument.

Posted: Mon Nov 06, 2006 9:14 pm
by trent2800
Ok, this is what I came up with:

Code: Select all

function display_select ($CategoryID) {
	
	echo $CategoryID;
	
	if ($CategoryID){
		
		$query = "SELECT * FROM `CLOTHING` WHERE `CategoryID` = '".$CategoryID."'";

		$result = mysql_query($query);

		while($row = mysql_fetch_assoc($result)) {

			echo "<option value='".$row['ProductID']."'>".$row['Name']."</option>\n";

		}
		
	}else{

		$query = "SELECT * FROM `CATEGORY` WHERE `Parent` = '1'";

		$result = mysql_query($query);
		
		while($row = mysql_fetch_assoc($result)) {

				echo "<optgroup label='".$row['Name']."' />\n";
				
				display_select($row['CategoryID']);

		}
	}
}
A) Does this look good? I know it works... but is there some glaring problem that I'm not seeing but will bring down the entire website, and/or the entirety of humanity including kittens and puppies???

B) I've stumbled on a new problem. This works fine if I'm adding a new record but I need to figure out how to mark the <option> tags with SELECTED based on what is in the database. I've been messing around with making that work in the database. Currently, I am imploding the info from the <select> array, dumping it into the database then exploding the data back into an array. Anyone have some suggestions on what would be the best way to do this? I think I could nest another loop in the function, but doesn't that have some performance issues? As always, I appreciate any and all comments/suggestions/concerns/biting sarcasam that you can offer up.

Posted: Tue Nov 07, 2006 3:08 am
by Rovas
a) It' s the simplest and easiest method and if you use it on a single page and that page is not visited you will not have problems with the code. There is another way you ll find it here http://www.phpwebcommerce.com/shop-browse-category.php
b) I think you re making your code more complicated. You can use the attribute selected='selected' and if to check if the respective option has been checked.