Get topic count from topic category - MySql

[eevan79]

I'm working on small forum script and want to show number of Topics and Replies for certain category (forum).

 

Here is my table topics

Now, topic_cat is linked with category ID.

I want to display number of all topics for all category. Currently my script show total topics for all forums, but I want to display separated for each forum.

 

Forum 1 | Topics| Replies |

Forum 2 | Topics| Replies |

...

Here is demo script http://avs-demo.iz.rs/testforum

 

Username: test

pass: test

 

and here is (now) my messy script

<?php
//create_cat.php
include 'connect.php';
include 'header.php';

$sql = "SELECT categories.cat_id, categories.cat_name, categories.cat_description,
        COUNT(topics.topic_id) AS topics FROM categories LEFT JOIN topics
        ON topics.topic_id = categories.cat_id
        GROUP BY categories.cat_id, categories.cat_name, categories.cat_description
        ORDER BY categories.cat_id ASC";
$result = mysql_query($sql);
$result1 = mysql_query("SELECT topic_id FROM topics") or die(mysql_error());   
$num_topics = mysql_num_rows($result1);    
if(!$result)
{
    echo $l_cat_cant_display;
}
else
{
    if(mysql_num_rows($result) == 0)
    {
        echo $l_no_cat;
    }
    else
    {
        //prepare the table
        echo '<table border="1">
              <tr>
                <td class="smallTable"></th>
                <td class="leftpart2"><b>'. $l_category. '</td>
                <td class="smallTable"><b>'. $l_topics. '</td>
                <td class="leftpart2"><b>'. $l_last_topic. '</td>
              </tr>';    
            
        while($row = mysql_fetch_assoc($result))
        {                
            echo '<tr>';
                echo '<td><img src="img/forum_icon.png"></td><td class="leftpart">';
                    echo '<h3><a href="category.php?id=' . $row['cat_id'] . '">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'];
                echo '</td>';
                
                //fetch last topic for each cat
                    $topicsql = "SELECT
                    topic_id, topic_subject, topic_date, topic_cat FROM topics
                    WHERE topic_cat = " . $row['cat_id'] . "
                    ORDER BY topic_date DESC LIMIT 1";    
                    $topicsresult = mysql_query($topicsql);            
                    if(!$topicsresult)
                    {
                        echo $l_last_topic_error;
                    }
                    else
                    {
                        if(mysql_num_rows($topicsresult) == 0)
                        {
                            echo '<td><div align="center">-</div></td>';
                        }
                        else 
                        {

echo '<td><div align="center">' . $num_topics . '</div></td>';    
$result2 = mysql_query("SELECT reply FROM topics WHERE topic_cat =' . $tid . '") or die(mysql_error());   
$num_replies = mysql_num_rows($result2);    
                            while($topicrow = mysql_fetch_assoc($topicsresult))
                            echo '<td><a href="topic.php?id=' . $topicrow['topic_id'] . '">' . $topicrow['topic_subject'] . '</a> <br /> ' . date('d m Y, H:i', strtotime($topicrow['topic_date'])) .'</div>';
                        }                
                    }
                echo '</td>';
            echo '</tr>';
        }
    }
}

include 'footer.php';
?>

As you can see I tried with $num_replies to get all posts.

 

Can someone help?Thanks

[Jax2]

try something like this?

 

$sql="SELECT * FROM table WHERE top_cat=(insert cat id here)";
$result=mysql_query($sql, $db);
$total=mysql_num_rows($result);
echo $total; <-- should display the total # of topics in that cat ....

[eevan79]

I get following warring:

Warning:  mysql_query(): supplied argument is not a valid MySQL-Link resource in..

 

$sql='SELECT * FROM table WHERE topic_cat=' . $row['cat_id'] . '';

$result=mysql_query($sql, $db);

$total=mysql_num_rows($result);

echo 'debug: '.$total;   

[Jax2]

You need to rewrite your query statement then to how you normally write them... Sorry, I do my queries a bit different than a lot of people. I have global include for $db which is my connection. If you are already connected, you should be able to simply use:

 

mysql_query(" <insert query> ");

 

instead of the $sql and $result ...

 

Give it a shot, just write your query  out how you normally would and use mysql_num_rows to find the number of records in cat_top 1...etc.

 

 

