Creating a category system with multiple sub categories

  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

Hi, i am trying to create a create a category system with multiple sub categories. Anyone got any idea how to go about this? I would like the sub-categories to be able to have sub-categories themselves. But i will no know how dfeep these are to go. anyone got any advice
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

Hi,
you could make 2 tables

CATEGORY and SUB_CATEGORY

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

The second holds (unique) couples of associations ID_CATEGORY/ID_SUB_CATEGORY.
A category could in in ID_CATEGORY, in all the rows showing her "daughters" and also in ID_SUB_CATEGORY in the rows showing her "mothers"

am I clear?
  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

hmmm sorry don't quite follow.
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

CATEGORY
ID_CATEGORY
NAME_CATEGORY
...

RELATION
ID_CATEGORY
ID_SUB_CATERORY

ALL the category 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 :
Code: [ Select ]
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 :

Code: [ Select ]
SELECT c.ID_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE ID_SUB_CATEGORY=CAT_WHAT_MOTHER_CATEGORY_ARE_SEARCHED

ask else question if you want
  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

Hi, 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
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

HI,
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):
Code: [ Select ]
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
  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

right, see this is way over my head (at the moment) i dont understand anything about joins. Do you think you could point me to a good tutorial?

Thanks for all this help, by the way

EDIT**

I had a look on 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 basicall what they do?
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

I will do a quick reply, before I can get more time (my job day is about to finish :-) )
in fact join query are for relationnal tables.
Not only for select but update stament.(for example updating rows in one or more tables, depending on values in one ore 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):
PHP Code: [ Select ]
//..
 
$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"];
 
 
  1. //..
  2.  
  3. $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,";
  4.  
  5.       $q .= " k.KEYWORD = CONCAT(k.KEYWORD, '_old'), k.ACTIF=0 WHERE ID_GENERAL=".$_POST["kw"];
  6.  
  7.  

That'll change 2 fields in a table and 2 others in the others depending on a field value of the first.(I had to do with a table structure that I hadn't chosen :-) ).

The only tuto I know in english language is the reference manual :
http://dev.mysql.com/doc/refman/5.0/en/select.html sorry.
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

I will add :
in a good data model, joins are necessary.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

If you want unlimited levels you could look at using a recursive solution

this would allow for scenarios like
Code: [ Select ]
Level
 level
 level
 level
  level
  level
 level
  level
   level
 level
  1. Level
  2.  level
  3.  level
  4.  level
  5.   level
  6.   level
  7.  level
  8.   level
  9.    level
  10.  level

Using the solution above you would be limited to
Code: [ Select ]
Level
 level
 level
level
 level
level
  1. Level
  2.  level
  3.  level
  4. level
  5.  level
  6. level


Recursive solutions can how ever become resource intensive (unless designed properly)
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

Hi RabidDog,

I'm agree with you.
That was just the sql solution, wich can be taken in a global applicative solution, depending on what he needs as an approach vertical/horizontal/both

horizontal :
Code: [ Select ]
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:
PHP Code: [ Select ]
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);
      }
   }
}
 
  1. function single_genealogy($category, $level = 0)
  2. {
  3.    $q = "SELECT ID_SUB_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE c.ID_CATEGORY=".$category;
  4.    $r = mysql_query($q); //or die/mail/echo mysql_error()
  5.    if(mysql_num_rows($r))
  6.    {
  7.       $level++;
  8.       while($d = mysql_fetch_row($q))
  9.       {
  10.          //displaying current node : $d[1]...
  11.          //recursive call :
  12.          single_genealogy($d[0], $level);
  13.       }
  14.    }
  15. }
  16.  

for a given category, that'll show :
Code: [ Select ]
level(<=given one)
  level
   level
   level
  level
  level
  1. level(<=given one)
  2.   level
  3.    level
  4.    level
  5.   level
  6.   level

Very important, a category musn't be twice in a single genealogical tree otherwise it'll turn the recursive solution into a perpetual one ! :-)
  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

Im not getting this at all, i think i should go away and do some serious reading. Thanks for all the help though. At least its put me in the right direction
  • fluxstream
  • Born
  • Born
  • fluxstream
  • Posts: 1
  • Loc: Australia

Post 3+ Months Ago

I think this has gone a bit fast for vegancoder. and thats because theres 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 parent categoryID of 0. the reason ID's were so important was so that multiple subcategories could use the same name without confusion.(important for different products subcategorised by brand , 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.
  • vegancoder
  • Graduate
  • Graduate
  • vegancoder
  • Posts: 176

Post 3+ Months Ago

No i undersatnd how to add, edit and remove data from tables, and i understand how ids and keys work. Its just getting my head round all the join stuff.

Post Information

  • Total Posts in this topic: 14 posts
  • Users browsing this forum: No registered users and 67 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.