Forum Counting Help

[Smackie]

I am in need of help i been bashing my head on the desk for 3 days straight on trying to get this script working..

I need it to count how many topics & posts there are in each category.. here is the script i got so far (its not all there but its all you should need)..

<?php 
$id = $_GET['id']; 

    $sql = mysql_query("SELECT * FROM header ORDER BY `id`"); 
    while($rows = mysql_fetch_array($sql)){ 
    $id = $rows['id']; 
    $a_id = $rows['id']; 

    $datetime = date('D dS M Y, h:i a', strtotime($rows['datetime'])); 

$get_num_topics = "select count(forumid) from forum_question where id = $id"; 
$get_num_topics_res = mysql_query($get_num_topics) or die(mysql_error()); 
$topics = mysql_result($get_num_topics_res,0,'count(forumid)'); 

$get_num_posts = "select count(question_id) from forum_answer where a_id = $a_id"; 
$get_num_posts_res = mysql_query($get_num_posts) or die(mysql_error()); 
$posts = mysql_result($get_num_posts_res,0,'count(question_id)'); 
?>

<tr>
    <td width="80%"><a href="../index.php?pages=topic&id=<?php echo $rows['id']; ?>"><?php echo $rows['heading']; ?></a><BR>&nbsp; &nbsp; &nbsp;<?php echo $rows['description']; ?></td>
    <td width="10%" align="center"><?php echo $topics; ?></td>
    <td width="10%" align="center"><?php echo $posts; ?></td>
  </tr>

Can someone please help me

Thank you
Smackie

[NogDog]

I suspect there is a way to get all the required data via one query, using one or more JOINs and probably a GROUP BY, but without understanding knowing what your database schema is and how the tables are related, I cannot suggest any specifics.

[Smackie]

well i got the topics part working its posting thats giong to be hard..

<?php 

$id = $_GET['id'];

$get_topic = mysql_query("SELECT id, heading, description FROM header ORDER BY id DESC") or die(mysql_error());	
	while($topic_info = mysql_fetch_array($get_topic)) {
		$id = $topic_info['id'];
		$heading = stripslashes($topic_info['heading']);
		$description = $topic_info['description'];

$get_num_topics = mysql_query("SELECT forumid FROM forum_question WHERE forumid = $id");
$topics = mysql_num_rows($get_num_topics);

$get_num_posts = mysql_query("SELECT question_id FROM forum_answer WHERE question_id = $id");
$posts = mysql_num_rows($get_num_posts);
?>
	<tr>
    <td width="80%"><a href="../index.php?pages=topic&amp;id=<?php echo $id; ?>"><?php echo $heading; ?></a><br /><?php echo $description; ?></td>
    <td width="10%" align="center"><?php echo $topics; ?></td>
    <td width="10%" align="center"><?php echo $posts; ?></td>
  	</tr>

here is my database setup..

header
id, heading, description

forum_question
id, forumid, topic, detail, userid, lastpost, datetime, view, reply

forum_answer
question_id, a_id, userid, a_answer, a_datetime

since i got the topics one workin i need to get a way to get id from forum_question and set it down so it matches question_id on

    $get_num_posts = mysql_query("SELECT question_id FROM forum_answer WHERE question_id = $id");
	$posts = mysql_num_rows($get_num_posts);

i hope you understand..

thank you
Smackie