Categories and Subcategories, please help

[eluser]satanrulehis[/eluser]
I have read a great article from http://sqllessons.com/categories.html about how to get all category items in 1 SQL.

The table looks like this

Code:

create table categories
( id       integer     not null  primary key
, name     varchar(37) not null
, parentid integer     null
, foreign key parentid_fk (parentid)
      references categories (id)
);

Add some values to it

Code:

INSERT INTO `categories` (`id`, `name`, `parentid`) VALUES
(1, 'animal', NULL),
(2, 'vegetable', NULL),
(3, 'mineral', NULL),
(4, 'doggie', 1),
(5, 'kittie', 1),
(6, 'horsie', 1),
(7, 'gerbil', 1),
(8, 'birdie', 1),
(9, 'carrot', 2),
(10, 'tomato', 2),
(11, 'potato', 2),
(12, 'celery', 2),
(13, 'rutabaga', 2),
(14, 'quartz', 3),
(15, 'feldspar', 3),
(16, 'silica', 3),
(17, 'gypsum', 3),
(18, 'hunting', 4),
(19, 'companion', 4),
(20, 'herding', 4),
(21, 'setter', 18),
(22, 'pointer', 18),
(23, 'terrier', 18),
(24, 'poodle', 19),
(25, 'chihuahua', 19),
(26, 'shepherd', 20),
(27, 'collie', 20);

Now the magic which SQL statement

Code:

select root.name  as root_name
     , down1.name as down1_name
     , down2.name as down2_name
     , down3.name as down3_name
  from categories as root
left outer
  join categories as down1
    on down1.parentid = root.id
left outer
  join categories as down2
    on down2.parentid = down1.id
left outer
  join categories as down3
    on down3.parentid = down2.id
where root.parentid is null
order
    by root_name
     , down1_name
     , down2_name
     , down3_name

Then we have a result

root_name down1_name down2_name down3_name
animal birdie NULL NULL
animal doggie companion chihuahua
animal doggie companion poodle
animal doggie herding collie
animal doggie herding shepherd
animal doggie hunting pointer
animal doggie hunting setter
animal doggie hunting terrier
animal gerbil NULL NULL
animal horsie NULL NULL
animal kittie NULL NULL
mineral feldspar NULL NULL
mineral gypsum NULL NULL
mineral quartz NULL NULL
mineral silica NULL NULL
vegetable carrot NULL NULL
vegetable celery NULL NULL
vegetable potato NULL NULL
vegetable rutabaga NULL NULL
vegetable tomato NULL NULL

The problem I have : how to display them in HTML in style ULs?

Example:
*animal
obirdie
odoggie
+companion
#chihuahua
#poodle
+herding
#collie


Can anyone help me? I use CodeIgniter certainly
Thanks alot for reading

[eluser]jedd[/eluser]
How would you do this in conventional PHP?

The differences to get it going under CI would be pretty trivial, but if you show us your existing code it should be easy to point them out.

[eluser]andrewtheandroid[/eluser]
Have you read this post?
"Want to show categories with subcategories"

If you use one table as phil suggested the sql is simpler as to find a root you have parent id = 0. Then you could write a recursive function to create sub-catagories based on your result array. You could use the array_map and make your callback recursive.

Code:

// Get root categories
$roots = select .. WHERE parent = 0;

function make_children_ul($child_node)
{
   $list_str = ''; // Your output string
   // Check if child has children with sql
   $children = ;// "SELECT * FROM categories WHERE parent = '{$child_node['id']}'"
   if(count($children)>0)
      {
          $list_str .= "<div class=\"parent\">{$child_node['name']}";
          $list_str .= '<div class="children"><ul>';
          foreach($children as $child)
          {
             $list_str .= make_children_ul($child);
          }
          $list_str .= "</ul></div></div>";
      }
   else
       $list_str .= "<div class=\"child\">{$child_node['name']}</div>";

   return $list_str;
}


// Then use call back for root categories
array_map('make_children_ul',$roots)

If you wanted to use your approach then maybe you could for each row
your results have the structure

Code:

// From your sql result your rows should have the structure
$catagory = array('root_name'=>'animal','down1'=>'birdie','down2'=>'NULL','down3'=>'NULL');

// Your final array of categories
$cats = array();

// Go through each one and add to final list
foreach ($catagory as cat)
{
    $root = $cat['root_name'];
    $down1 = $cat['down1'];
    $down2 = $cat['down2'];
    $down3 = $cat['down3'];
    // Can do the above in one line but broken down for readability
    $cats[$root][$down1][$down2] = $down3;
}

// You could format them using nested foreach loops
foreach ($cats[$root] as $root)
{
    // show name
    if ($root['down1'] != NULL)
    { // continue loop
       foreach // etc...
    }
}

The above is just pseudo code and has not been tested just to give you the algorithm.