Count New posts in one forum?

[Taurine]

Is there any way to count new posts in one forum and have it displayed as a number?

I'm trying to set up a new control block for the officers of in my Guild and I wanted to have it displayed similar to the welcome block for vbadvanced.

New News:2
New Applicants:5
etc......

But I need to be able to count the posts in individual forums.... Any help would be great.

[Lynne]

Sure you could. If you do it the easy way and just add a query in a plugin. If you are only displaying it on one page for the user, then that is probably not a big deal, but if you diplay it in the postbit (showthread) then that means one query added per postbit on each showthread. Default number of posts per thread is 20 (I think), so that is 20 extra queries per page. Not good. What you really should do is write a plugin that update a column in the user table (or another new table) for the forums you want to keep track of. You'd have to update it when posts are made, posts are deleted, posts are moved out of the forums, posts are merged, etc. But, if you don't want it in the postbit and only on a single page, writing a plugin with one query is probably not a big deal. If this is for a vbadvanced page though, you should probably ask over there for help cuz they are better able to suggest hook locations and template modifications.

[Taurine]

It is for vbadvanced, but I have gotten no response on my question there so I figured I'd ask here.

A query was the way i figured I'd go, but I'm having trouble with that as it's a very weak point on my limited coding knowledge. I was hoping that using a modified version of the default vbadvanced one that gets the total new posts would do the trick. I think it would be as simple as defining a forumid...... but thats where i'm lost.

Here is the current query for getting a total count of all the forums:

PHP Code:

    // New posts
    if ($mod_options['portal_welcome_newposts'])
    {
        if (strlen($vbulletin->session->vars['newposts']) > 0 AND !$vbulletin->options['threadmarking'])
        {
            $newposts = number_format($vbulletin->session->vars['newposts']);
        }
        else
        {
            $getnewposts = $db->query_first("
                SELECT COUNT(*) AS count
                FROM " . TABLE_PREFIX . "post AS post
                " . iif($vbulletin->options['threadmarking'],
                    'LEFT JOIN ' . TABLE_PREFIX . 'threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = ' . $vbulletin->userinfo['userid'] . ')') . "
                WHERE dateline >= " . $vbulletin->userinfo['lastvisit'] .
                    iif($vbulletin->options['threadmarking'],
                        ' AND dateline > IF(threadread.readtime IS NULL, ' . (TIMENOW - ($vbulletin->options['markinglimit'] * 86400)) . ', threadread.readtime)') . "
                    AND visible = 1
            ");

            if (!$vbulletin->options['threadmarking'])
            {
                $db->query_write("UPDATE " . TABLE_PREFIX . "session SET newposts = '$getnewposts[count]' WHERE userid = " . $vbulletin->userinfo['userid']);
            }

            $newposts = vb_number_format($getnewposts['count']); 


I figured by changing the variable $newpost to $newnews and $newapps etc..... I could use the same query for each of the different outputs I wanted.

The only other thing I was thinking was getting it so it just counted new threads instead of posts, but i'd be happy with either.

Any help would be great!

--------------- Added [DATE]1227576934[/DATE] at [TIME]1227576934[/TIME] ---------------

I should add that I have the template written out, the queries are the only spot I'm stuck on right now.

[Taurine]

Anyone have any ideas?

[Lynne]

Why are you updating the session table? You realize that gets wiped shortly after the user leaves the site. Also, there is no column newposts in the session table (unless you added it?), so that query is not going to do anything.

[Taurine]

Originally Posted by Lynne

Why are you updating the session table? You realize that gets wiped shortly after the user leaves the site. Also, there is no column newposts in the session table (unless you added it?), so that query is not going to do anything.

That's the default query for vbadvanced to get the new post count since your last visit. the problem i'm having is that it counts all new posts, and I need ones in just one forum. I tried adding a forumid condition but with no success.

[Lynne]

Post what you tried to change it to (with the forumid) and maybe we can spot the error.

[Taurine]

Code I tried:

PHP Code:


                {
            $getnewapps = $db->query_first("
                SELECT COUNT(*) AS count
                FROM " . TABLE_PREFIX . "post AS post
                " . iif($vbulletin->options['threadmarking'],
                    'LEFT JOIN ' . TABLE_PREFIX . 'threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = ' . $vbulletin->userinfo['userid'] . ')') . "
                WHERE forumid = 38 AND dateline >= " . $vbulletin->userinfo['lastvisit'] .
                    iif($vbulletin->options['threadmarking'],
                        ' AND dateline > IF(threadread.readtime IS NULL, ' . (TIMENOW - ($vbulletin->options['markinglimit'] * 86400)) . ', threadread.readtime)') . "
                    AND visible = 1
            ");

            if (!$vbulletin->options['threadmarking'])
            {
                $db->query_write("UPDATE " . TABLE_PREFIX . "session SET newposts = '$getnewapps[count]' WHERE userid = " . $vbulletin->userinfo['userid']);
            }

            $newapps = vb_number_format($getnewapps['count']);
        } 


Database error in vBulletin 3.7.2:

Invalid SQL:

SELECT COUNT(*) AS count
FROM imperialpost AS post

WHERE forumid = 38 AND dateline >= 1228313329
AND visible = 1;

MySQL Error : Unknown column 'forumid' in 'where clause'
Error Number : 1054
Request Date : Wednesday, December 3rd 2008 @ 07:42:31 PM
Error Date : Wednesday, December 3rd 2008 @ 07:42:31 PM
Script : http://www.fooguild.com/
Referrer : http://www.fooguild.com/forums/forumdisplay.php?f=38
IP Address : ...............
Username : ...................
Classname : vB_Database
MySQL Version : 5.0.51a-community-log

[Lynne]

That's because there is no column forumid in either the post table or the threadread table. You will need to do a JOIN between the post table and the thread table (on thread.threadid = post.threadid) in order to then use thread.forumid in your where statement.

[Taurine]

Working now, thanks!