is it possible to build a collapsible treeview list using PHP only (no JS)

Question

I have two functions that together build a treeview list on my website. It's based on recursion and allows to build a treeview with an unlimited number of nodes.

But I can't make it collapsible. For example: script should determine whether $_GET['node'] == $node_id or not, and if it is, display (unroll) block element and all it's parents. So, I need to pass that "displaying" parameter on the top, to the root.

The thing is in $display and $display2 vars.

I have a classic db table with three columns (id, parent_id, name). Root nodes have not parent_id field filled.
hrefs links to just a GET parameters. GET parameter means node number.

I just need this collapsing technique working based on what node i was selected.

UPDATE: Ok, there is a complete and purified info. i created a php file, working with a database, containing only one table. just for understanding the issue:

1 I use sqlite3 database format. this is the DB dump:


        # sqlite3 catalog.sqlite .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE groups(id INTEGER PRIMARY KEY NOT NULL, name TEXT, parent_id INTEGER);
    INSERT INTO "groups" VALUES(1,'root1','');
    INSERT INTO "groups" VALUES(2,'root2','');
    INSERT INTO "groups" VALUES(3,'root3','');
    INSERT INTO "groups" VALUES(4,'root4','');
    INSERT INTO "groups" VALUES(5,'sub1',1);
    INSERT INTO "groups" VALUES(6,'sub3',3);
    INSERT INTO "groups" VALUES(7,'subsub1',5);
    INSERT INTO "groups" VALUES(8,'subsubsub1',7);
    INSERT INTO "groups" VALUES(9,'subb1',1);
    COMMIT;

2 this is an PHP file, dealing with the databse.

<?php

$db = new SQLite3('catalog.sqlite');

function build_catalog($db){ //build roots and diggs for childnodes for every root in a loop
    //$content_root="<ul id='collapsedlist'>";
    $content_root = '';
    $roots = $db->query('SELECT * from groups WHERE parent_id="" OR parent_id is null');
    while($root = $roots->fetchArray()){
        list ($content,$display)=get_children_of_node($db,$root['id']); 
        $content_root .= "<li id='".$root['id']."' ><a href='/?node=".$root['id']."'>".$root['name']."</a>";
        $content_root .= $content;
        $content_root .= "</li>\n";
    }
    $content_root = "<ul id='collapsedlist'>".$content_root."</ul>\n";

    return $content_root;
}

function get_children_of_node($db,$node_id){
    if(!isset($content)) $content = '';
    $display = (isset($_GET['node']) && $_GET['node'] == $node_id)? "style='display:block'" : "style='display:none'";
    $query = $db->querySingle('SELECT count(*) from groups WHERE parent_id='.$node_id);
    if ($query > 0){
        //$content .= "<ul class='subcategories'>\n";
        $children = $db->query('SELECT * from groups WHERE parent_id =\''.$node_id.'\'');
        while ($child = $children->fetchArray()){
            list($content2,$display)=get_children_of_node($db,$child['id']);
            $content .= "<li id='".$child['id']."' ".$display.">";
            $content .= "<a href='/?node=".$child['id']."'>".$child['name']."</a>";
            $content .= $content2;
            $content .= "</li>\n";
        }
        $content = "<ul class='subcategories' ".$display.">".$content."</ul>\n";
    }
    return array($content,$display);
}


?>

Here the php file ends with a pure HTML shoving above. i divided it one from another here, hence the editor cant parse HTML+PHP syntax at once. but it is the same index.php file. HTML part:

    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Collapsible Nested List</title>
    </head>
    <body>
        <div id="sidebar">
            <?=build_catalog($db);?>
        </div>
    </body>
    </html>

You've mentioned your parent column is called `parent_id`, but have used `parend_id` throughout the script. Are you sure that's right? — halfer, Apr 23 '12 at 20:44
yes, it's ok. it was a mistake when i created the table in DB. anyway it's a draft. the logic is working fine, but i need to pass the info about display or not to display the block to upper levels to make in collapsible dinamically. now, all the nodes are displayed as default, so i have an uncollapsed list of all subcategories. — remort, Apr 24 '12 at 05:14
i can provide more info. a table structure, screenshots and so on. tell me if it's nessesary, cause i know, recursion is a difficult for understand thing )) — remort, Apr 24 '12 at 05:18
Looks like the kind of thing that one would need to run personally to have a dig about in. Maybe if you could put up a pastie.org containing a `CREATE TABLE` and a bunch of `INSERT` demo rows, people will have something to work with. (That said, what class type is `$db`? This won't run on its own). — halfer, Apr 24 '12 at 09:49
I remade my entire post. Simplified, clarified and purified the issue. Database included. If you have a PHP up and running with sqlite3 module compiled in, you can rapidly repeat the issue on your server. — remort, Apr 24 '12 at 12:47
A well-formulated question! I might not have time to run this myself, but if it's still unanswered in a couple of days, ping me here and I will add a bounty for you (the platform doesn't permit it to be added straight away). — halfer, Apr 24 '12 at 13:02

score 3 · Accepted Answer · answered Apr 24 '12 at 15:17

Here is a quick solution to your problem, keeping the same HTML just replace your PHP with the following code.

function get_children($db, $parent_id) {
    $res = $db->query("SELECT * FROM groups WHERE parent_id='$parent_id'");
    if (!$res) return array();

    $out = array();
    while ($row = $res->fetchArray(SQLITE3_ASSOC)) $out[$row['id']] = $row;
    return $out;
}

function get_parent_id($db, $node_id) {
    return $db->querySingle("SELECT parent_id FROM groups WHERE id='$node_id'");
}

function get_menu($db, $node_id) {
    $menu = get_children($db, $node_id);
    while (($parent_id = get_parent_id($db, $node_id)) !== null) {
        $temp = get_children($db, $parent_id);
        $temp[$node_id]['children'] = $menu;
        $menu = $temp;
        $node_id = $parent_id;
    }
    return $menu;
}

function build_html(array $menu) {
    $str = '';
    foreach ($menu as $id => $item) {
        $str .= sprintf('<li><a href="?node=%s">%s</a></li>', $id, $item['name']);
        if (isset($item['children']))
            $str .= build_html($item['children']);
    }
    return "<ul>$str</ul>";
}

function build_catalog($db) {
    $menu = get_menu($db, isset($_GET['node']) ? intval($_GET['node']) : '');
    return build_html($menu);
}

This code could be really optimized if your "groups" table is quite small. An idea would be to get all records in a $groups array and to build a parent_id index. The building of the catalog would then be much more easy.

ah, shits ! it works just great ! thank you very very much, you are a real pro )) tomorow i'll get into your code more closely... Please, tell me what did you mean talking about 'optimize logic if "groups" is small' ? this table could be huge, and contain many children, for example a catalogue of web-shop with many categories. — remort, Apr 24 '12 at 16:49
The idea is to minimize the number of queries made to the database. In this example and for a node of depth 3 the code would make 2 * 4 queries to the database, on each page view and, IMHO, this is bad. I see at least two ways of optimizing this. 1) Build a tree with left, right and depth columns (like Doctrine Tree Behavior, complex query & insert logic). 2) Fetch the whole table in an array (eats memory). Categories for a web shop are not so "huge". Let's say you have 100 categories, this makes quite a nice catalog already... — smrtl, Apr 25 '12 at 10:19
thanks a lot, smrtl. i've got all the info i need )) now the question seems to be clear for me ! — remort, Apr 28 '12 at 06:59

is it possible to build a collapsible treeview list using PHP only (no JS)

1 Answers1