Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Working on Hierarchical Data tree structure with PHP, MySQL and XML.

Status
Not open for further replies.
timgerr

timgerr

IS-IT--Management
Jan 22, 2004
364
US
Hello all,
I have a huge task that I cannot find any good information on the internet about. I am working on a Hierarchical Data tree structure that will output the information into XML. I am working from 2 web pages, one is from MySQL: Managing Hierarchical Data in MySQL ( This is a good read and SitePoint: Storing Hierarchical Data in a Database (
The problem with most of these sites and already created classes is that the output from the PHP/Sql queries are in the form of tabbed indention's in HTML IE
Code: 
ELECTRONICS           
  TELEVISIONS         
   TUBE               
   LCD                
   PLASMA             
  PORTABLE ELECTRONICS
   MP3 PLAYERS        
    FLASH             
   CD PLAYERS         
   2 WAY RADIOS

What I am trying to do is return the structure in XML and that is getting confusing for me. I am wondering if I could get some help on this task. Since I have not seen this code done I will Open source it when I am done.

OK here is what I need help on. I read the article from
MySQL: Managing Hierarchical Data in MySQL ( and created the test database/table.

I have created a php file with the following code
Code: 
<?php
 	header("Content-type: text/xml");
    if(!$dbconnect = mysql_connect('localhost', 'root')) {
   		echo "Connection failed to the host 'localhost'.";
   		exit;
	} // if
	if (!mysql_select_db('tree')) {
	   echo "Cannot connect to database 'test'";
	   exit;
	} // if
	
	$table_id = 'nested_category';
	//$query = "SELECT * FROM $table_id";
	
	$query = "SELECT node.name, node.lft, node.rgt
				FROM nested_category AS node,
				nested_category AS parent
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
				AND parent.name = 'ELECTRONICS'
				ORDER BY node.lft";
	
	$dbresult = mysql_query($query, $dbconnect);
	
   // start with an empty $right stack
   $right = array(); 
   
	// This is the first line that is given 	
	echo "<?xml version=\"1.0\"?>\n";
	// process one row at a time
	while($row = mysql_fetch_array($dbresult)) {
		if($row['lft'] + 1 == $row['rgt']){
			$show = $show . "<node label=\"" . $row['name'] . "\"></node>\n";
		} else {
			$show = $show . "<node label=\"" . $row['name'] . "\">\n";
		}
		
		if (count($right)>0) {
           // check if we should remove a node from the stack
           while ($right[count($right)-1]<$row['rgt']) {
               array_pop($right);
				$show = $show . "</node>\n";		  	 
           }
       }
		$right[] = $row['rgt'];  
	}
	echo "<root>\n" . $show . "</root>";
?>

The output looks like this:
Code: 
<?xml version="1.0"?>
<root>
<node label="ELECTRONICS">
<node label="TELEVISIONS">
<node label="TUBE"></node>
<node label="LCD"></node>
</node>
<node label="PLASMA"></node>
</node>
<node label="PORTABLE ELECTRONICS">
</node>
</node>
<node label="MP3 PLAYERS">
<node label="FLASH"></node>
<node label="CD PLAYERS"></node>
</node>
</node>
<node label="2 WAY RADIOS"></node>
</node>
</root>

This is not correct, I need to output to mach the tree structure like this
Code: 
<?xml version="1.0"?>
<root>
	<node label="ELECTRONICS">
		<node label="TELEVISIONS">
			<node label="TUBE"></node>
			<node label="LCD"></node>
			<node label="PLASMA"></node>
		</node>

		<node label="PORTABLE ELECTRONICS">
			<node label="MP3 PLAYERS">
				<node label="FLASH"></node>
			</node>
			<node label="CD PLAYERS"></node>
			<node label="2 WAY RADIOS"></node>
		</node>
	/node>
</root>
This tree structure came from Can someone help me create this tree?

Thanks for the read and help,
Timgerr


-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!
 
Sort by date Sort by votes
I guess I didn't ask this question, has this been done already, am I reinventing the wheel? Does this code already exists?

Thanks,
Timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!


 
Upvote 0 Downvote
I think this is the solution. Please take a look and tell me if you see any thing wrong with it.

Thanks,

Timgerr

Code: 
<?php
/*
 *	Hierarchical Data tree structure
 *	What we have is a bunch of nodes and we need to see when to close them
 * 	What I did is use the left and right keys to see if the node is open or closes.  Open
 * 	meaning that there are other nodes under it and closes meaning that it is the leaf node
 * 
 *  What I did was:
 *		lft means left key and rgt means right key
 *		if lft + 1 != rgt then the node is not a leaf node.  I will then push the rgt key into an array using
 *		array_unshift because I want to have all new items in the array stacked on top
 * 		
 * 		I will then print the node without the closing tag.  This means that there are other nodes under
 * 		this node
 * 		
 * 		I will then see if rgt + 1 == array[0] meaning is the right key + 1 == to the stacked key.  If it 
 * 		is then we know we have to move up in the tree.  We will then send a close node and then shift off 
 * 		uses key from the array.
 * 		
 * 		If lft + 1 == rgt then we know that this is a leaf node.
 * 
 *	Examples and code barrowed from the following 
 *		MySQL: Managing Hierarchical Data in MySQL ([URL unfurl="true"]http://dev.mysql.com/tech-resources/articles/hierarchical-data.html)[/URL]
 *		SitePoint: Storing Hierarchical Data in a Database ([URL unfurl="true"]http://www.sitepoint.com/print/hierarchical-data-database)[/URL]
 */ 

 	header("Content-type: text/xml");
    if(!$dbconnect = mysql_connect('localhost', 'root')) {
   		echo "Connection failed to the host 'localhost'.";
   		exit;
	} // if
	if (!mysql_select_db('tree')) {
	   echo "Cannot connect to database 'test'";
	   exit;
	} // if
	
	$table_id = 'nested_category';
	
	
	$query = "SELECT node.name, node.lft, node.rgt
				FROM nested_category AS node,
				nested_category AS parent
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
				AND parent.name = 'ELECTRONICS'
				ORDER BY node.lft";
	
	$dbresult = mysql_query($query, $dbconnect);
	
   // start with an empty $right stack
   $right = array(); 
   
	// This is the first line that is given 	
	echo "<?xml version=\"1.0\"?>\n";
	
	while($row = mysql_fetch_array($dbresult)) {
		
		if($row['lft'] + 1 != $row['rgt']){
			array_unshift($right,$row['rgt']);
		//	echo $right[0] . "<br/>";
			$show .=  "<node label=\"" . $row['name'] . "\">\n";
		} else {
			$show .= "<node label=\"" . $row['name'] . "\"></node>\n";
		}
					
		if(($row['rgt'] + 1) == $right[0]){
			$show .= "</node>\n";
			array_shift($right);
		} 
	}
	if(count($right) == 1){
		$show .= "</node>\n";
	}
	echo $show;
?>

Outputs This:
Code: 
<?xml version="1.0"?>
<node label="ELECTRONICS">
<node label="TELEVISIONS">
<node label="TUBE"></node>
<node label="LCD"></node>
<node label="PLASMA"></node>
</node>
<node label="PORTABLE ELECTRONICS">
<node label="MP3 PLAYERS">
<node label="FLASH"></node>
</node>
<node label="CD PLAYERS"></node>
<node label="2 WAY RADIOS"></node>
</node>
</node>

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!
 
Upvote 0 Downvote
i think you'd be better off using the node based approach that the article discusses first. it is a more simple translation to xml.
 
Upvote 0 Downvote
Yea I was thinking that but I have to do adds, deletes and other things that this approach is better at doing.

timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
230
Chris Miller
Chris Miller
cdlvj
  • Question
PHP Debugging
Replies
0
Views
97
cdlvj
cdlvj
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
358
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
263
spamjim
spamjim
Brianfree
  • Locked
  • Question
Dropdown list data generator
Replies
3
Views
264
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top