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 biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Echo hierarchy from MySQL 1

Status
Not open for further replies.
y2k1981

y2k1981

Programmer
Aug 2, 2002
773
IE
Sorry that's not a very good subject line, I'm pretty new to the whole PHP and MySQL thing. Basically, I've got the following table in MySQL:
Code: 
OUID     OU_Name     Parent_OU
1        MyComp     <blank>
2        Div1        1
3        Div2        1
4        DepA        2
5        DepB        2
6        DepC        3
7        DepD        3

So basically, as you can see we've got a company called my comp. Then we've got 2 divisions in there, Div1 and Div2. In Div1, we've got 2 depattmetns, A & B and in Div2 we've got departments C & D. What I want know is how to I echo out the hierarchy. So far, I've got
Code: 
$get_hierarchy = mysql_query("SELECT * FROM hierarchy ORDER BY OUID");
$hierarchy = mysql_fetch_array($get_hierarchy);
I can echo it all out, but say I want to put a Div (say in blue) to show the company, then another div with the divisions and then their departments underneath that, how can I go about it? I don't want to introduce levels because the company pretty much has sub-departments within departments - not a very structured hierarchy. Can anybody help me do this at all?
 
Sort by date Sort by votes
I don't think you can't do it effectively with one query. The way I would handle this is to query for the topmost parent (in my datebase, this is signified by a NULL value in the parent_OU column), then fetch children via a recursive funtion.

Using this data:

[tt]+------+---------+-----------+
| OUID | OU_name | parent_OU |
+------+---------+-----------+
| 1 | MyCOMP | NULL |
| 2 | Div1 | 1 |
| 3 | Div2 | 1 |
| 4 | DepA | 2 |
| 5 | DepB | 2 |
| 6 | DepC | 3 |
| 7 | DepD | 3 |
+------+---------+-----------+[/tt]

This code:
Code: 
<?php
function show_children ($id, $level)
{
	global $dbh;
	
	$query = "SELECT * FROM company_data WHERE parent_OU = " . $id;
	
	$rh = mysqli_query ($dbh, $query);
	
	while ($row = mysqli_fetch_assoc($rh))
	{
		for ($counter = 0; $counter < $level; $counter++)
		{
			print "\t";
		}
		
		print $row['OU_name'] . "\n";
		
		show_children ($row['OUID'], $level + 1);
	}
}

print '<html><body><pre>';

$dbh = mysqli_connect ('localhost', 'test', 'test', 'test');

$query = "SELECT * FROM company_data WHERE parent_OU IS NULL";

$rh = mysqli_query ($dbh, $query);

while ($row = mysqli_fetch_assoc ($rh))
{
	print $row['OU_name'] . "\n";
	
	show_children ($row['OUID'], 1);
	
	print "\n\n";
}

print '</pre></body></html>';
?>

produced this output:
[tt]MyCOMP
Div1
DepA
DepB
Div2
DepC
DepD[/tt]




Want the best answers? Ask the best questions!

TANSTAAFL!!
 
Upvote 0 Downvote
Thanks, that's just what I was looking for. I think you must be using a different version of PHP to me, I've never seen mysqli_query or mysqli_*anything* for that matter !! But I've got it working anyway. My next step is to create DIV's instead of using the PRE tag, and I think I'm nearly there. But out of curiosity, what's the difference between mysql and mysqli??????????????
 
Upvote 0 Downvote
Hi Again,

Unfortunately I need your help with one more thing. Basically, instead of having a tab for each level, I want to create a div for each level, so My Company would be in a DIV, then Div 1 and Div 2 would be in a div ... like this:
Code: 
<div>My Company
   <divDiv 1
         <div>Dep A
              Dep B
         </div>
       Div 2
         <div>Dep C
              Dep D
          </div>
    </div>
</div>

then I would use JavaScript to control the visibility of each DIV. My problem is, that because the function is being called withint itself, it echo's a DIV echo time it loops through the while statement. Can you help me out at all? My function is still basically the same as yours except that I've removed all reference to level because it was just being used to generate the tabs.

