Categories tree view

[ganeshcp]

I have written a script where you can create categories and subcategories. Each category has the variables :
cat_id, sub_cat_id

Now I'm really confused as to how to display categories and sub-categories in a neat tree view format? I've checked prev posts on this topic and have found some really puzzling code !

G

[Kudose]

$query = mysql_query("SELECT cat_id FROM table") or die(mysql_error());
while($row = mysql_fetch_array($query)){
echo $row['cat_id'];
$sql = mysql_query("SELECT sub_cat_id FROM table") or die(mysql_error());
while($sub = mysql_fetch_array($sql)){
echo "  ".$sub['sub_cat_id'];
   }//end second while
}//end first while

That, in theory, should work.

[Tekime]

If you want tree-like structures with infinite depth, you need a recursion function. You also want to track the parent of each category, rather than all subcategories. I find it easier to refer to each point on the tree as a node, and set up a simple table like:

[FONT=courier new]nodeId | nodeParentId | nodeName[/FONT]

By identifying the parent of every node (category), you can start at any given node on the tree and walk through each child.

It's actually really easy to do this with an RDBMS and a single function.

function display_branch($node)
{    

    static $tab;

// Get all children of this node
$sql = "SELECT * FROM tblNodes WHERE nodeParentId = " . $node;

if($rs = $db->execute($sql)) // Get recordset
{   
    $oldtab = $tab; // Backup tab
    $tab .= ' '; // Increase tab
    while(!$rs->EOF)
    {
        echo $tab . $rs->fields['nodeName'] . '<br />'; // Output node name
        node_tree($rs->fields['nodeId']); // Recurse with current node
        $rs->MoveNext(); // Move to next record
    }
    $tab = $oldtab; // Restore tab
}

return true;
}

I haven't tested the above function but the idea is very similar. I'm not promising this is the most efficient method, but a good starting point. I use a custom DB and template class consistently so my DB nomenclature might be foreign, and you might not want to echo the output directly from this function.

The general idea is:

1. Pass the function a node
2. Get each child of that node
3. Output each child node, passing each node back to the function to repeat the process

The $tab var just keeps track of your current tab indent. If this is just confusing you even more, I'll try to clarify when it isn't 5am. 😉

(Now to bed for three hours...)

[ganeshcp]

I modified the code to suit my DB class. But I don't know what's the MoveNext function, can you plz explain that?

function display_branch($id) 
{     

    global $tableprefix,$site_db;
     static $tab; 
    // Get all children of this node 
    $que = "SELECT * FROM ".$tableprefix."_categories WHERE sub_cat= $id"; 

if($res = $site_db->query($que)) // Get recordset 
{    
    $oldtab = $tab; // Backup tab 
    $tab .= ' '; // Increase tab 
    while($row = $site_db->fetch_array($res)) 
    { 
        echo $tab . $row[cat_name] . '<br />'; // Output node name 
        display_branch($row[cat_id]); // Recurse with current node 
        $rs->MoveNext(); // Move to next record 
    } 
    $tab = $oldtab; // Restore tab 
} 

return true; 
}
 

[Tekime]

You can probably delete that line altogether. It's just a seeking method of sorts -- I'm assuming your fetch_array method is passing back the results of mysql_fetch_array()?

[ganeshcp]

Thanks. Its working fine now 😃
G

[Tekime]

Great, good to hear. 🙂