[SOLVED] Creating main and sub categories

[Jason28]

It is hard for me to find a simple example of how to do this.  I already created a db table that has the category name, and two fields "main_cat" and "sub_cat".  The main cattegory will have values in the main cat table, and the sub categories will have the value of the main cat in their sub cat field I assume?  So how what query would I use to display on a webpage such as:

 

Main category name 1

- sub cat name 1

- sub cat name 2

- sub cat name 3

- and so on...

 

Main category name 2

- sub cat name 1

- sub cat name 2

- sub cat name 3

- and so on...

 

If you could provide me with a working php example that would be great.  Thanks.

[bluejay002]

i dont think a single table is good. try creating two tables: one for the header (main category) and the other for the subheader (subcategory).

 

this way, tables would be more normalized.

 

i think this should be under mysql other than php... what do you think modo?

[Jason28]

Ok thanks.  I would still need a php example that displays the info in the organized format.  Well this topic can go in either forum but since I need php code it should be ok to ask in here. 

[Jason28]

Actually I am looking at other scripts DB info and they layout the mysql tables the same way so I will stick with that.  I still need to figure out the php end to retrieve it.  If no one helps I will just have to tinker with it.

[Jason28]

Ah well nevermind I just threw some code together and it worked.  Hopefully this is the best way to write it:

 

$sql = "SELECT main_cat, genre_name FROM " . GENRES_TABLE . " WHERE main_cat > 0";
$result = mysql_query($sql);

while ( $row = mysql_fetch_array($result) )
{
	echo $row['genre_name'] . '<br>';

	$sql2 = "SELECT main_cat, genre_name FROM " . GENRES_TABLE . " WHERE sub_cat = '".$row['main_cat']."'";
	$result2 = mysql_query($sql2);

	while ( $row2 = mysql_fetch_array($result2) )
	{

		echo $row2['genre_name'] . '<br>';
	}
}