Page 1 of 1

[56K WARN] using while loop inside a while loop?

Posted: Mon Jun 04, 2007 2:54 pm
by .Stealth
is this possible?


first off ill explain what im doing, im making a website and im using a mysql database to generate the links in the menu.
in the menu there will be category's which contain the links like the below:

CATEGORY 1
link 1
link 2
link 3

CATEGORY 2
link 1
link 2
link 3

i have tried to get all of the info needed by using a while loop, but it only seems to be getting 1 link from the database and it repeats it through all category's.

if you would like to see the results live, here it is:
http://www.designs.sketchfx.co.uk/index.php

heres my tables:

ive used a table to link 2 tables together, 1st contains the actual links and their names, 2nd contains the stuff to relate both tables and the third is the category names and their id's.

ImageImageImage



heres my script (its just a function):


Code: Select all

function display_menu(){
	//get a list of categorys
	$cats = @mysql_query('SELECT id, name FROM linkCategory');
	if(!$cats){
		echo '<p>error retreiving menu from' .
			 '<br /> the database</p>';
	}
	//get category name and id
	while ($cat = mysql_fetch_array($cats)){
		$name = $cat['name'];
		$catid = $cat['id'];
		
		//get corresponding links
		$links = @mysql_query("SELECT linkid FROM linkcategory WHERE categoryid ='$catid'");
		if(!$links){
			echo '<p>error retreiving menu from' .
			 	 '<br /> the database</p>';
		}
	while($link = mysql_fetch_array($links)){
		$linkid = $link['linkid'];
		
	}
	$actual_links = @mysql_query("SELECT name, url FROM menu WHERE id ='$linkid'");
	while($actual_link = mysql_fetch_array($actual_links)){
		$link_name = $actual_link['name'];
		$link_url = $actual_link['url'];
	}
		
		echo "<h1>$name</h1>\n
		<li><a href='$link_url'/>$link_name</a></li>\n";
	}
}


if anybody could help me at all i would be most great full :)

Posted: Mon Jun 04, 2007 3:07 pm
by maliskoleather
your third while loop should be nested inside the second one:

Code: Select all

while($link = mysql_fetch_array($links)){
            $linkid = $link['linkid'];
            $actual_links = @mysql_query("SELECT name, url FROM menu WHERE id ='$linkid'");
            while($actual_link = mysql_fetch_array($actual_links)){
                $link_name = $actual_link['name'];
                $link_url = $actual_link['url'];
            }
        }
this is causing it to cycle through all the link id's before running them ;)

Posted: Mon Jun 04, 2007 3:29 pm
by .Stealth
ohh i see, thanks alot for that :D

Posted: Mon Jun 04, 2007 3:40 pm
by s.dot
I've always tried to avoid using queries inside of loops -- that's where bottlenecks commonly occur! Instead, you could store all queries results in arrays, and loop through the arrays, referencing other arrays to get information from the other queries.

Posted: Mon Jun 04, 2007 4:19 pm
by RobertGonzalez
I would say over the last 3 months I have posted a solution to this exact same problem maybe five times. I think I even included code once.

In all cases, I run queries that read into arrays, then loop the arrays instead of making a bunch of trips to the database. But that is just me.