[RESOLVED] how do I construct a loop within a loop?

[greg890]

Everytime I try this, the loop kills and I get 0 results.

Is it possible to make a loop within a loop for example:

<?
$sql = "select * from table1";
$result = executequery($sql);
while($line=mysql_fetch_array($result))
{
$product_name	 = $line['product_name'];

$sql = " select * from table2 where product_name = '$product_name' ";
result2 = executequery($sql);
while($line=mysql_fetch_array($result2))
{
$product_id	= $line['product_id'];

echo("$product_name - $product_id<br>");
}}
?>

I know that I could just join the queries, but my actual code has about 4 or 5 tables that are linked together by different parameters, and I'm trying to pull data from all of them where they all share various common values.

Thanks!

[Piranha]

It is possible to have a loop in a loop. The only strange thing I see in your code is

$result = executequery($sql); 

Executequery() is not a PHP function, it should be mysql_query unless you have coded executequery yourself. Can it be that there is no results?

I think that you should use one query if possible rather than queries in a loop. To evaluate what is best use the function microtime to see how long the queries take.

[greg890]

Thanks Piranha,

You're probably right that I'm looking for something that doesn't exist, and it's killing the loop. I'll go back and look more carefully at what data I'm trying to pull.

As for the executequery, I did write a function for it. This isn't a big deal, but it might help newbies like myself who are trying to troubleshoot their script:

function executeQuery($sql)
{
	$result = mysql_query($sql) or die("<span style='FONT-SIZE:11px; FONT-COLOR: #000000; font-family=tahoma;'><center>An Internal Error has Occured. Please report following error to the webmaster1.<br><br>".$sql."<br><br>".mysql_error()."'</center></FONT>");
	return $result;
} 

[NogDog]

I'd probably simplify(?) it to this:

$sql = "SELECT t1.product_name, t2.product_id FROM table1 t1 INNER JOIN table2 t2 ON t1.product_name = t2.product_name";
$result = executequery($sql);
while($line = mysql_fetch_assoc($result))
{
   printf("%s - %s<br>\n", $line['product_name'], $line['product_id']);
}

[bradgrafelman]

While NogDog's approach with using SQL JOIN's may seem more daunting of a task, it's probably more efficient that executing a separate SQL query within each loop iteration.

[anoopmail]

--