Page 1 of 1

Structure MySQL database for a select box.

Posted: Sun Nov 23, 2003 10:43 am
by lunartek
I'm having some problems with structuring the database for it to progress the data as I want it. I have a table that looks something like this:

Code: Select all

categories:
--------------------------------------------------------------
uid       group_id       level       group_name
--------------------------------------------------------------
1         1                 0            category#01
2         2                 0            category#02
3         1                 1            sub category#01
4         1                 2            sub category#02
5         2                 1            sub category#03
The levels are set as where the category should be shown, 0 is for top category and so on. Now, using the select box tag I want it to display this:

Code: Select all

category#01
  sub category#01
     sub category#02
category#02
  sub category#03
I'm not sure how to go about this. All categories including all sub categories will be created on the fly by an online editor tool so they must be kept in same table to work I believe.

Any suggestions on this would be appreciated :)

Posted: Sun Nov 23, 2003 9:37 pm
by McGruff
Not entirely sure what you're getting at but my first thought is that doesn't look like a properly normalised db design http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf.

Group data should probably have their own table - group names don't belong in a user table, same might apply to levels (it could be that it's better to assign these to groups, but then I don't know exactly how they are used).

Posted: Mon Nov 24, 2003 9:47 am
by lunartek
The groups has their own table, I want all top groups and sub groups in same table because when you create a new sub category (group) within another category from the online editor tool, then you can't really create a new table for every sub category. That wouldn't sound right to me.

The data that will be stored in this top categories, sub categories are in a different table and that's not what I have problem with. The problem is the select box, I want the the categories to be ordered in right way.

I use this code now:

Code: Select all

$result = mysql_query("SELECT * FROM cyberwear_categories",$connect);
$numrows = mysql_num_rows($result);
if ($numrows>0){
	while($load = mysql_fetch_array(mysql_query("SELECT * FROM cyberwear_categories",$connect))){
		if ($load['level']==0){
			echo "<option value='".$load['uid']."'>".ucwords($load['name'])."</option>";
		}
		else{
			echo "<option value='".$load['uid']."'>--".ucwords($load['name'])."</option>";
		}
	}
}
else{
echo "<option>None Available</option>";
}
As you can see sub categories will be shown like --my sub category. However these will not be listed under correct top category. So I want the listing in the selection box to be as this:

Code: Select all

category#01 (first top category that has level 0)
--subcategory#01 (first sub category that has level 1)
----subcategory#02 (second sub category that has level 2)
category#02 (second top category that has level 0)
--subcategory#03 (first sub category that has level 1)
It shouldn't matter how many sub categories one has, so I think I must use some sort of loop but not sure how. I hope this make some sense, I might be using wrong database structure or just missing some php code.

Posted: Mon Nov 24, 2003 10:54 am
by Weirdan
Maybe this will be of help...

Posted: Mon Nov 24, 2003 9:16 pm
by McGruff