Tech Freaks .........!: August 2008

I recently had a problem of creating a folder tree structure.

I took the help of the CoolJsTree package and with the help of Ultratree Algoritham. I am thankful to Aitor Solozabal Merino

To learn how to dinamically build a tree from a MySql Table I write this utility PHP script code, for anyone like me that need it.

<?php

/*
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation in any version
of the License.This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

==============================================================================

Application : Mysql DataFile Treeview using PARTIALLY

         Ultratree 1.1 FREE Server-Side Tree Builder

         (Algorithm with only ONE SQL query required)

         PHP dynamic tree node generation process to use with

         COOLjsTree (Free/Standard/Pro) 2.7.0 Javascript Client-Side

         management

Name Program : Mysql_DataFile_Treeview_Builder.php

Author : Aitor Solozabal Merino (Spain)

Email : aitor-3@euskalnet.net

Date : 26-08-2005

Type : php program utility

Description : This utility build a data tree structure from a datafile in a

MySql database

******************************************************************************

* Based partially on the FREE algorithm of UltraTree v.1.1 by Mike Baikov *

* with only one query from the database and sorting the result *

* Full original source script can be downloaded from http://tourbase.ru/zink *

******************************************************************************

* With the management of the FREE/Standard/Pro COOLjsTree javascript utility *

* Can be downloaded from http://javascript.cooldev.com/scripts/cooltree *

* Can be used the professional versión of this utility choosing the indicated*

* lines in the script code *

******************************************************************************

* To run this script you must follow the next steps *

******************************************************************************

* 1º Download the appropiate files to a subdir under your web root *

* You need the files : COOLjsTree.js or ( COOLjsTreepro.js ) *

* Tree_Styles.css *

* to the same directory of this script *

* Create "img" subdir with the files: b.gif (blank), *

* e.gif (expanded), *

* c.gif (collapsed), *

* d.gif (document) *

******************************************************************************

* 2º you must create a MySql database to insert the new table and use it *

* with the SQL code below or any other of your invention *

* Take care that the code for the parent always have a value >=0 *

******************************************************************************

//SQL CODE TO CREATE AND POPULATE THE SAMPLE TABLE

use YOURDATABASE

drop table if exists `tab_treeview`;

CREATE TABLE `tab_treeview` (

`treeview_cod` smallint(5) unsigned NOT NULL auto_increment,

`treeview_name` char(25) NOT NULL default '',

`treeview_desc` char(25) NOT NULL default '',

`treeview_parent_cod` smallint(5) unsigned NOT NULL default '0',

PRIMARY KEY (`treeview_cod`)

) ENGINE=MyISAM ;

INSERT INTO `tab_treeview` VALUES (1,'root','folder',0);

INSERT INTO `tab_treeview` VALUES (2,'parent1','folder',1);

INSERT INTO `tab_treeview` VALUES (3,'parent2','document',1);

INSERT INTO `tab_treeview` VALUES (4,'parent3','folder',1);

INSERT INTO `tab_treeview` VALUES (5,'child1','folder',2);

INSERT INTO `tab_treeview` VALUES (6,'child2','document',2);

INSERT INTO `tab_treeview` VALUES (7,'grandchild1','document',5);

INSERT INTO `tab_treeview` VALUES (8,'grandchild2','document',5);

INSERT INTO `tab_treeview` VALUES (9,'child3','folder',4);

INSERT INTO `tab_treeview` VALUES (10,'child4','document',4);

INSERT INTO `tab_treeview` VALUES (11,'grandchild3','folder',9);

INSERT INTO `tab_treeview` VALUES (12,'grandgrandchild1','document',11);

INSERT INTO `tab_treeview` VALUES (13,'grandgrandchild2','document',11);

******************************************************************************

* 3º you must change some lines of the main code to set your MySql variables *

******************************************************************************

W A R N I N G S

Due to storing the full information of the tree directory in GLOBAL arrays, the perfomance

slow down when the number of nodes in the treeview grows up - is acceptable up to 3.000 nodes.

The purpose of this utility is learning about how to dinamically build a tree structure from

the SERVER-SIDE and manage it with a javascript utility in the CLIENT_SIDE.

*/

//******************************************************************************

function init_mysql_php_tree_process($host,$user,$userpass,$database,$sql_string){

//sql string with only id, parent, name SELECT FIELDS FROM

//mysql connection

$mysql_connection = mysql_connect($host, $user, $userpass);

mysql_select_db($database,$mysql_connection);

//only this query is required

$mysql_query_result=mysql_query($sql_string,$mysql_connection);

//--------------------------------------------------------------------------

build_data_tree($mysql_query_result);

//--------------------------------------------------------------------------

mysql_free_result($mysql_query_result);

mysql_close($mysql_connection);

}

function build_data_tree($mysql_query_result) {

//this is based on Ultratree algorithm

while ( list($id_cod, $parent_cod, $id_name) = mysql_fetch_array($mysql_query_result) ) {

$table[$parent_cod][$id_cod] = $id_name;

} ;

make_branch_tree(0, $table, 0);

RETURN;

}

