Iteration of grouped output creates an empty record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

Iteration of grouped output creates an empty record

Post by maxd »

I have a MySQL table which describes the architecture of a Web site I'm developing. I'm attempting to use the table to create dynamic menus and the sitemap. Here is the table structure, abbreviated:

Code: Select all

| id | parent    | child1 | child2 | child3 |
--------------------------------------------------
  1  | Home      |        |        |        |
  2  | About     |  Foo1  |        |        |
  3  | About     |  Foo2  |        |        |
  4  | About     |  Foo3  |        |        |
  5  | Resources |  Bar1  |        |        |
  6  | Resources |  Bar2  |        |        |
  7  | Resources |  Bar3  |  foo1  |        |
  8  | Resources |  Bar4  |  foo2  |        |
  9  | Services  |  Zot1  |        |        |
  9  | Services  |  Zot2  |        |        |
And so forth...

What I'm trying to do is iterate through the results of a query of this table, basically creating an outline format, which I can then leverage for navigation and for the sitemap.

Here's my query:

Code: Select all

$navQuery = sprintf("
SELECT DISTINCT parent, child1, child2, child3
FROM sitemap2
WHERE nav = 'y'
ORDER BY id,parent,child1,child2,child3;
");
And here's my display code:

Code: Select all

<?php 
						$myParent = '';
						$myChild1 = NULL;
						$myChild2 = NULL;
						$myChild3 = NULL;
						while ($row = mysql_fetch_assoc($navResult)) {
						  if ($myParent != $row['parent']) {
						    echo '<b>' . $row['parent'] . "</b><br>\n";
						    $myParent = $row['parent'];
						  }
						  if ($myChild1 != $row['child1']) {
						    if ($myChild1 != " ") {
						    	echo '<em>' . $row['child1'] . "</em><br>\n";
						    	}
						    $myChild1 = $row['child1'];
						  }
						  if ($myChild2 != $row['child2']) {
						    if ($myChild2 != " ") {
							    echo '&nbsp;&nbsp;&nbsp;' . $row['child2'] . "<br>\n";
							    }
						    $myChild2 = $row['child2'];
						  }
						}
					?>
This is working well (for some reason I had to add the

Code: Select all

if ($myChild1 != " ") {
because the display was dropping a single record if I didn't have that check... I'm not sure why. Actually, I have a sneaking suspicion this is related to the problem I'm describing below).

But there is a problem I can't resolve. The output has a single blank line in it, and it's not an empty record in the DB, as far as I can tell, because if I filter out a section, the blank line appears in a new place (always the 26th output line, I think). So, the source code of the output ends up looking like this (again, this is an abbreviated recreation):

Code: Select all

<b>ABOUT US</b><br>
<em>Foo1</em><br>
<em>Foo2</em><br>
<em>Foo3</em><br>
<b>RESOURCES</b><br>
<em>Bar1</em><br>
<em>Bar2</em><br>
<em>Bar3</em><br>
&nbsp;&nbsp;&nbsp;foo1<br>
                                             I'M NOT SURE WHY THERE'S A CARRIAGE RETURN HERE... ODD
<em>Bar4</em><br>
&nbsp;&nbsp;&nbsp;foo2<br>
<b>SERVICES</b><br>
<em>Zot1</em><br>
&nbsp;&nbsp;&nbsp;<br>  THIS IS THE MYSTERY ROW
<em>Zot2</em><br>
I assume it's some mystery with the loop, loop interference, full moon?

Whatever it is, I can't figure it out. Any ideas?

Thanks,
max
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

I would just store the element's name and parent node in the tree. Then you could use a recursive function to rebuild the tree... much easier than the weird nesting you're doing!

Also, for xhtml it would be better form to use nested unordered lists:

Code: Select all

<ul>
   <li>item 1</li>
   <li>item 2</li>
   <li>item 3
      <ul>
         <li>sub item 1<li>
         <li>sub item 2<li>
         <li>sub item 3<li>
      </ul>
   </li>
   <li>item 4</li>
   <li>item 5</li>
</ul>
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

Post by maxd »

Thanks for the reply. I wish I could pull off what you describe. It is (I think) how I was originally attempting to create this system. I only had "parent" and "child" fields in the DB. But no matter what I did in my output, I couldn't get the 2nd level child to display properly.

I don't suppose you have an example of a recursive function which works?

I was able to successfully iterate through the parent/child1 relationships, but the child1/child2 relationship would never take. Instead, child1 would move up to parent position, so instead of:

HOME
ABOUT
Foo1
Foo2
Foo3
RESOURCES
Bar1
Bar2
Bar3
foo1 (this being child2)
Bar4
foo2

it kept rendering as:

HOME
ABOUT
Foo1
Foo2
Foo3
RESOURCES
Bar1
Bar2
Bar3
Bar4
BAR 3
foo1
BAR 4
foo2

Obviously, I'm not much of a programmer, but I did try everything I could conceive of, until my eyeballs began to bleed and it was 2 in the morning. Any sample code you can provide is VERY welcome. I'm not sure I even know how to set up the tree/recursive function you describe.

Thanks,
max
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Database:

Code: Select all

item		parent
========================
Home 	 
About 	 
Resources 	 
Services 	 
Foo1 		About
Foo2 		About
Foo3 		About
Bar1 		Resources
Bar2 		Resources
Bar3 		Resources
Bar4 		Resources
Zot1 		Services
Zot2 		Services
Code:

Code: Select all

function buildMenu($parent=NULL){
	$result = MySQL_query("SELECT * FROM menu WHERE parent='".MySQL_real_escape_string($parent)."'");
	if(MySQL_num_rows($result)==0) return false;
	$output='<ul>';
	while($row=MySQL_fetch_assoc($result)){
		$output.='<li>'.$row['item'];
		$output.=buildMenu($row['item']);
		$output.='</li>';
	}
	$output.='</ul>';
	return $output;
}

echo buildMenu();
Output:

Code: Select all

* Home
    * About
          o Foo1
          o Foo2
          o Foo3
    * Resources
          o Bar1
          o Bar2
          o Bar3
          o Bar4
    * Services
          o Zot1
          o Zot2
You'll want to add more to this example like a URL field in the database, an ID number for each row (in case you want to change the title of a section), etc... but this should get you started. If you continue to have trouble, post it here!
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

Post by maxd »

In the end I resolved the issue with a different system altogether. It's a good tree menu system:

viewtopic.php?t=61737&sid=2f2ed11763e1c ... be86dc8ecd

Thanks for your help.
Post Reply