Category Hierarchy (PHP/MySQL)

by

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

Related Question:

More Related Contents:

  1. Comma separated numeric values – PHP CodeIgniter
  2. PHP PDO and Mysql [closed]
  3. Parse error: syntax error, unexpected T_STRING in C:\wamp\www\b_php\project\login_save.php on line 14 [closed]
  4. The requested URL was not found on this server.
  5. Fatal error: Call to a member function bind_param() on boolean [duplicate]
  6. How to create a secure mysql prepared statement in php?
  7. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  8. strange character encoding of stored data , old script is showing them fine new one doesn’t
  9. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  10. How to loop through a mysql result set
  11. Cannot connect to MySQL 4.1+ using old authentication
  12. Doctrine 2 and Many-to-many link table with an extra field
  13. Post form and update multiple rows with mysql
  14. MySQL/PHP Error:[2002] Only one usage of each socket address (protocol/network address/port) is normally permitted
  15. How to use multiple databases dynamically for one model in CakePHP
  16. MySQL retrieve variable from Stored Procedure in PHP PDO
  17. How to run the PHP script at scheduled time
  18. How to handle error for duplicate entries?
  19. How to handle PDO exceptions [duplicate]
  20. MySql PHP select count of distinct values from comma separated data (tags)
  21. mysql_insert_id() returns 0
  22. php artisan migrate throwing [PDO Exception] Could not find driver – Using Laravel
  23. Change name of Laravel’s created_at and updated_at
  24. How do I get database column names in Laravel?
  25. PDO and MySQL Fulltext searches
  26. Eloquent many-to-many-to-many – how to load distant relation easily
  27. How to avoid “Using temporary” in many-to-many queries?
  28. How do you make good use of multicore CPUs in your PHP/MySQL applications? [closed]
  29. How to get a list of databases?
  30. How to sort MYSQL fulltext search results by relevancy

Leave a Comment