MySQL query to get total Thread Views?

[xlguy]

Hi guys, a quick question for those who know MySQL. I want to get the total number of thread views for an entire forum, within a specific number of days.

So, if I had a forum called 'Computer Chat' and I wanted to know how many times *all* the threads in that forum have been viewed in the last X days.

You already have total threads and total posts for each forum, I'm looking at a way of querying the database to get total thread views.

Hope that makes sense and just looking for a MySQL query I can run from shell/phpmyadmin.

Thanks in advance to anyone that can shed some light

[cheesegrits]

Well, it depends whether you have the 'threadviewslive' option set or not. So to account for either case, you'd need something like:

$totviews = 0;
if ($vbulletin->options['threadviewslive'])
{
        $query = "
                SELECT SUM(views) AS totviews
                FROM " . TABLE_PREFIX . "thread
                WHERE forumid = $forumid
        ";
}
else
{
        $query = "
                SELECT COUNT(*) AS totviews
                FROM " . TABLE_PREFIX . "threadviews AS threadviews
                INNER JOIN " . TABLE_PREFIX . "thread AS thread ON (thread.threadid = threadviews.threadid)
                WHERE thread.forumid = $forumid
        ";           
}
if ($result = $vbulletin->query_first_slave($query))
{
        $totviews = $result['totviews'];
}

This assumes that the forumid you want is in $forumid.

Untested. If the inner join doesn't work try a right join, I always get confused between those two. :erm:

-- hugh

[Marco van Herwaarden]

View information is not permanently stored by date-time, so without logging historic data into a new table, this is not possible.

[cheesegrits]

Ooops, I spaced on the "within a specific number of days" part. As Marco says, the hit data is not timestamped.

As Marco also says, you'd have to log the historic data. A cron job running once a day (or whatever) that pretty much executes the query I gave you, then updates a new table (say 'tothits') with forumid, timestamp and the delta between the current hit value and the previously recorded ones. I think this could be done with a single query, by using an inner SELECT to sum the previously stored deltas for that forum, and subtract it from the current value.

Then your display query would do something like "SELECT SUM(hits) FROM tothits WHERE timestamp > $cutoff", where $cutoff is set to "time() - (60 * 60 * 24 * $numdays)", where $numdays is how far back you want to go.

-- hugh

[xlguy]

Hi Hugh & Marco,

Thanks for the replies.

When viewing the forum listings from the index/home page it's possible to see the total number of threads and total number of posts. I assume this is done by adding up the stats for each thread in a specific forum? In that case, surely it's also possible to total the number of views for each thread in to a grand total?

[cheesegrits]

Yes, and the code I posted should do that. What you can't do is what you specified in your original post, which is "within a specific number of days". The hit data is not timestamped, so there is no way of selecting a date range.

BTW, the code I posted could be modified to do all forums in one query. For live views:

SELECT SUM(views) AS totviews, forumid FROM vb2_thread GROUP BY forumid

... should work. I don't have the other option enabled anywhere, or I'd test it and give you that one.

-- hugh