Category Hierarchy (Php/Mysql)

Category Hierarchy (PHP/MySQL)

When using an adjacency list model, you can generate the structure in one pass.

Taken from One Pass Parent-Child Array Structure (Sep 2007; by Nate Weiner):

$refs = array();
$list = array();

$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";

/** @var $pdo \PDO */
$result = $pdo->query($sql);

foreach ($result as $row)
{
    $ref = & $refs[$row['item_id']];

    $ref['parent_id'] = $row['parent_id'];
    $ref['name']      = $row['name'];

    if ($row['parent_id'] == 0)
    {
        $list[$row['item_id']] = & $ref;
    }
    else
    {
        $refs[$row['parent_id']]['children'][$row['item_id']] = & $ref;
    }
}

From the linked article, here's a snippet to create a list for output. It is recursive, if there a children for a node, it calls itself again to build up the subtree.

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

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

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

    return $html;
}

Category Hierarchy (in order) using PHP MySQL

You want to traverse the tree using SQL? That is not possible with the adjacency list model, you have to use the nested sets model. Then you can just ORDER BY left to get the whole tree in the correct order.

Category Hierarchy (PHP/MySQL) in Yii

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);

Get categories in hierarchical format from database

This was hand-written in a text editor... I don't have a dev environment handy so appologies if this is just plain wrong or contains typos.

But regardless I think the idea is clear/sound.

The $sql would be:

SELECT 
    parent.id as parentid,
    parent.[name] as parentname,
    child.[name] as childname

FROM
    category child

    left join category parent -- Can't exclude parents with no children, else they won't appear in the list!
    on child.parent = parent.id

ORDER BY
    parentid

And then the PHP would be:

function categoryLevels(){
    $bullet = "*"; // You can specify your HTML/CSS :-)
    $indent = "   "; // You can specify your HTML/CSS :-)

    $sql = "_as above_";
    $query = mysql_query($sql);
    $lastParentId = 0;
    while($row = mysql_fetch_array($query)){
        $parentId = $row['parentid']
        if ($parentId != 0) {
            if ($parentId != $lastParentId) {
                echo $bullet . $row['parentname']
                $lastParentId = $parentId;
            }
            if (!is_null($row['childname']) { // this line is psuedo-code! - don't know the php equiv for is_null, sorry.
                echo $indent . $bullet . $row['childname'];
            }
        }
    }
}

The gist of this is basically:

Get a result set of all categories (excluding those with no parents), sorted by the parent to which they belong
Iterate that result set, and for each change in parent, output a "header" with the parent name.
Output the child.

Edited this to be less complex (and faster). Now the SQL simply excludes the parent rows altogether, rather than excluding them with the IF statement in the PHP.

How to Create Multi level category hierarchy ( Category Tree ) - codeigniter

The biggest problem in your code was that you were overwriting $rows inside your foreach loop.

Additionally, with a recursive solution, like you've gone for, it's important to keep track of what's been returned from the inner calls to the function/method.

Also, I've added an order by, to make sure the root category appears first.

protected function getCategoryTree($level = 0, $prefix = '') {
    $rows = $this->db
        ->select('id,parent_id,name')
        ->where('parent_id', $level)
        ->order_by('id','asc')
        ->get('categories')
        ->result();

    $category = '';
    if (count($rows) > 0) {
        foreach ($rows as $row) {
            $category .= $prefix . $row->name . "\n";
            // Append subcategories
            $category .= $this->getCategoryTree($row->id, $prefix . '-');
        }
    }
    return $category;
}

public function printCategoryTree() {
    echo $this->getCategoryTree();
}