How to Retrieve Hierarchical Data from a MySQL Database in PHP as Nested HTML?

Category Hierarchy Retrieval in PHP

In a MySQL database, you can store hierarchical data in a table named categories with columns for category_id and parent_id. To retrieve this data in a hierarchical structure using PHP, follow these steps:

1. Fetch Data from the Database:

   Use a SQL query to fetch all categories ordered by their names:

   <code class="php">$sql = "SELECT category_id, parent_id, name FROM categories ORDER BY name";
   $result = $pdo->query($sql);</code>

2. Create Reference Array:

   Create an associative array $refs that will store references to each category. Each reference will contain parent_id and name properties. For categories with no parent (parent_id = 0), add them to the $list array.

   <code class="php">$refs = array();
   $list = array();
   foreach ($result as $row) {
       $ref = &$refs[$row['category_id']];
       $ref['parent_id'] = $row['parent_id'];
       $ref['name'] = $row['name'];
       if ($row['parent_id'] == 0) {
           $list[$row['category_id']] = &$ref;
       } else {
           $refs[$row['parent_id']]['children'][$row['category_id']] = &$ref;
       }
   }</code>

3. Generate Nested HTML List:

   Create a recursive function toUL to generate a nested HTML list using the $list array. It should check for children and call itself recursively to build subtrees.

   <code class="php">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;
   }</code>

4. Display the Hierarchy:

   Use the toUL function to generate and display the hierarchical HTML list.

You can find a helpful example on obtaining a nested HTML list from an object's array recordset in the "Related Question" section provided in the reference material.

The above is the detailed content of How to Retrieve Hierarchical Data from a MySQL Database in PHP as Nested HTML?. For more information, please follow other related articles on the PHP Chinese website!