Displaying data in data trees showing parent/child relationships can be important to your application for when users are looking for data. You have been seeing this all along, since you have been using computers with file browsing in directory trees showing a directory(which in fact is a file) and all of its children in one big tree starting with a the ‘/’ root path.
Here I will be showing you (most likely a beginner programmer) how to display data from a database (MySQL) using PHP.
In the this example I’m using the data from Maris SimpleDocu, a “simple” documentation system which in fact has document pages that have parent/child relationships we want to be displayed in a large tree (that depends on how many pages are in the database).
You can download the source files here!
Lets take a look at the `pages` table schema:
CREATE TABLE IF NOT EXISTS `pages` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL DEFAULT '',
`content` text NOT NULL DEFAULT '',
`post_date` int(10) UNSIGNED NOT NULL DEFAULT '0',
`last_update` int(10) UNSIGNED NOT NULL DEFAULT '0',
`parent` int(10) UNSIGNED NOT NULL DEFAULT '0',
`last_ip` varchar(100) NOT NULL DEFAULT '',
`p_order` int(5) UNSIGNED NOT NULL DEFAULT '0',
`hidden` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`views` int(10) UNSIGNED NOT NULL DEFAULT '0',
`locked` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
UNIQUE KEY `id` (`id`),
KEY `parent` (`parent`)
) ENGINE=MyISAM;
The key table fields you want to pay attention to are `id` `parent` and `p_order` (page order on that parent level).
I created this file and code in the SimpleDocu source for testing, called “test.php” here is the code:
<?php
include 'common.php';
$result = array();
$level = 1;
function tree
(&$res, &$lvl, $pid = 0) {
global $pc;
$sql = "SELECT id, title, parent, p_order FROM pages WHERE parent = '$pid' ORDER BY p_order";
$res[] = mysql_query($sql) or
die(mysql_error());
$i = sizeof($res)-1;
while ($row = mysql_fetch_array($res[$i])) {
if ($pc->has_children($row['title'])) {
print str_repeat('-', $lvl).$row['title']. '<br />';
$lvl++;
tree
($res, $lvl, $row['id']);
} else {
print str_repeat('-', $lvl).$row['title']. '<br />';
}
}
$lvl = 1;
}
tree
($result, $level);
?>
The Run Down:
I included the file called “common.php” because it holds most of the common small functions, variables and constants, but most importantly the database connection.
Next you’ll see I’ve declared a variable $result with an array type to hold multiple data resources and then $level integer so we can trace what level we are in the tree.
include 'common.php';
$result = array();
$level = 1;
Now we begin making our function: function tree(&$res, &$lvl, $pid = 0) … the arguments &$res and &$lvl take in variables passed by reference and work with the $result and $level variables. $pid is the variable used to get the last parent ID, you will see it used for when the tree function is used in recursion.
First section of tree():
global $pc;
$sql = "SELECT id, title, parent, p_order FROM pages WHERE parent = '$pid' ORDER BY p_order";
$res[] = mysql_query($sql) or
die(mysql_error());
The global $pc variable that instantiates a class called Pages and we use one of its methods called has_children($title) to determine if the current page in the loop has any children.
Second section of tree():
$i = sizeof($res)-1;
while ($row = mysql_fetch_array($res[$i])) {
if ($pc->has_children($row['title'])) {
print str_repeat('-', $lvl).$row['title']. '<br />';
$lvl++;
tree
($res, $lvl, $row['id']);
} else {
print str_repeat('-', $lvl).$row['title']. '<br />';
}
}
The variable $i get the last element in the $res array which holds the last database resource id in that script. Then we start the while loop notice that mysql_fetch_array($res[$i]) does that instead of just mysql_fetch_array($res). In the loop we first determine if the current page has any children with $pc->has_children($row['title']), if so we print the title then we call the tree() function from within (recursion) else just print the title and move on to the next section of the function.
Last section of tree():
We just set $lvl = 1;
Now we can call the tree function to see our results:
And here is my ouput: (NOTE: Output will vary because we all may not have the same pages created!)
-Introduction
–Test Child
–More
—And More
—-Wait there is more!!
-Chapt Two
–Section 1
-RSURL Test
-Another Test
Notice the dashes to mark how deep each page is for each level.
And thats all!
———————-[ EDITED 2009-08-22 ]——————–
Because of me not thinking of “scale” and people posting the comments below …Here is a more efficient way of doing this with using one query against the database!
<?php
include 'common.php';
class Tree
{
public $lvl = 1;
public $nodes = array();
public function __construct
() {
$this->fill_nodes();
}
public function fill_nodes
() {
$sql = "SELECT id, title, parent, p_order FROM pages ORDER BY p_order";
$result = mysql_query($sql) or
die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$vals = array();
$vals['id'] = $row['id'];
$vals['title'] = $row['title'];
$vals['parent'] = $row['parent'];
$vals['p_order'] = $row['p_order'];
$this->nodes[] = $vals;
}
}
public function has_children
($id) {
foreach ($this->nodes as $row)
if ($row['parent'] == $id) return true;
return false;
}
public function tree
($pid = 0) {
foreach ($this->nodes as $row) {
if ($row['parent'] == $pid) {
if ($this->has_children($row['id'])) {
print str_repeat('-', $this->lvl).$row['title']. '<br />';
$this->lvl++;
$this->tree($row['id']);
} else {
print str_repeat('-', $this->lvl).$row['title']. '<br />';
}
}
}
$this->lvl = 1;
}
}
$tree = new Tree
();
$tree->tree();
?>