[SOLVED] Problem with forums being duplicated.

[Eiolon]

I created a small forum which seems to be working with one exception.  Whenever more than 1 topic is created in a forum it duplicates the forum on the main page and also gives the duplicate the wrong post count.

 

Query:

 

$query_both = "SELECT f.forum_id, f.forum_name, f.forum_desc, f.forum_num_topics, t.topic_num_replies, t.topic_forum FROM forums f JOIN topics t ON (f.forum_id = t.topic_forum)";
$both = mysql_query($query_both) OR die ('Cannot retrieve forum information.');
$row_both = mysql_fetch_array($both);

 

Table:

 

          <table width="100%" border="0" cellspacing="1" cellpadding="6" bgcolor="#CCCCCC">
            <tr bgcolor="#E5E5E5">
              <td><strong>Forum</strong></td>
              <td width="75"><div align="center"><strong>Topics</strong></div></td>
              <td width="75"><div align="center"><strong>Posts</strong></div></td>
            </tr>
            <?php do { 

		$number1 = $row_both['forum_num_topics'];
		$number2 = $row_both['topic_num_replies'];
		$posts = $number1 + $number2;

		?>
            <tr bgcolor="#FFFFFF">
              <td><a href="forum.php?forum_id=<?php echo $row_both['forum_id'] ?>"><?php echo $row_both['forum_name'] ?></a> - <?php echo $row_both['forum_desc'] ?></td>
              <td width="75"><div align="center"><?php echo $row_both['forum_num_topics'] ?></div></td>
              <td width="75"><div align="center"><?php echo $posts ?></div></td>
            </tr>
            <?php } while ($row_both = mysql_fetch_array($both)); ?>
          </table>

 

Result after more than 1 topic is created in the forum (duplicate in red square):

 

 

Thanks for your help!

 

[wsantos]

can you post the insert query?

[Eiolon]

Insert query for adding a topic:

 

$insert = "INSERT INTO topics (topic_subject, topic_author, topic_body, topic_date, topic_forum) VALUES ('".$_POST['topic_subject']."','".$_POST['topic_author']."','".$_POST['topic_body']."',now(),'".$_GET['forum_id']."')";
$result = mysql_query($insert) OR die ('Could not add topic to forum.');

if ($insert) {
$update = "UPDATE forums SET forum_num_topics = '$topics', forum_num_posts = '$posts' WHERE forum_id = ".$_GET['forum_id']."";
$update_result = mysql_query($update) OR die ('Could not add to topic total.');
header('Location: topic_added.php');
exit; }	

 

 

Insert query for adding a reply:

 

$insert = "INSERT INTO replies (reply_author, reply_body, reply_date, reply_topic) VALUES ('".$_POST['reply_author']."','".$_POST['reply_body']."',now(),'".$_GET['topic_id']."')";
$result = mysql_query($insert) OR die ('Could not add reply to topic.');

if ($insert) {
$update = "UPDATE topics SET topic_num_replies = '$replies' WHERE topic_id = ".$_GET['topic_id']."";
$update_result = mysql_query($update) OR die ('Could not add to reply total.');
header('Location: reply_added.php');
exit; }	

 

I just want to be clear that it's not actually inserting the duplicate forums into the database.  It appears that it's the join making the output duplicate, but I have tried every join that I could find and still no resolution.

[wsantos]

Try this

 

$query_both = "SELECT DISTINCT f.forum_id, f.forum_name, f.forum_desc, f.forum_num_topics, t.topic_num_replies, t.topic_forum FROM forums f JOIN topics t ON (f.forum_id = t.topic_forum)";

[Eiolon]

No luck, same result  :'(

[wsantos]

can you run these queries directly on mysql and post the results here.

 

SELECT topic_subject,topic_forum from topics;

 

SELECT forum_id,forum_desc,forum_num_topics from forums;

 

[Eiolon]

Certainly, here you go:

 

mysql> SELECT topic_subject,topic_forum from topics;
+-----------------------------+-------------+
| topic_subject               | topic_forum |
+-----------------------------+-------------+
| Welcome to the forums!      |           5 |
| use of others library cards |           3 |
| The new room                |           4 |
| South Wing                  |           5 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)

 

