[RESOLVED] displaying fields from tow tables in a nested while loop

[Fawkman]

Hi Guys

I am writing a forum and am on the admin functionality. When the admin is logged in they click on the FORUm link, and this takes them to a page, wehere I want to dislay all the different forums, then under each forum I want to display the topics for that forum. I have a topic, forum and comments table in my db.

I have tried to do the following using a nested while loop:

//this selects the topics in the topic table
$topicquery = "SELECT topic.topic_id,forum.forum_id,topic_title, topic_body FROM forum, module,topic where topic.forum_id = forum.forum_id AND module.forum_id = forum.forum_id";

$query = "SELECT * FROM forum";

  $data = mysqli_query($dbc, $query);
  while ($row = mysqli_fetch_array($data)) { 


  	echo '<tr align="left">';
	echo '<td class="forum_id">' . $row['forum_id'] . '</td>';
	echo '<td class="forum_name">' . $row['forum_name'] . '</td>';
	echo '<td class="forum_description">' . $row['forum_description'] . '</td>';
	echo "<td><a href='topic_create.php?forum_id=" . $row['forum_id'] ."'>Create Topic</a>";	
	echo '</tr></table>';

	echo '<br />';

	// while loop here which selects all topics associated to this forum
	 $topicdata = mysqli_query($dbc, $topicquery);
  while ($topicrow = mysqli_fetch_array($topicdata)) { 
  echo '<tr align="left">';
		echo '<td class="topic_id">' . $topicrow['topic_id'] . '</td>'; 
		echo '<td class="topic_title">' . $topicrow['topic_title'] . '</td>';
		echo '<td class="topic_body">' . $topicrow['topic_body'] . '</td>';
		echo "<td><a href='topic_view.php?topic_id=" . $topicrow['topic_id'] ."'>View Topic</a>";	
		echo '</tr></table>';
    	echo '<br />';
  }  //close while
    }

But when I run this, the output shows that each forum has exactly the same topics related to it, which is wrong. I think the topicquery select statement is wrong, as it selects all the topics in the db, but I cannot add a specific WHERE clause for a variable, as I want to show, all forums and all topics for each forum.

Could anyone advise me on where I am going wrong or how I could put this right to achieve what I am wanting to do. My other option is just to iterate the forum data in a while loop, then add a link to each forum called view topics, but I would like to avoid this if possible.

If anyone has any help then I would really appreciate it.

Kind regards

[sneakyimp]

You're going to have to run your topic query INSIDE the forum loop and limit the topics returned to the current forum id value.

[Fawkman]

Thanks for your response.

So I have changed the code to the following:

This time I tested the topicquery and it works as I want it to, when hard coding the forum_id. SO I have put the topicquery in the forum loop. But only the FORUM details are displayed. I thought if the first select statement is selecting the forum_id my second statement would run correctly. Should I be doing something different here?

$query = "SELECT * FROM forum";

	//$topicquery = "SELECT topic.topic_id, topic.topic_title, topic.topic_body FROM topic, forum WHERE forum.forum_id = topic.forum_id and forum.forum_id = '$forum_id'";

$data = mysqli_query($dbc, $query);
  while ($row = mysqli_fetch_array($data)) { 


  	echo '<tr align="left">';
	echo '<td class="forum_id">' . $row['forum_id'] . '</td>';
	echo '<td class="forum_name">' . $row['forum_name'] . '</td>';
	echo '<td class="forum_description">' . $row['forum_description'] . '</td>';
	echo "<td><a href='topic_create.php?forum_id=" . $row['forum_id'] ."'>Create Topic</a>";	
	echo '</tr></table>';

	echo '<br />';
$topicquery = "SELECT topic.topic_id, topic.topic_title, topic.topic_body FROM topic, forum WHERE forum.forum_id = topic.forum_id and forum.forum_id = '$forum_id'";

	// while loop here which selects all topics associated to this forum
	 $topicdata = mysqli_query($dbc, $topicquery);
  while ($topicrow = mysqli_fetch_array($topicdata)) { 
  echo '<tr align="left">';
		echo '<td class="topic_id">' . $topicrow['topic_id'] . '</td>'; 
		echo '<td class="topic_title">' . $topicrow['topic_title'] . '</td>';
		echo '<td class="topic_body">' . $topicrow['topic_body'] . '</td>';
		echo "<td><a href='topic_view.php?topic_id=" . $topicrow['topic_id'] ."'>View Topic</a>";	
		echo '</tr></table>';
    	echo '<br />';

	}  //close while
}

I dont beleive I have to use the GET or POST method here, or do I?

Any help is grately appreciated.

thanks

[sneakyimp]

You only need to use GET or POST data if you want this query to be affected by user input. It doesn't sound like you do...you just want to display forums and the topics they contain.

Your code change is good except for one thing. You put the var $forum_id in your topic query but you neglected to define this var anywhere. Maybe change that line to this:

$topicquery = "SELECT topic.topic_id, topic.topic_title, topic.topic_body FROM topic, forum WHERE forum.forum_id = topic.forum_id and forum.forum_id = '" . $row['forum_id'] . "'";

[Fawkman]

This is perfect. The code makes sense to me now, I was almost there. Thanks so much for your help, I really appreciate it

kind regards

[sneakyimp]

Glad to help.