My code is not looping as I expect it too..

[FoxRocks]

Hello,

 

I've been trying to make a menu that is drawn from a mysql database. I am new to PHP and I am trying to learn it by trial and error...which I actually enjoy as I love troubleshooting and problem solving. Anyways I'm only saying that because I expect anyone with experience is going to look at my code and say "Why the eff are you doing ______"

 

Ok, so here it is:

 

$q1 = mysql_query("SELECT section_id , section_name FROM sections ORDER BY section_id");
$q2 = mysql_query("SELECT page_name , page_link , section_id FROM pages ORDER BY page_id");

while($section = mysql_fetch_array($q1))
	{
	echo $section['section_name'] . "<br>";
		while($page = mysql_fetch_array($q2))
			{
				if($page['section_id'] == $section['section_id'])
				{
				echo $page['page_name'] . "<br>";
				}
			}
	}

 

The problem I'm getting is the menu is coming out like this:

 

Section One

  Page 1

  Page 2

  Page 3

Section Two

Section Three

Section Four

 

 

But I want it to look like this:

 

 

Section One

  Page 1

  Page 2

  Page 3

 

Section Two

  Page 1

  Page 2

 

...etc.

 

It appears as though it's ignoring the page data on the second, third and forth loop. I've tried it a few different ways and by reading through the PHP.net manuals, but I'm not getting anywhere.

 

I can post this contents of my database, but I'm guessing it's not relevant as the field names sort of explain the structure well enough. I've got a few pages in each section and four sections.

Let me know if you want me to post the data.

 

Thanks for your help and your time, I appreciate it

 

~FOX~

 

[kicken]

After the first time you go through your while($page = mysql_fetch_array($q2)) loop, you will have exhausted all the records in that result set.  All subsequent calls to mysql_fetch_array will return false and cause the loop to exit.

 

What you need to do is loop the result set and save the results to an array first, then use that array to match things up.

$q1 = mysql_query("SELECT section_id , section_name FROM sections ORDER BY section_id");
$q2 = mysql_query("SELECT page_name , page_link , section_id FROM pages ORDER BY page_id");

$pages = array();
while($page = mysql_fetch_array($q2))
{
$pages[] = $page;
}

while($section = mysql_fetch_array($q1))
{
echo $section['section_name'] . "<br>";
foreach ($pages as $page)
{
	if($page['section_id'] == $section['section_id'])
	{
		echo $page['page_name'] . "<br>";
	}
}
}

 

Or, better yet, learn how to use an SQL JOIN to return your pages and sections in a single query and display them how you want with just a little processing logic:

$sql = '
SELECT
s.section_id,
s.section_name,
p.page_name,
p.page_link
FROM sections s
LEFT JOIN pages p ON s.section_id=p.section_id
ORDER BY
section_id,
page_id
';

$q1 = mysql_query($sql);
$lastSection=0;
while($section = mysql_fetch_array($q1))
{
if ($lastSection != $section['section_id']){ //Only display section header if the section has changed.
	$lastSection=$section['section_id'];
	echo $section['section_name'] . "<br>";
}

echo $page['page_name'] . "<br>";
}

 

[FoxRocks]

After the first time you go through your while($page = mysql_fetch_array($q2)) loop, you will have exhausted all the records in that result set.  All subsequent calls to mysql_fetch_array will return false and cause the loop to exit.

 

What you need to do is loop the result set and save the results to an array first, then use that array to match things up.

$q1 = mysql_query("SELECT section_id , section_name FROM sections ORDER BY section_id");
$q2 = mysql_query("SELECT page_name , page_link , section_id FROM pages ORDER BY page_id");

$pages = array();
while($page = mysql_fetch_array($q2))
{
$pages[] = $page;
}

while($section = mysql_fetch_array($q1))
{
echo $section['section_name'] . "<br>";
foreach ($pages as $page)
{
	if($page['section_id'] == $section['section_id'])
	{
		echo $page['page_name'] . "<br>";
	}
}
}

 

Hi Kicken,

 

Thank you very much, I just couldn't figure that out!!

 

Although what you said makes sense, in that it exhausted the records, I am still unsure of why your solution works. I mean, it works and it works perfectly, I just can't make sense of it...if that makes sense.

 

It really just tells me I've got a lot more to learn

 

Thanks for the help, much appreciated.

 

~FOX~