get category tree with name and id of parent category

Question

I have database table categories with id, name, parent

I currently create navigation bar with:

$rsCategories = mysql_query("SELECT * FROM categories WHERE hide = '0' ORDER BY parent, id");

$arrayCategories = array();

while($row = mysql_fetch_assoc($rsCategories)){ 
    $arrayCategories[$row['id']] = array("parent" => $row['parent'], "name" => $row['name']);   
}

function createTree($array, $currentParent, $currLevel = 0, $prevLevel = -1) {

    foreach ($array as $categoryId => $category) {

        if ($currentParent == $category['parent']) {                        

            if ($currLevel > $prevLevel) echo " <ul> "; 

            if ($currLevel == $prevLevel) echo " </li> ";

            echo '<li id="'.$categoryId.'"><a href="/categorie/'. strtolower(str_replace(" ", '_', $category['name'])) .'-'. $categoryId .'/">'. $category['name'] .'</a>';

            if ($currLevel > $prevLevel) { $prevLevel = $currLevel; }

            $currLevel++; 

            createTree ($array, $categoryId, $currLevel, $prevLevel);

            $currLevel--;               
        }   

    }

    if ($currLevel == $prevLevel) echo " </li>  </ul> ";

}`

I need to get full link on third category level like

`href="/category_1/subcaegory_4/ssubcategory_2/"`

For second category level the link should be

`href="/category_1/subcategory_4/"`

and for first level

`href="/category_1/"`

I have tried with `LEFT OUTER JOIN`

`$rsCategories = mysql_query("SELECT *, c.name AS name, c.parent AS parent, c.id AS id, c1.name AS c1_name FROM categories c LEFT OUTER JOIN categories c1 ON c1.id = c.parent WHERE c.hide = '0' AND c1.hide = '0' ORDER BY c.parent, c.id");

but is not work...

Thanks!

Answer 1

You'll need a structure like this (I wont detail the exact specifications, but what is needed for recursion):

table_categories:
    - record_id
    - parent_id
    - category_name


 +-----------+-----------+---------------------+
 | record_id | parent_id | category_name       |
 +-----------+-----------+---------------------+
 | 1         | NULL      | Parent Category 1   |
 | 2         | NULL      | Parent Category 2   |
 | 3         | 1         | Child Category 1    |
 | 4         | 3         | Subchild Category 1 |
 | 5         | 2         | Child Category 2    |
 +-----------+-----------+---------------------+

Once you have your database table setup with the record_id and parent_id fields set up, use this code to get the tree structure:

// Create a new class to manage structure generation
class treeStructure
{

    // Create a property to store the database records
    private $structureData;


    // This function will retrieve all records from the database
    // We can use PHP to manage the database, rather than relying 
    // on recursive SQL queries
    function getRecords() {

        // Generate a db connection
        try {
            $db = new PDO($dsn, $username, $password);
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            echo $e->getMessage();
        }

        // Retrieve all the records from the database
        $result = $db->prepare("SELECT record_id, parent_id, category_name FROM table_categories");
        $result->execute();

        // Save the data array to an object property
        $this->structureData = $result->fetchAll(PDO::FETCH_ASSOC);


        // Return a default true value
        return true;
    }


    // This function will count the number of children for any specified
    // parent
    function countChildren($parentId = 0){

        // Set a default value to return. By default
        // Say this element has 0 children
        $childCount = 0;

        // Loop through each of the results
        foreach($this->structureData as $row){

            // If the current records parent ID is the same
            // as the supplied parent ID, add 1 to the child 
            // count
            if((int)$row['parent_id']===(int)$parentId) {
                $childCount += 1;
            }
        }

        // Return the number of children
        return $childCount;
    }


    // This method will generate our HTML tree structure
    function generateStructure($parentId = 0) {

        // Define a default value for $html
        $html = '';

        // Loop through the results
        foreach($this->structureData as $row){

            // If the current records parent ID equals the requested
            // parent ID...
            if((int)$row['parent_id']==(int)$parentId){

                // Add an <li> element
                $html .= '<li>' . $row['category_name'];


                // Before closing the <li>, check for any children
                // If this record does have children, generate a new 
                // <ul> element, and recall this function with a new
                // parent ID
                if($this->countChildren($row['record_id']>0)){
                    $html .= '<ul>';
                    $html .= $this->generateStructure($row['record_id']);
                    $html .= '</ul>';
                }

                // Now close the <li>
                $html .= '</li>';
            }
        }


        // Return the generated HTML
        return $html;
    }
}


$structureObj = new treeStructure();

$structureObj->getRecords();
$html = '<ul>' . $structureObj->generateStructure() . '</ul>';

echo $html;

This is a basic overview of what should happen:

1. Generate a new structure object
2. Get ALL records of the structure from the database, and assign to an object property
3. Run the generateStructure() method, passing the $parentId to get records of
4. generateStructure() then loops through all the records and looks for records with parent_id of the id passed to generateStructure()
5. Once the current category has been added to the structure, generateStructure() will then call the method countChildren(). If countChildren() returns an int greater than 0, the current record has children, so we generate another menu element
6. generateStructure() then returns the generated HTML

I haven't debugged the above code, there may be a few syntax errors. But, it should output html similar to this:

<ul>
    <li>Parent Category 1
        <ul>
            <li>Child Category 1
                <ul>
                    <li>Subchild Category 1</li>
                </ul>
            </li>
        </ul>
    </li>

    <li>Parent Category 2
        <ul>
            <li>Child Category 2</li>
        </ul>
    </li>
</ul>