Category Hierarchy (PHP/MySQL) in Yii

Question

I found this here: Category Hierarchy (PHP/MySQL)

And i want to display that code, but it is not working correctly.

I got the following Hierarchy:

-Airsoft
--Scopes

That's all. But the code is displaying:

-Airsoft
--Scopes (So far so good)
-Scopes <--- this one should not be here!

Here's the code:

public static function producten(){

        $connection=Yii::app()->db;   // assuming you have configured a "db" connection
        $sql = 'SELECT id, parent, naam FROM categories ORDER BY naam';
        $command=$connection->createCommand($sql);
        $dataReader=$command->query();

        $refs = array();

        foreach ($dataReader as $row)
        {
            $ref = & $refs[$row['id']];

            $ref['parent'] = $row['parent'];
            $ref['naam']   = $row['naam'];

            if ($row['parent'] == NULL)
            {
                $list[$row['id']] = & $ref;
            }
            else
            {
                $refs[$row['parent']]['children'][$row['id']] = & $ref;
            }
        }

        function toUL(array $array)
        {
            $html = '<ul>' . PHP_EOL;

            foreach ($array as $value)
            {
                $html .= '<li>' . $value['naam'];
                if (!empty($value['children']))
                {
                    $html .= toUL($value['children']);
                }
                $html .= '</li>' . PHP_EOL;
            }

            $html .= '</ul>' . PHP_EOL;

            return $html;
        }
        print_r($refs);
        echo toUL($refs);
    }

The print_r() in there is displaying:

Array ( [1] => Array ( [parent] => [naam] => Airsoft [children] => Array ( [2] => Array ( [parent] => 1 [naam] => Scopes ) ) ) [2] => Array ( [parent] => 1 [naam] => Scopes ) )

Can somebody figure out what's wrong with the code and help me please?

Well the problem is, that you don't check if child already exists for that parent.. — Naruto, Nov 05 '14 at 12:48
Not tested, but something like: if(!(in_array($row['parent'], $refs){ $refs[$row['parent']]['children'][$row['id']] = & $ref; } — Naruto, Nov 05 '14 at 13:04

score 1 · Accepted Answer · answered Nov 06 '14 at 13:46

You can try this:

$connection=Yii::app()->db;   // assuming you have configured a "db" connection
$sql = 'SELECT id, parent, naam FROM categories ORDER BY naam';
$command=$connection->createCommand($sql);
$dataReader=$command->queryAll();


function createList($elements, $parentId = 0) {
        $branch = array();

        foreach ($elements as $element) {
            if ($element['parent'] == $parentId) {
                $children = createList($elements, $element['id']);
                if ($children) {
                    $element['children'] = $children;
                }
                $branch[] = $element;
            }
        }

        return $branch;
    }

    $list = createList($dataReader);
    CVarDumper::dump($list, 5678, true);

Please note that this is given from http://stackoverflow.com/questions/8587341/recursive-function-to-generate-multidimensional-array-from-database-result/ but matches your problem. — Ali MasudianPour, Nov 06 '14 at 13:49

Category Hierarchy (PHP/MySQL) in Yii

1 Answers1

Linked