[eevan79]

I know. I use this query statement cause of testing. Basic script if different.

Usually I use mysql_query("query "); but this time is used because of "debug".

In the above script is a lot of unnecessary code.

 

I work on this all day and cant make it to work...

[eevan79]

cant edit post...

 

anyway I fixed script:

<?php
//create_cat.php
include 'connect.php';
include 'header.php';
$result = mysql_query("SELECT categories.cat_id, categories.cat_name, categories.cat_description, COUNT(topics.topic_id) AS topics FROM categories LEFT JOIN topics ON topics.topic_id = categories.cat_id
GROUP BY categories.cat_id, categories.cat_name, categories.cat_description ORDER BY categories.cat_id ASC");
        
$result1 = mysql_query("SELECT topic_id FROM topics") or die(mysql_error());   
$num_topics = mysql_num_rows($result1);    

if(!$result)
{
echo $l_cat_cant_display;
}
else
{
if(mysql_num_rows($result) == 0)
    {
    echo $l_no_cat;
    }
    else
    {
//prepare the table
echo '<table border="1">
<tr>
<td class="smallTable"></th>
<td class="leftpart2"><b>'. $l_category. '</td>
<td class="smallTable"><b>'. $l_topics. '</td>
<td class="leftpart2"><b>'. $l_last_topic. '</td>
</tr>';        
while($row = mysql_fetch_assoc($result))
{        
echo '<tr>';
echo '<td><img src="img/forum_icon.png"></td><td class="leftpart">';
echo '<h3><a href="category.php?id=' . $row['cat_id'] . '">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'];
echo '</td>';
//fetch last topic for each cat
$topicsresult = mysql_query("SELECT * FROM topics WHERE topic_cat = " . $row['cat_id'] . " ORDER BY topic_id DESC LIMIT 0,1");
$last_topic = mysql_query("SELECT * FROM posts ORDER BY post_date DESC LIMIT 0,1");    
$postrow = mysql_fetch_assoc($last_topic);

$show_last_poster = mysql_query("SELECT user_name FROM users WHERE user_id=". $postrow['post_by'] . " " );
$username = mysql_fetch_assoc($show_last_poster);
if(!$topicsresult)
{ echo $l_last_topic_error;
} else
{
if(mysql_num_rows($topicsresult) == 0)
{ echo '<td><div align="center">-</div></td>';
}
else 
{
echo '<td><div align="center">' . $num_topics . '</div></td>';    
$result2 = mysql_query("SELECT reply FROM topics WHERE topic_cat =' . $tid . '") or die(mysql_error());   
$num_replies = mysql_num_rows($result2);    
while($topicrow = mysql_fetch_assoc($topicsresult))
echo '<td><b><a href="topic.php?id=' . $topicrow['topic_id'] . '" class="subjectlink">' . $topicrow['topic_subject'] . '</a><br />Last post by </b>' . $username['user_name'] . '<br />on ' . date('d m Y, H:i', strtotime($postrow['post_date'])) .'</div>';
  }                
}
   echo '</td>';
echo '</tr>';
        }
    }
}
include 'footer.php';
?>

But now I have this index structure:

Forum1 | Topics | Last post (subject, username, post date) |

Forum2 | Topics | Last post (subject, username, post date) |

Forum3 | Topics | Last post (subject, username, post date) |

 

etc...

With this script I always get date, username, numb. topics same for all forums.

 

At the end of script I have mysql_fetch_assoc($topicsresult))

while "fetch is in progress " tables are created. But how to fetch more rows at this loop -

$show_last_poster = mysql_query("SELECT user_name FROM users WHERE user_id=". $postrow['post_by'] . " " );
$username = mysql_fetch_assoc($show_last_poster);

This shows user who last posted (but he is same for all forums and also date and topics). This structure is not good.

 

How to fix it?

 

I have following tables:

Categories

-cat_id

-cat_name

-cat_description

Posts

-post_id

-post_content

-post_date

-post_topic

-post_by (IDs of users)

Topics

-topic_id

-topic_subject

-topic_date

-topic_cat

-topic_by (and view and reply)

Users (id, name/pass, date, email, level)

 

Now I'm stuck here...with this last topics, dates, users on forum view (index)