Start Building Professional
Web Apps Today

dhtmlxtreegrid - parsing resultset to treegrid

In many cases there is a need to display a flat DB result set as treegrid.
common use case in by sql group by and first x columns should be the tree nodes.
I could not find in the documents a better way than to use the following php script to parse tree grid data in the backend:

<?php
//------------- Input ------------
$sql = "select * from treegridDB"; //any sql statement (aggregate group by is the common use case)
$rs = $conn->execute($sql); //result set retrieval
$treegrid = array("Fieldname1","Fieldname2","Fieldname3"..."Fieldnamen); //the result set field names
$treedepth = 3; // depth of the tree - number of fields to parse as tree nodes
//--------- Code -----------------
header("Content-type:text/xml");
print('<?xml version="1.0" encoding="UTF-8"?>');

$gridsize = count($treegrid);
$griddata = array(); //will hold a row data
$treesave = array_fill(0,$treedepth,""); // will hold last row tree values

print "<rows id='treegrid' open='1'>\n<row id='total'>\n<cell>Total</cell>\n"; //top level node
for ($i=0;$i<$gridsize;$i++){ print "<cell></cell>\n";}
$datarow = 0;
while (!$rs->EOF) {
    for ($i = 0; $i < $gridsize; $i++){ $griddata[$i] = $rs->fields[$treegrid[$i]]; } // fill row array from resultset
    for ($i = 0; $i < $treedepth; $i++) { //check node values to create new nodes
        if ($griddata[$i] != $treesave[$i]) {
            if ($datarow != 0) {for ($j = $i; $j < $treedepth ; $j++) {print "</row>\n";}}            
            for ($j = $i; $j < $treedepth ; $j++) {
                print ("<row id='r-".($datarow+1)."-t-".($j+1)."'>\n<cell>".$griddata[$j]."</cell>\n");
                for ($k = $treedepth ; $k < $gridsize;$k++){print "<cell></cell>\n";} //create data cells
            }
        break;
        }
    }        
    print ("<row id='".($datarow+1)."'>\n"); //create data node
    print "<cell></cell>\n";
    for ($i = $treedepth; $i < $gridsize;$i++){
        print "<cell>$griddata[$i]</cell>\n";
    }
    print "</row>\n";
    $rs->MoveNext(); // next result set row
    $datarow++;
    for ($i=0;$i<$treedepth;$i++){$treesave[$i] = $griddata[$i];} //save last node data
}
for ($j = 0; $j < $treedepth ; $j++) {print "</row>\n";} //close open tree nodes
print("</row>\n</rows>\n"); //close total and rows

?>

If there is a better way please advise, if not please share this anyway you like...
is there anyway to change the tree nodes on the client side (like grid regrouping)?

Thanks.
Regards

Unfortunately there is no significantly easy way to group data on server side (oncoming update will contain a set of PHP classes , which must simplify such task )

>>is there anyway to change the tree nodes on the client side
While there is no built-in solution, in theory it possible to extend loading routine, so it will load data from plain set of rows and will assign parent child relations on some kind of additional attributes. ( but you still need to form correct labels and parent-child attributes on server side, which require nearly similar amount of work as current solution ) 
Full grouping functionality can be created as well, but it will be resource consuming operation, and performance killer for big datasets ( we will add it to list of possible improvements )