Asked
Updated
Viewed
20.6k times

I am trying to create a category system with multiple subcategories.

I would like the categories and sub-categories to be able to have sub-categories as well. I do not know how deep the nesting should go, so it should be assumed that they will infinitely nest.

What sort of logic should be used to create an infinitely nested category structure?

add a comment
1

3 Answers

  • Votes
  • Oldest
  • Latest
Answered
Updated

To expand on RabidDog's idea using a recursive solution, I am in agreement with that.

The first solution I provided was just the SQL solution, which can be taken in a global applicative solution, depending on what he needs as an approach vertical/horizontal/both.

Horizontal:

SELECT c.ID_CATEGORY, c.NAME CATEGORY, ID_SUB_CATEGORY, c2.NAME_CATEGORY FROM CATEGORY c JOIN RELATION r ON (c.ID_CATEGORY=r.ID_CATEGORY) JOIN CATEGORY c2 ON(ID_SUB_CATEGORY=c2.ID_CATEGORY) ORDER BY c.ID_CATEGORY 

Vertical, something looking like that:

function single_genealogy($category, $level = 0)
{
	$q = "SELECT ID_SUB_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE c.ID_CATEGORY=".$category;
	$r = mysql_query($q); //or die/mail/echo mysql_error()
	if(mysql_num_rows($r))
	{
		$level++;
		while($d = mysql_fetch_row($q))
		{
			//displaying current node : $d[1]...
			//recursive call :
			single_genealogy($d[0], $level);
		}
	}
}

for a given category, that'll show :

level(<=given one)
   level
      level
      level
   level
   level

Very importantly, a category mustn't be twice in a single genealogical tree otherwise it'll turn the recursive solution into a perpetual one! 🙂

  • 0
    I'm not getting this at all, I think I should go away and do some serious reading. Thanks for all the help though. At least it put me in the right direction. — vegancoder
  • 0
    I think this has gone a bit fast for vegancoder and that's because there's a lot involved. I wrote some subcategory stuff a year ago and found that a single table of categories suited all my needs. Each category had a name, an ID, and a parentCategoryID. The top-level categories had a parentCategoryID of 0. The reason IDs were so important was so that multiple subcategories could use the same name without confusion. Important for different products subcategorized by brand, and brand can be used many times. Anyway, you'll need to create a database and the table/s first, then establish that you can talk to it in a basic manner say add something to a table and then retrieve it. Then you can happily develop some more advanced tricks. — fluxstream
  • 0
    No, I understand how to add, edit and remove data from tables, and I understand how ids and keys work. It's just getting my head around all the join stuff. — vegancoder
add a comment
1
Answered
Updated

You could make 2 tables:

CATEGORY and SUB_CATEGORY

The first table CATEGORY would hold the info about each category no matter what it is hierarchically.

The second table SUB_CATEGORY would hold (unique) couples of associations ID_CATEGORY to ID_SUB_CATEGORY.
A category could be in ID_CATEGORY, in all the rows showing her "daughters" and also in ID_SUB_CATEGORY in the rows showing her "mothers"

CATEGORY:

ID_CATEGORY
NAME_CATEGORY

RELATION

ID_CATEGORY
ID_SUB_CATERORY

ALL the categories have an entry in the first table. The second show all the subcategory for each ID_CATEGORY that has got at least one, in fact, if you want to register a subcategory you put in the id of the mother ID_CATEGORY and it's own id ID_SUB_CATEGORY, that'll list all the subcategories of one category :

SELECT ID_SUB_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE c.ID_CATEGORY=CAT_WHAT_CATEGORY_ARE_SEARCHED

You can also check if a category is a subcategory:

SELECT c.ID_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE ID_SUB_CATEGORY=CAT_WHAT_MOTHER_CATEGORY_ARE_SEARCHED
  • 0
    hmmm sorry don't quite follow. — vegancoder
  • 0
    Ok, I understand better now, so would I need to query the database for every category? That seems like it could be a load on the server. — vegancoder
  • 0
    No need to do a query for each category, of course, you can list all categories with their sub-categories within a query, I didn't test it but it'll be something like that (duplicating CATEGORY as c2 to get subcategories info): SELECT c.ID_CATEGORY, c.NAME CATEGORY, ID_SUB_CATEGORY, c2.NAME_CATEGORY FROM CATEGORY c JOIN RELATION r ON (c.ID_CATEGORY=r.ID_CATEGORY) JOIN CATEGORY c2 ON(ID_SUB_CATEGORY=c2.ID_CATEGORY) ORDER BY c.ID_CATEGORY — gisele
  • 0
    Right, see this is way over my head (at the moment). I don't understand anything about joins. Do you think you could point me to a good tutorial? I had a look at W3 Schools and read a bit on unions and joins, do they just allow data to be selected from more than one table in one query? Obviously, they can do more but is that basically what they do? — vegancoder
  • 0
    Join queries are for relational tables. Not only for select, but update statements. For example updating rows in one or more tables, depending on values in one or more other tables within a query. Here is an example of doing 2 things within a single query (picked in some code of mine in my job): $q = "UPDATE ".$b[0]."._KEYWORD k JOIN ".$b[0].".GENERAL_LIST l USING(ID_KEYWORD) SET l.KEYWORD=CONCAT(l.KEYWORD, '_old'), l.ACTIF=0,"; $q .= " k.KEYWORD = CONCAT(k.KEYWORD, '_old'), k.ACTIF=0 WHERE ID_GENERAL=".$_POST["kw"]; That'll change 2 fields in one table and 2 others in another depending on the field value of the first. I had to do with a table structure that I hadn't chosen 🙂. The only tutorial I know in the English language is the MySQL reference manual. I will add that in a good data model, joins are necessary. — gisele
add a comment
0
Answered
Updated

If you want unlimited levels you could look at using a recursive solution. This would allow for scenarios like:

Level
  level
  level
  level
   level
   level
  level
    level
      level
  level

Using one of the solutions provided here you would be limited to:

Level
  level
  level
level
  level
level

Recursive solutions can however become resource-intensive (unless designed properly).

add a comment
0