[SOLVED] counting posts and topics for forum

[runnerjp]

hey guys... how would i cound number of topics for my forum thread...im gathering by the froum its in so for example

 

WHERE forum = 'general'

 

postid forum author title                           post               showtime realtime  lastposter   numreplies  parentid   lastrepliedto

134 general  Admin test 1                       test me         212279922 1212279922 Admin         0         0           1212279922

132       Admin                     test 2       121227543412  12275434   Admin       0     130               0

130 general  Admin welcome to forum welcome     1212275218  1212275218 Admin           2           0             1212275434

131       Admin                   hahah changed    1212275426 1212275426 Admin           0         130                  0

 

 

as you can see this is how ,y db is set out  so if someone replys to a post then parentid is filed in with the postid of that posrson

 

but i caqn of the life of me figure out how i can get 

 

number of posts within the forum and number of threads :S

[paulman888888]

use

 tags please.
Your information is really messy try useing tables
[table][tr]hope ask again[/tr][/table]

[joquius]

First of all it's good practice to have a topic/post number field in each table, updated after each post to have less queries.

 

For the moment, assuming the topics table name is "topics"

 

$counts = mysql_fetch_array (mysql_query ("SELECT COUNT(`postid`) as count, SUM(`numreplies`) as sum FROM `topics` WHERE `forum` = 'general'"));

 

$counts['count']; // topic num

$counts['count'] + $counts['sum']; // posts (replies + first post in topic)

[runnerjp]

i tired $counts = mysql_fetch_array (mysql_query ("SELECT COUNT(`title`) as count, SUM(`id`) as sum FROM `forumtutorial_posts` WHERE `forum` = 'general'"));

 

but i got Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/runningp/public_html/members/test2.php on line 4

[joquius]

I think your fields are wrong, use

 

mysql_query ("...") or die (mysql_error ());

[runnerjp]

humm ok i did this

 

<?php

include '../settings.php';

$counts = mysql_query ("SELECT COUNT(`title`) as count, SUM(`postid`) as sum FROM `forumtutorial_posts` WHERE `forum` = 'general'")or die (mysql_error ());
$topic = $counts['count']; // topic num
$posts =$counts['count'] + $counts['sum']; // posts (replies + first post in topic)

echo $topic;
echo $posts;


?>

 

and all i get is 0

[joquius]

Is this a topic table or do only have one table for everything?

 

Is forumtutorial_posts the only table?

 

Apart from that the SUM() needs to be on the numreplies not the id..

[trq]

You never fetch the records from the $count array. Your also using reserved words as your aliases.

 

<?php

include '../settings.php';

if ($result = mysql_query ("SELECT COUNT(title) as cnt, SUM(postid) as sm FROM forumtutorial_posts WHERE `forum` = 'general'")) {
 if (mysql_num_rows($result)) {
   $row = mysql_fetch_assoc($result);
   $topic = $row['cnt'];
   $posts =$row['cnt'] + $row['sm'];
   echo $topic;
   echo $posts;
 }
}

?>

[whizard]

you need to do something like this

 

<?php

include '../settings.php';

$result = mysql_query ("SELECT COUNT(`title`) as count, SUM(`postid`) as sum FROM `forumtutorial_posts` WHERE `forum` = 'general'")or die (mysql_error ());
$counts = mysql_fetch_array($result);
$topic = $counts['count']; // topic num
$posts =$counts['count'] + $counts['sum']; // posts (replies + first post in topic)

echo $topic;
echo $posts;


?>

 

HTH

Dan

[runnerjp]

ahh yes that worked a treat... ty