Thanks in advance for any help you can give me
 
Upvote 0 Downvote
Try this on for size:

Code: 
<?php
function show_children ($id, $level = 0)
{
	global $dbh;
	
	$query = "SELECT * FROM company_data WHERE";
	
	if ($level != 0)
	{
		$query .= " parent_OU = " . $id;
	}
	else
	{
		$query .= " parent_OU IS NULL";
	}
	
	$rh = mysqli_query ($dbh, $query);
	
	if (mysqli_num_rows($rh) > 0)
	{
		
		print str_repeat ("\t", $level) . '<div id="level' . $level . '">' . "\n";
		while ($row = mysqli_fetch_assoc($rh))
		{
			print str_repeat ("\t", $level + 1) . $row['OU_name'] . "\n";
			
			show_children ($row['OUID'], $level + 1);
			
		}
		print str_repeat ("\t", $level) . '</div>' . "\n";
	}
}

$dbh = mysqli_connect ('localhost', 'test', 'test', 'test');

print '<html><body>

';

show_children ($row['OUID']);
	
print '
</body></html>';
?>

On my machine, this script outputs:

Code: 
<html><body>

<div id="level0">
	MyCOMP
	<div id="level1">
		Div1
		<div id="level2">
			DepA
			DepB
		</div>
		Div2
		<div id="level2">
			DepC
			DepD
		</div>
	</div>
</div>

</body></html>

I moved logic into the recursive function and got rid of the loop to print tabs in lieu of the str_repeat() function. I kept the level parameter, as it might be useful as a place to hang "id" or "class" HTML parameters. Plus it pretties up the output a little.

Want the best answers? Ask the best questions!

TANSTAAFL!!
 
Upvote 0 Downvote
CORRECTION:

The script in my last post produces an error. This is the corrected script:

Code: 
<?php
function show_children ($id = "NULL", $level = 0)
{
	global $dbh;
	
	$query = "SELECT * FROM company_data WHERE";
	
	if ($id != "NULL")
	{
		$query .= " parent_OU = " . $id;
	}
	else
	{
		$query .= " parent_OU IS NULL";
	}
	
	$rh = mysqli_query ($dbh, $query);
	
	if (mysqli_num_rows($rh) > 0)
	{
		print str_repeat ("\t", $level) . '<div id="level' . $level . '">' . "\n";
		while ($row = mysqli_fetch_assoc($rh))
		{
	
			print str_repeat ("\t", $level + 1) . $row['OU_name'] . "\n";
			
			show_children ($row['OUID'], $level + 1);
	
		}
		print str_repeat ("\t", $level) . '</div>' . "\n";
	}
}

$dbh = mysqli_connect ('localhost', 'test', 'test', 'test');

print '<html><body>

';

show_children ();
	
print '
</body></html>';
?>

Want the best answers? Ask the best questions!

TANSTAAFL!!
 
Upvote 0 Downvote
Once again thanks for your post. Actually, the first way you posted worked, not the second one!! Althought I can't understand how when the function doesn't yet have a value for $row['OUID']. But likewise I can't understand how the function works without the required parameters being passed to it at all.

There was one problem with your method actually, you were using level for the ID for the div's. This produces duplicates - because you have 2 div's which both contain "level 2" items - ie dep a and dep b in one div and dep c and dep d in another div. so instead, I used the parent OU as the id - this will be unique because each div contains the children of the OU above it. so all I had to do was say id=div_".$id. In MySQL, I set changed parent_OU for my company so that it's "0" instead of null, however, it doesn't echo that one for some reason. the id is just echoed as div_.

apart from that, it's perfect. once again, thanks for all your help
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Locked
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
387
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
489
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
327
spamjim
spamjim
P
  • Locked
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
440
Chris Miller
Chris Miller
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
602
ersatz
ersatz

Part and Inventory Search

Sponsor

Back
Top