[SOLVED] Help! Error while looping through query results.

[kjtocool]

Hey guys, any help is greatly appreciated.

 

In short, I make a $databaseConnect variable earlier in the code.  I then run a query on the database, and get a results set.  I enter into a while loop to go through the results, and each time through the loop, I want to query the database again to find something else.  I use the same $databaseConnect variable.

 

I am getting an error when trying to query the database the second time through:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in ...

 

<?php
		$query = "SELECT * FROM Comments WHERE article_ID = $article_ID";
		$result = mysqli_query($databaseConnect, $query);
		$row = mysqli_fetch_assoc($result);

		while ($row)
		{
			$userID = $row['user_ID'];
			$commentID = $row['comment_ID'];
			$comment = $row['comment'];

			$query2 = "SELECT username FROM Users WHERE user_ID = $user_ID";
			$result2 = mysqli_query($databaseConnect, $query2);
			$row2 = mysqli_fetch_assoc($result2);

			$userName = $row2['username'];
			mysqli_free_result($result2);

			echo '<table width="70%" border="0" align="center" cellpadding="0" cellspacing="1">';
			echo   '<tr>';
			echo	 '<td bgcolor="#F4FAFF" class="style6">Author: ' . $userName . ' ( ' . $userID . ' )</td>';
			echo   '</tr>';
			echo   '<tr>';
			echo	 '<td bgcolor="#F4FAFF" class="style6">' . $comment . '</td>';
			echo   '</tr>';
			echo '</table><br />';

			$row = mysqli_fetch_assoc($result);
		}

		mysqli_free_result($result);
		mysqli_close($databaseConnect);
		?>

 

Any explanation as to why I can't query while still using the results of the same query would be great, as well as how I can get around this.

 

KJ

[toplay]

There's several things wrong like using $userID and $user_ID in second query. But you can do this in one query anyway

 

Example:

 

$query =

"SELECT *

FROM `Comments` c

JOIN `Users` u ON u.user_ID = c.user_ID

WHERE c.article_ID = $article_ID";

 

If you need to get results from 'Comments' table even when there aren't any matching 'Users', the use a "LEFT JOIN" instead.

 

[kjtocool]

The $userID and $user_ID issue was what was giving me the error.  Thanks!

 

I think you're right about the join, I'll try that out and see if I can't get it to work.

 

EDIT:  The join worked great.  Thanks again for your help.