Tree Generation from the Database

In addition to wide range of useful features CodeThatTree allows you to generate tree script from the database.

At the example below we use MySQL as database platform and PHP as web-script language. But you can choose your own favorite database platform and web-script language. Here we just try to show basic principles for database tree generation.

First of all we create three tables - script_items, script_styles and scripts. Tables structure and data are listed below:

CREATE TABLE scripts
(
	id INT AUTO_INCREMENT NOT NULL,
	scriptname VARCHAR(255),
	PRIMARY KEY (id)
);

insert into scripts values(1, 'CodeThatMenu Standard');
insert into scripts values(2, 'CodeThatTree Standard');
insert into scripts values(3, 'CodeThatCalendar Standard');
insert into scripts values(4, 'CodeThatXPBar Standard');

CREATE TABLE script_items
(
	item_id INT AUTO_INCREMENT NOT NULL,
	script_id INTEGER NOT NULL,
	item_name VARCHAR(255),
	style_id INTEGER DEFAULT 0,
	style_over_id INTEGER DEFAULT 0,
	item_parent_id INTEGER DEFAULT 0,
	PRIMARY KEY (item_id)
);

CREATE TABLE script_styles(
	style_id INT AUTO_INCREMENT NOT NULL,
	css VARCHAR(255),
	size VARCHAR(20),
	offset VARCHAR(20),
	bgcolor VARCHAR(7),
	color VARCHAR(7),
	bgimg VARCHAR(255),
	shadow VARCHAR(255),
	border VARCHAR(255),
	imgitem VARCHAR(255),
	imgdir VARCHAR(255),
	imgdiropen VARCHAR(255),
	fixwidth INT,
	PRIMARY KEY (style_id)
);

insert into script_items values(17, 2, 'Item 1', 5, 6, 0);
insert into script_items values(18, 2, 'Item 2', 5, 6, 0);
insert into script_items values(19, 2, 'Item 1:1', 0, 0, 17);
insert into script_items values(20, 2, 'Item 1:2', 0, 0, 17);
insert into script_items values(21, 2, 'Item 1:2', 0, 0, 17);
insert into script_items values(22, 2, 'Item 2:1', 0, 0, 18);
insert into script_items values(23, 2, 'Item 2:2', 0, 0, 18);
insert into script_styles values(5, 'test1tree', '[100, 25]', '
	{"x":"50","y":"0"}', '#ffffff', '#000000', '""', '""', '""', '"img/new.gif"',
	'"img/open.gif"', '"img/up.gif"', 200);
insert into script_styles
	values ( 6, 'test1tree', '', '', '#D3E5FA', '#00008B', '', '', '',
	'"img/new.gif"', '"img/open.gif"', '"img/up.gif"', 200);

Of course, when you create your real CodeThatTree script, tables will contain more information.

Next step - we create .php script. Result of this script implementation is a string variable that contains full tree definition:

/* declare some relevant variables */
$hostname = "localhost"; /* This is the hostname on which your MySQL is running */
$dbName = "ct";
$username = "root";
$password = "";
/* Make connection to database */
mysql_connect($hostname, $username, $password) OR
	DIE("Unable to connect to database");
/* Select the database to be processed */
@mysql_select_db( "$dbName") or die( "Unable to select database");

function create_level($parent_id)
{
	$items = '';
	$strQuery = "select item_id, item_name from script_items where
	script_id=2 and item_parent_id=" . $parent_id;
	$result = mysql_query($strQuery);
	$first = 1;
	while (list($item_id, $item_name) = mysql_fetch_row($result))
	{
		if ($first == 1)
			$first = 0;
		else
			$items .= ",";
		$next_level = create_level($item_id);
		if ($next_level == "")
		{
			$items .= ("{\"text\":\"".$item_name."\"");
			$items .= $next_level;
			$items .= "}";
		}
		else
		{
			$items .= ("{\"text\":\"".$item_name."\",\"menu\":{\"items\":[");
			$items .= $next_level;
			$items .= "]}}";
		}
}
	return ($items);
}

function create_definition()
{
	$definition = "";
	$strQuery = "select distinct css, size, offset, bgcolor, color, bgimg," .
		" shadow, border, imgitem, imgdir, imgdiropen, fixwidth" .
		" from script_styles, script_items where script_styles.style_id =" .
		" script_items.style_id " .
		" and script_items.item_parent_id=0 and script_id=2";
	$result = mysql_query($strQuery);
	$definition = $definition . 'var TreeDef = {"style" : {"css" : "' .
		mysql_result($result, 0, "css") . '", ' .
		'"size":' . mysql_result($result, 0, "size") . ', ' .
		'"itemoffset":' . mysql_result($result, 0, "offset") . ', ' .
		'"bgcolor":"' . mysql_result($result, 0, "bgcolor") . '", ' .
		'"color":"' . mysql_result($result, 0, "color") . '", ' .
		'"fixwidth":"' . mysql_result($result, 0, "fixwidth") . '", ' .
		'"imgitem":' . mysql_result($result, 0, "imgitem") . ', ' .
		'"imgdir":' . mysql_result($result, 0, "imgdir") . ', ' .
		'"imgdiropen":' . mysql_result($result, 0, "imgdiropen") .
		'}';
	$strQuery = "select distinct css, bgcolor, color, bgimg, shadow, border," .
		" imgitem, imgdir " .
		" from script_styles, script_items where script_styles.style_id =" .
		" script_items.style_over_id " .
		" and script_items.item_parent_id=0 and script_id=2";
	$result = mysql_query($strQuery);
	$definition = $definition . ', "itemover": { "css" : "' .
		mysql_result($result, 0, 'css') . '", ' .
		'"bgcolor":"' . mysql_result($result, 0, 'bgcolor') . '", ' .
		'"color":"' . mysql_result($result, 0, 'color') . '"} ';
	$definition = $definition . ', "position": { "absolute": false," .
		'"pos":[30,20] } ';
	$items = ', "items" : [ ';
	$items = $items . create_level(0);
	$items = $items . ' ] ';
	$definition = $definition . $items . " };";
	return $definition;
}

And finally we complete a web page for menu script:

<php
echo '<HEAD><link href="common_codethat.css" rel="stylesheet" type="text/css">'.
	'<script language="javascript1.2" src="codethatsdk.js"></script> ' .
	'<script language="javascript1.2" src="codethattreepro.js"></script> ' .
	'<script language="javascript1.2"> ' .
	create_definition() . '</script> </head>'; 
?>

<body bgcolor="#ffffff">
<script language="javascript1.2">
<!--

var t1 = new CTree(TreeDef, "sample");
t1.create();
t1.draw(); 

//-->
</script>
</body>
</html>

Example - Tree Generation from the Database

You can see an example and complete code here - Tree Generation from the Database Example [popup]

Read more about CodeThatTree >>

© CodeThat.com, 2003-2008
Design by XTLabs, Inc.