PHP + MYSQL Categories > Sub Categories | Help!

[GFXUniverse]

Hi,

 

I need to create a category system using MySQL + PHP and i know how to do this

two tables in database

Category

  id | Name

 

Sub_category

id | Name | parent_category

 

and i also know how to display them, like

 

show sub categories WHERE parent _category = Category

 

The result will be

Category1

  -Sub Category1

  -Sub Category2

Category2

  -Sub Category1

  -Sub Category2

 

but

 

I need to create a database system that will grab the top level category and list any sub-category it has under it and that in turn will have to list all the categories under that subcategory. Like this

 

Category1

        Sub Category1

                Sub sub category1

                Sub sub category1

        Sub Category2

                Sub sub category1

                Sub sub category1

Category2

        Sub Category1

                Sub sub category1

                Sub sub category1

        Sub Category2

                Sub sub category1

                Sub sub category1

 

Please help me out! thanks

 

waiting 4 reply

[Buddski]

You could add a field that is a flag that tells you if the sub category is a child of another sub cateogory which, if true, will look in the sub cat table for its parent instead of the main cat table.

You follow?

[GFXUniverse]

i got you, can u please tell me the table structure! in examples plz

[laffin]

The easiest ways are pretty much simple and can only provide a number of level of categories.

And In all actuality, you are pretty close to a multilevel category system already!

Sub_category

id | Name | parent_category

 

now if you remove the category table, and rename this sub-category table as category. your set

now ya revise your category structure (with ids) so you can see a bit more visually

Category1

        Sub Category3

                Sub sub category7

                Sub sub category8

        Sub Category4

                Sub sub category9

                Sub sub category10

Category2

        Sub Category5

                Sub sub category11

                Sub sub category12

        Sub Category6

                Sub sub category13

                Sub sub category14

now your root categories don't have parents, so u leave parent as 0;

so you can traverse the tree pretty easily:

to get all root categories

SELECT * FROM category WHERE parent=0

which should return 1 & 2

than go from there changing parent.

 

[PravinS]

You can change your table structure as told by "laffin" and then you can use recursive function to retrieve the subcategories.

[laffin]

PHP Portion:

Bow that you got your mysql table all setup, next you are probably wondering, how to load and use this in php.

well there are plenty of ways, u can traverse the tree manually or use more complex coding and use a recursive function

 

in either instance, you should build a simple array of the categories

function get_categories()
{
     $category=array();
     $res=mysql_query('SELECT * FROM category');
      while($arr=mysql_fetch_assoc($res))
      {
           $category[$arr['parent']][$arr['id']=$arr['name'];
      }
      return $category;
}

which should give an array, if your example structure is used something like

$category = array(

    [0] => array(

      [1]=>Category1,

      [2]=>Category2

    )

  [1] => array (

      [3] => Sub Category3,

      [4] => Sub Category4,

  )

  [2] => array (

      [3] => Sub Category3,

      [4] => Sub Category4,

  )

...

 

myself I prefer recursion method, which is a bit more coding, but makes the traversal much simpler.

 

[GFXUniverse]

i want to create categories like this

http://3docean.net/category/

[ignace]

You want to create multi-level categories but you only foresee a 2-level category system (category, subcategory) Instead you need:

 

create table category (
  id integer not null auto_increment,
  parent_id integer,
  ..
  key category_parent_id_fk (parent_id),
  primary key (id));

 

Now you can go as deep as you want. For example:

 

insert into category values
(1, 0, 'parent category'),
(2, 1, 'sub category'),
(3, 2, 'sub sub category'),
(4, 3, 'sub sub sub category');