From Learn About the Ext JavaScript Library

Contents 1 Introduction 2 Database Setup 3 PHP Query & Loop 4 More to Come

Introduction

This tutorial will describe how to create a Parent/Child Relationship Tree using data stored from a MySQL database with PHP.

Database Setup

Let us suppose we have a MySQL database set up as follows:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(99) DEFAULT NULL,
  `parent_id` int(5) DEFAULT NULL,
  `offset` float DEFAULT '0',
  `leaf` tinyint(1) DEFAULT '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
 
-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `locations` VALUES ('1', 'United States', NULL, '0', '0');
INSERT INTO `locations` VALUES ('2', 'Georgia', '1', '0', '0');
INSERT INTO `locations` VALUES ('3', 'Atlanta', '2', '0', '0');
INSERT INTO `locations` VALUES ('4', 'Airport', '3', '0', '0');
INSERT INTO `locations` VALUES ('5', 'North Terminal', '4', '0', '1');
INSERT INTO `locations` VALUES ('6', 'South Terminal', '4', '0', '1');
INSERT INTO `locations` VALUES ('7', 'Macon', '3', '0', '0');
INSERT INTO `locations` VALUES ('8', 'Savannah', '7', '0', '0');
INSERT INTO `locations` VALUES ('9', 'Europe', NULL, '0', '0');
INSERT INTO `locations` VALUES ('10', 'England', '9', '0', '0');
INSERT INTO `locations` VALUES ('11', 'London', '10', '0', '0');
INSERT INTO `locations` VALUES ('12', 'Westminster', '11', '0', '1');
INSERT INTO `locations` VALUES ('13', 'Waterloo', '11', '0', '1');

What we have done is setup a table named "locations" with the following fields:

• ID (auto generated)
• Name (the name of the location)
• Parent_ID (where this location falls under)
• Offset (a value used in this example, but does not relate to the parent/child relationship)
• Leaf (whether or not this location is a leaf (i.e. has no children))

PHP Query & Loop

// Define arrays
	$refs = array();
	$list = array();
// Create SQL statement
	$sql = "SELECT * FROM locations ORDER BY id ASC";
	$result = mysql_query($sql);
// Loop through the results
	while ($data = @mysql_fetch_assoc($result))
	{
	    $thisref = &$refs[ $data['cust_location_id'] ];
 
	    $thisref['name'] = $data['name'];
	    $thisref['offset'] = $data['offset'];    
	    $thisref['parent_id'] = $data['parent_id'];
	    $thisref['id'] = $data['id'];
	    $thisref['uiProvider'] = 'col'; // Required for CSS
 
	    if ($data['leaf'] == 1) // tether_leaf has no children
	    {
		$thisref['expanded'] = '[TRUE]'; // if expanded is true and children is null, [+] will not appear on load (extjs mod)
		$thisref['children'] = '[NULL]';
	    }
 
	    if ($data['id'] == '')
	    {
	        $list[ $data['id'] ] = &$thisref; // This is a root
	    }
	    else
	    {
	    	$parent_id = $data['parent_id'];
 
	        $refs[$parent_id]['children'][] = &$thisref;
	    } // end if parent
	}
 
sort($list); // Might as well sort the array
 
$returnJSON = json_encode($list); // Convert the PHP array into a JSON array
$returnJSON = str_replace("\"[NULL]\"", "[]", $returnJSON); // Create the empty LEAF nodes
$returnJSON = str_replace("\"[TRUE]\"", "true", $returnJSON); // Expand the LEAF nodes
 
echo $returnJSON; // Return the grown tree

More to Come

I'll finish this soon(tm). :)