Looping too many times

[enfys]

I know this is probably simple but I can't seem to see where I'm going wrong.

I have a number of categories in MySQL (just text) and each category can have a number of topics under it (topics are contained in a separate table and linked via a foreign key (cat_id).

I want to display each category title with all the relevant topics under it.

The first loop is ok but the 2nd loop includes the previous topics and so on...

It's probably staring me in the face but I can't see it!!!!!

Help!!

mysql_connect($hostname, $mysqlusername, $mysqlpassword);
mysql_select_db($database);

$sql="SELECT * FROM categories_tbl ORDER BY category_order ASC";

$result=mysql_query($sql) or die(mysql_error());

while ($cat_array=mysql_fetch_array($result)) {
$cat_id=$cat_array['category_id'];
$cat_title=$cat_array['category_title'];

//Get topics for the current category
$sql="SELECT * FROM topics_tbl WHERE cat_id = $cat_id ORDER BY topic_order";

$topic_result=mysql_query($sql);

while($topics_array=mysql_fetch_array($topic_result)) {
$topic_id=$topics_array['topic_id'];
$topic_title=$topics_array['topic_title'];
$topic_created=$topics_array['topic_created'];

$topic_rows .="
<tr bgcolor=\"ffffff\">
 <th></th>
 <th>$topic_title</th>
</tr>";
}

$cat_rows .="
<tr>
<th colspan=\"2\" class=\"tdCat\"><a href=\"view_topics.php?catID=$cat_id\">$cat_title</a></th>
</tr>
$topic_rows";
}
?>

<?php include("inc_header.php"); ?>

<table class="tblCats" cellspacing="1">
<tr bgcolor="#999999">
 <th></th>
 <th>Categories</th>
</tr>
<?php echo $cat_rows; ?>
</table>

<?php include("inc_footer.php"); ?>

[enfys]

Just managed to work it out.

I wasn't clearing the $topic_rows variable on each loop.

🙂

[thorpe]

you should really try to put this all into one query using 'joins' aswell.

much easier and more efficient.

[enfys]

Thanks, I thought about joining the tables but wasn't sure how to do it.

The problem is that I only want to display each category once but will need to show all topics under the category. How would I avoid repeating the category on each loop of the topics?

Would I use the group by option?

Thanks