mysql> SELECT forum_id,forum_desc,forum_num_topics from forums;
+----------+---------------------------------------------------+------------------+
| forum_id | forum_desc                                        | forum_num_topics |
+----------+---------------------------------------------------+------------------+
|        3 | Talk about general library business, issues, etc. |                1 |
|        4 | Discuss the library expansion project.            |                1 |
|        5 | Chat about anything not related to the library.   |                2 |
+----------+---------------------------------------------------+------------------+
3 rows in set (0.00 sec)

[wsantos]

Cool...Then execute your join...and post the result here

 

SELECT DISTINCT f.forum_id, f.forum_name, f.forum_desc, f.forum_num_topics, t.topic_num_replies, t.topic_forum FROM forums f JOIN topics t ON (f.forum_id = t.topic_forum);

 

Looking on it that should work...If the result is what I expect then let's try a while loop instead of a do-while.

[Eiolon]

Looks to be the same output.

 

mysql> SELECT DISTINCT f.forum_id, f.forum_name, f.forum_desc, f.forum_num_topics, t.topic_num_replies, t.topic_forum FROM forums f JOIN topics t ON (f.forum_id = t.topic_forum);
+----------+--------------------+---------------------------------------------------+------------------+-------------------+-------------+
| forum_id | forum_name         | forum_desc                                        | forum_num_topics | topic_num_replies | topic_forum |
+----------+--------------------+---------------------------------------------------+------------------+-------------------+-------------+
|        5 | The Lounge         | Chat about anything not related to the library.   |                2 |                11 |           5 |
|        3 | General Discussion | Talk about general library business, issues, etc. |                1 |                 1 |           3 |
|        4 | Library Expansion  | Discuss the library expansion project.            |                1 |                 1 |           4 |
|        5 | The Lounge         | Chat about anything not related to the library.   |                2 |                 0 |           5 |
+----------+--------------------+---------------------------------------------------+------------------+-------------------+-------------+
4 rows in set (0.00 sec)

[Eiolon]

Let me ask you this, would it be better to make a new column in the forums table called "forum_num_posts" and whenever a new topic/reply is made update that column?  It would stop me from having to do a join but then again I am not sure if that is the best choice or not since I am new to this stuff.

[wsantos]

That answers our problem, use this:

 

SELECT DISTINCT f.forum_id, f.forum_name, f.forum_desc, f.forum_num_topics, t.topic_forum FROM forums f JOIN topics t ON (f.forum_id = t.topic_forum);

 

Don't include the number of replies in that section since it is the number of replies per topic.

[wsantos]

Let me ask you this, would it be better to make a new column in the forums table called "forum_num_posts" and whenever a new topic/reply is made update that column?  It would stop me from having to do a join but then again I am not sure if that is the best choice or not since I am new to this stuff.

 

It is always dependent on what your plans for your site are...Nevertheless, I still prefer minimal number of columns though.  But hey its your site and everyone here is just to help each other and possibly get ideas along the way.

[Eiolon]

The above query does get rid of the duplicate forum.  The problem that occurs with removing the topic_num_replies is I am doing math in the do-while statement to give me the total posts.  It adds forum_num_topics + topic_num_replies to get the total posts.

 

So if I were to add another column, forum_num_posts, how would I modify my above insert query for adding a reply?  Currently I have it updating the topics table upon a successful insert.  Is it possible to update two tables upon successful insert?

 

Thanks again for all your help on this!

[wsantos]

From my understanding is this what you want to show on that page?

 

| Forum name - Forum Description      |  Number of topics    |  total number of post for the whole forum |

[Eiolon]

Correct, it's actually just like this forum.  On the main page you see the name, desc, topics created in the forum and total posts in the forum.

[wsantos]

Try this if this is what you need.

 

SELECT DISTINCT f.forum_id, f.forum_name, f.forum_desc, f.forum_num_topics, SUM(t.topic_num_replies) FROM forums f JOIN topics t ON (f.forum_id = t.topic_forum) GROUP BY t.topic_forum

[Eiolon]

Thanks very much!  Works like a charm!