Categories and Subcategories problem with MySQL?

[eluser]dojolab[/eluser]
Hello,

I have a logical problem with MySQL (store procedure or not), because I want to extract (and fetch) ALL categories and relative subcategories.

I have a tree table in my mysql database, for example:

ID NAME PARENT
1 Toys 0
2 Cars 1
3 Racers' Cars 2

ecc...

I want to get ALL records with only MySQL query and return tree into recordset:

Toys
-- Cars
---- Racers' Cars

ecc...

I've try that:

Code:

SELECT DISTINCT
t2.category_name AS sub_category_name, t2.category_permalink AS sub_category_permalink, t1.category_name AS category_name, t1.category_permalink AS category_permalink
FROM categories t1
LEFT OUTER JOIN categories t2 ON t1.category_parent = t2.category_id
WHERE t1.category_visible > 0
GROUP BY t1.category_parent
ORDER BY t1.category_name ASC

But doesn't work correctly.
Is there a solution for my problem?

Thanks
M,

[eluser]dojolab[/eluser]
this is my query:

Code:

SELECT DISTINCT t1.category_name AS category_name, t2.category_name AS sub_category_name FROM categories t1 LEFT JOIN categories t2 ON (t1.category_id = t2.category_parent) WHERE t1.category_visible > 0 AND t1.category_parent = 0 AND t1.category_language = 'it' ORDER BY t1.category_name, t2.category_name

and return it:

Code:

Array
(
    [0] => stdClass Object
        (
            [category_name] => Prima Categoria
            [sub_category_name] => Seconda sub categoria
        )

    [1] => stdClass Object
        (
            [category_name] => Prima Categoria
            [sub_category_name] => SUB Prima Categoria
        )

    [2] => stdClass Object
        (
            [category_name] => Seconda Categoria
            [sub_category_name] =>
        )

)

[eluser]skunkbad[/eluser]
[quote author="dojoLAB" date="1276220912"]this is my query:

Code:

SELECT DISTINCT t1.category_name AS category_name, t2.category_name AS sub_category_name FROM categories t1 LEFT JOIN categories t2 ON (t1.category_id = t2.category_parent) WHERE t1.category_visible > 0 AND t1.category_parent = 0 AND t1.category_language = 'it' ORDER BY t1.category_name, t2.category_name

and return it:

Code:

Array
(
    [0] => stdClass Object
        (
            [category_name] => Prima Categoria
            [sub_category_name] => Seconda sub categoria
        )

    [1] => stdClass Object
        (
            [category_name] => Prima Categoria
            [sub_category_name] => SUB Prima Categoria
        )

    [2] => stdClass Object
        (
            [category_name] => Seconda Categoria
            [sub_category_name] =>
        )

)

[/quote]

So then it worked?

[eluser]dojolab[/eluser]
Yes, but return max 2 levels of categories, for example:

Code:

first
- sub first
[END]
second
third
- sub third
[END]

My recursive function limit 2 level of categories.

[eluser]WanWizard[/eluser]
If you have multiple levels, you're talking about a tree structure. Which means you need a tree algorithm.
Search for nested trees, someone has already made a library to work with nested trees. It's also mentioned in the Wiki.

[eluser]dojolab[/eluser]
[quote author="WanWizard" date="1276258801"]If you have multiple levels, you're talking about a tree structure. Which means you need a tree algorithm.
Search for nested trees, someone has already made a library to work with nested trees. It's also mentioned in the Wiki.[/quote]

Mumble, thansk WanWizard I'll see into wiki for tree's library!

[eluser]skunkbad[/eluser]
In my ecommerce application, the work of building a category menu has this same task, but was achieved a different way. Instead of a complex query, all of the categories table data is returned, and then I have the task inside php of converting the query result into the category menu. Yes it does involve at least one recursive function, but it works for infinite levels of categories and subcategories. You might check it out on bitbucket.

The db query is in models/product_model and the category menu is built in views/category_menu.

[eluser]Twisted1919[/eluser]
I prefer doing this with a recursive function , because is easier . This is my function , maybe will help u :

Code:

private function _get_menu_categories($parent_id='', $indent= 0)
    {
        $indent++;
        
        $data = array();
        
        if($results = $this->model->get_dropdown_categories($parent_id))
        {
            foreach ($results as $result)
            {
                $data[] = array(
                    'category_id' => $result->category_id,
                    'name'        => str_repeat('    ', $indent) . $result->name
                );
                
                $sub = $this->_get_menu_categories($result->category_id, $indent);
                
                if ($sub)
                {
                  $data = array_merge($data, $sub);
                }
            }            
        }

        return $data;
    }

//Running $data into a foreach loop will output something like :
--CATEGORY 1
----SUBCATEGORY 1
----SUBCATEGORY 2
------SUB-SUBCATEGORY 1
--CATEGORY 2
--CATEGORY 3

The model function just retrieves the categories . very simple and fast .

I tried allot of ways to solve this kind of problem, but i am back to my old recursive function.

[eluser]dojolab[/eluser]
Recursive function is optimal way... but I don't like recursive alghoritmic solution with PHP

I've try:

Code:

function get_all($language = NULL)
    {        
        // get all parents categories.
        $sql = "SELECT
                categories.category_id, categories.category_name,
                categories.category_permalink, categories.category_parent
                FROM categories
                WHERE categories.category_visible > 0
                AND categories.category_language = " . (string) $this->db->escape($language) . "
                GROUP BY categories.category_id
                ORDER BY categories.category_parent, categories.category_name ASC";

        // execute query and count all records.
        $query = $this->db->query($sql);
        $unsorted = $query->result_array();
        
        $sorted = array();
        $count = $query->num_rows();
                        
        //sort the categories into a nested tree
        for($i = 0; $i < $count; $i++)
        {
             if(isset($unsorted[$i]))
             {
                  if($unsorted[$i]['category_parent'] == 0)
                  {
                       $category_id = $unsorted[$i]['category_id'];
                       $sorted[] = $unsorted[$i];
                       unset($unsorted[$i]);
                      
                       $x = count($unsorted);
                       for($j = 0; $j < $x; $j++)
                       {
                            if($unsorted[$j]['category_parent'] == $category_id)
                            {
                                 $sorted[] = $unsorted[$j];
                                 unset($unsorted[$j]);
                            }
                       }
                  }
             }
        }
        
        // return.
        return (array) $sorted;
    }

But returns

Code:

A PHP Error was encountered

Severity: Notice

Message: Undefined offset: 0

Filename: models/category.php

at line: for($j = 0; $j < $x; $j++)

[eluser]Twisted1919[/eluser]
I bet my recursive function is way too fast than your joined + grouped + distinct query .
Go recursive, it's not about you like it or not, it's just it's easier and faster .
If you want to get more levels you need to join tables once again for each join, joins in mysql are expensive.
Believe me, i have had fight allot with this to get my opinion about it, it's recursive, but it's fast .