Having a query within a loop

[defroster]

Hello,

 

I read somewhere that it is no good to have a query within a loop. But in my understanding there is no way to get around that sometimes. Anyone who could explain a bit more if things like the code below is ok to do? Or is there another way of doing this? The code has been very simplified but I hope you understand what I mean.

 

Thanks /df

 

 

 

SELECT * FROM PHOTOS

$i=1
ECHO PHOTOS

	SELECT * FROM AUTHOR WHERE AUTHOR=PHOTO_ID
	ECHO AUTHOR	

$i=1++

[Alex]

The alternative to this loop would be a MySQL JOIN. Something like this:

 

SELECT PHOTOS.*, AUTHOR.* FROM PHOTOS JOIN ON (PHOTOS.PHOTO_ID = AUTHOR.AUTHOR)

[defroster]

Thanks so much for help.. I am a bit confused by the JOIN, how would I rewrite the queries below to only have one query?

 

 

		$commsql = "SELECT * FROM comments WHERE video_id = " . $row['id'] . " ORDER BY dateposted;" ;
	$commresult = mysql_query($commsql);
	$numrows_comm = mysql_num_rows($commresult);

	if($numrows_comm == 0) {
		echo "No comments";
	}
	else {
		echo "Comments (<strong>" . $numrows_comm . "</strong>)</a><br>";
		$i = 1;		



		while($commrow = mysql_fetch_assoc($commresult)) {

			$userdetailsql = "SELECT * FROM users WHERE id = " . $commrow['userid'];
			$userdetailresult = mysql_query($userdetailsql)or die(mysql_error());
			$userdetailrow = mysql_fetch_assoc($userdetailresult);

		echo "<a href='viewentry.php?id=" . $row['id'] . "#comment" . $i . "'>". $i ." " . $commrow['comment'] . "</a>  ";
		echo "Comment by " . $userdetailrow['name'] . " (" . $commrow['userid'] . ":" . $userdetailrow['username'] . ")<br>";
		$i++;
		}

	}

	echo "<hr>";