function make_branch_tree($parent_cod, $table, $level) {

//recursive function based on Ultratree algorithm

$list = $table[$parent_cod];

asort($list); // the sorting

while (list($key, $val) = each($list)) {

   $GLOBALS['tree_node_counter']++;

   $GLOBALS['tree_node_id'][$GLOBALS['tree_node_counter']]=$key;

   $GLOBALS['tree_node_name'][$GLOBALS['tree_node_counter']]=$val;

   $GLOBALS['tree_node_level'][$GLOBALS['tree_node_counter']]=$level;

$GLOBALS['tree_node_parent'][$GLOBALS['tree_node_counter']]=$parent_cod;

if ((isset($table[$key]))) {make_branch_tree($key, $table, $level + 1);} ;

} ;

RETURN;

}

function show_data_tree(){

//show the data array ordered

//start table

echo "<p><a href='javascript: tree1.expandAll();'>open all</a> | <a href='javascript: tree1.collapseAll();'>close all</a> | <a href=".$_SERVER['PHP_SELF']."><Img SRC='img/tree_refresh1.gif' BORDER=0 alt='rebuild tree'> refresh</a></p>";

echo "<table border=2 align=CENTER cellpadding=2 cellspacing=2>";

ECHO "<tr><font size=3><b>

Mysql DataFile Treeview using PARTIALLY<br>

Ultratree 1.1 FREE Server-Side Tree Builder<br>

(Algorithm with only ONE SQL QUERY required)<br>

PHP dynamic tree node generation process to use with<br>

COOLjsTree (FREE/Standard/Pro) 2.7.0 Javascript Client-Side management<br>

<br>

</b>

</font>

";

//first row table header

echo "<tr>";

echo "<td align=center>TREE</td>";

echo "<td></td>";

echo "<td align=right>DATABASE</td>";

echo "<td align=right>" . $GLOBALS['database'] . "</td>";

echo "<td align=right>TABLE</td>";

echo "<td align=right>" . $GLOBALS['tablename'] . "</td>";

//second row table subheader

echo "</tr>";

echo "<td align=center>ORDER</td>";

echo "<td align=center>LEVEL</td><td></td>";

echo "<td align=center>".$GLOBALS['cod_id']."</td>";

echo "<td align=center>".$GLOBALS['cod_parent']."</td>";

echo "<td align=center>".$GLOBALS['cod_name']."</td>";

echo "</tr>";

//rest of the rows table body

for ($j=1;$j<=$GLOBALS['tree_node_counter'];$j++){

echo "<tr >";

echo "<td align=CENTER><b>".$j."</b></td>";

echo "<td align=CENTER><b>".$GLOBALS['tree_node_level'][$j]."</b></td>";

echo "<td></td>";

echo "<td align=CENTER><b>".$GLOBALS['tree_node_id'][$j]."</b></td>";

echo "<td align=CENTER><b>".$GLOBALS['tree_node_parent'][$j]."</b></td>";

echo "<td align=CENTER><b>".$GLOBALS['tree_node_name'][$j]."</b></td>";

echo "</tr>";

}

//end table

echo "</table>";

}

function php_process_data_tree(){

// this function build the node structure following the rules of the COOLjsTree utility

$indent_spaces_of_level = chr(9);

$var_tree= "var TREE1_NODES = [".chr(13); //naming convention

$level=0;

//loop starting in the begining of the tree ( root )

for ($j = 1;$j <= $GLOBALS['tree_node_counter'];$j++){

if ($level==0) {

$level=1;

}else{

if ($level < ($GLOBALS['tree_node_level'][$j]+1)){

$var_tree.=chr(13); //node end line without any special character only the carry return 13

$level++; // increment the level variable only it is possible in one by one way

} else {

If ($level == ($GLOBALS['tree_node_level'][$j]+1)) {

$var_tree.= "],".chr(13); //node end line with the special characters "],"

} else {

$var_tree.= "]".chr(13); // node end line with the special character "]" only. No comma

For ($i=($level-1);$i>($GLOBALS['tree_node_level'][$j]+1);$i--){

//closing levels until are equals

$var_tree.= str_repeat ($indent_spaces_of_level, ($i)) . "]".chr(13); // end of nodel levels with the special character "]" without comma

}

$level=$i;

if ($level>0) {

$var_tree.= str_repeat ($indent_spaces_of_level, ($level)) . "],".chr(13); // if there are more levels the node end line with "]," characters

}

}

}

}

$var_tree.= str_repeat ($indent_spaces_of_level, ($level)) . "[{id:".$GLOBALS['tree_node_id'][$j]."},'" . $GLOBALS['tree_node_name'][$j] . "',null,null,";

}

If ($level>0){

$var_tree.= "]".chr(13);

}

For ($i=($level);$i>1;$i--){

//closing levels until levels are equal with end node level "]" character

$var_tree.= str_repeat ($indent_spaces_of_level, ($i-1)) . "]".chr(13);

}

$var_tree.= "];".chr(13);

// end of the php process tree node structure

RETURN $var_tree; //returning the string with the full tree

}

