CODEWITHSUNDEEP
phpsqlalgorithmtreeview
SmootQ
SmootQ

Reputation: 2122

Algorithm for listing a tree hierarchy for categories in the same table

I have a SQL table tbl_categories with these fields: 

id , parent , title

for instance, the table may contain this information:

id   parent  title
1     0      the main item
2     1      first sub item
3     1      second sub item
4     2      first sub sub item 
5     3      second sub sub item

for example: 1 is the top category, 2 and 3 are children of 1, 4 is child of 2 and 5 is child of 3.

I want to list this information like a tree structure using PHP, like this:

- 1. the main item
 -- 2.first sub item
  ---4.first sub sub item
 -- 3. second sub item
  ---5.second sub sub item

and consider to add the "-"'s according to the level of the item in the tree.

So the question is: what is an appropriate algorithm for this task?

Upvotes: 2

Views: 1416

Answers (2)

Jakob Egger
Jakob Egger

Reputation: 12041

I'm assuming you use MySQL:

<?php
// connect to the database
$dbh = new PDO("mysql:host=127.0.0.1;port=3306;dbname=test", "root", "");

// prepare a statement that we will reuse
$sth = $dbh->prepare("SELECT * FROM tbl_categories WHERE parent = ?");

// this function will recursively print all children of a given row
// $level marks how much indentation to use
function print_children_of_id( $id, $level ) {
    global $sth;
    // execute the prepared statement with the given $id and fetch all rows
    $sth->execute(array($id));
    $rows = $sth->fetchAll();

    foreach($rows as $row)
    {
        // print the leading indentation
        echo str_repeat(" ", $level) . str_repeat("-", $level) . " ";
        // print the title, making sure we escape special characters
        echo htmlspecialchars($row['title']) . "\n";
        // recursively print all the children
        print_children_of_id($row['id'], $level+1);
    }
}

// now print the root node and all its children
echo "<pre>";
print_children_of_id( 0, 1 );
echo "</pre>";
?>

Upvotes: 3

xQbert
xQbert

Reputation: 35343

What database engine are you using?

Oracle has a feature built in called connect-by-pior. you're after something similar in the database engine your using...

if mySQL this may help --> http://sujay-koduri.blogspot.com/2007/01/prior-connect-in-mysql.html

Upvotes: 1

Related Questions