function build_cooltree(){

//you can alter the commented parts of this code in case of

//cooljstree free/pro versions

//you can alter this function using a tree_format.js file in place of the

//code written for the tree_format variable

//you can test the output tree1_nodes.js file in place of the code written

?>

<html>

<head>

   <title>Mysql DataFile Treeview</title>

   

   <link rel='stylesheet' type='text/css' href='tree_styles.css' />

   

   

   

   

   <script type="text/javascript" src="cooltree.js"></script>-->



   

   <script type='text/javascript'>

   // Tree format definition

   var TREE_FORMAT = [

      //0. left position

      55,

      //1. top position

      70,

      //2. show +/- buttons

      true,

      //3. couple of button images (collapsed/expanded/blank)

      ["img/c.gif", "img/e.gif", "img/b.gif"],

      //4. size of images (width, height,ident for nodes w/o children)

      [16,16,0],

      //5. show folder image

      true,

      //6. folder images (closed/opened/document)

      ["img/fc.gif", "img/fe.gif", "img/d.gif"],

      //7. size of images (width, height)

      [16,16],

      //8. identation for each level [0/*first level*/, 16/*second*/, 32/*third*/,...]

      [0,16,32,48,64,80,96,112,128,144,160,176,192,208,224,240,256,272],

      //9. tree background color ("" - transparent)

      "",

      //10. default style for all nodes

      "clsNode",

      //11. styles for each level of menu (default style will be used for undefined levels)

      [],

      //12. true if only one branch can be opened at same time

      false,

      //13. item pagging and spacing

      [0,0],

   ];

   </script>

   

   <script type='text/javascript'>

      

      <?php echo $GLOBALS['tree']//you can comment this line and use tree1_nodes.js file;?>

   </script>

</head>

<body>

   <script type='text/javascript'>



      var tree1 = new COOLjsTree('tree1', TREE1_NODES, TREE_FORMAT);

      

      

      

      tree1.caption_onclick = function (_node) {

         alert("Cod_ID= "+_node.nodeID);

      }

      

      tree1.collapseAll(true);

      

   </script>

   

   

</body>

</html>

<?

}//end build_cooltree function

//==============================================================================

//=========================== MAIN PROGRAM ===================================

//==============================================================================

//MYSQL VARIABLES ( MUST BE CHANGED "Ad Hoc" BY YOU )

$host ="localhost"; // your mysql server

$user ="root"; // your mysql username

$userpass ="lbsb2vb"; // your mysql user password to access to databases

$database ="test"; // your mysql database name

//one sample table

$tablename="tab_treeview"; // your mysql table name

$cod_id="treeview_cod"; //your mysql table fields

$cod_parent="treeview_parent_cod";//your mysql table fields

$cod_name="treeview_name"; //your mysql table fields

//another sample table

//$tablename="treetest";// your mysql table name

//$cod_id="catid"; //your mysql table fields

//$cod_parent="parcat"; //your mysql table fields

//$cod_name="name"; //your mysql table fields

//==============================================================================

//=========================== NOTHING TO CHANGE ===============================

//==============================================================================

//init globals process variables

unset($tree_node_counter);

unset($tree_node_id);

unset($tree_node_parent);

unset($tree_node_name);

unset($tree_node_level);

$tree_node_counter=0;

//string to query the table : must have a SELECT node id, node parent, node name

//FROM any tree table structure ORDER by node_parent (very simple)

$sql="SELECT `".$cod_id ."`,`".$cod_parent ."`,`".$cod_name."` FROM `".$database."`.`".$tablename."` order by `".$cod_parent."`";

// time benchamrk (optional): end time count, get benchmark result

$time_start = microtime(1);

//start procedure

init_mysql_php_tree_process($host,$user,$userpass,$database,$sql);

// time benchmark end

$time_end = microtime(1);

$time_elapsed = $time_end - $time_start;

show_data_tree();

printf("<br>UltraTree built in %f seconds ", $time_elapsed);

// begin process to build the node structure for COOLjsTree javascript

// (free and commercial versions)

$time_start = microtime(1);

//start procedure

$tree=php_process_data_tree();

// time benchmark end

$time_end = microtime(1);

$time_elapsed = $time_end - $time_start;

//echo "<br>".$tree."<br>";

printf("<br>PHP process data tree in %f seconds ", $time_elapsed);

//the tree structure can be written to disk and shown with a text editor

$connection_file=fopen("tree1_nodes.js","w+");

fwrite($connection_file,$tree);

fclose($connection_file);

//finally the structure must be passed to COOLjsTree to manage the tree

$time_start = microtime(1);

//start procedure

build_cooltree();

// time benchmark end

$time_end = microtime(1);

$time_elapsed = $time_end - $time_start;

printf("<br>finally COOLjsTree show data tree in %f seconds ", $time_elapsed);

//=============================================================================

//=========================== END PROGRAM ===================================

//=============================================================================

?>

Anyone of you facing any problem implementing this pls post i ll try to help u to the maximum i can...

Tech Freaks .........!

Monday, August 18, 2008

PHP, MySQL and Javascript : Create a folder tree structure using database

Blog Archive

About Me