Number of posts in last 30 days...

[dontpanic]

Hi all,

I've been trying to figure out how to get the query written so that I can show the total number of posts in the past 30 days.

This is what I have thus far, which I think should work if the date format was standard MySQL instead of the dateline format.

PHP Code:

// get posts over the past 30 days
$countrecentposts=$DB_site->query_first('SELECT COUNT(*) AS 30recent FROM post WHERE TO_DAYS(NOW()) - TO_DAYS(dateline) <= 30');
$total30recent=number_format($countrecentposts['30recent']); 


And then, I am using the $total30recent in my forums_home template where I want it to appear.

Any ideas on this one?? I know the problem is because the date is not stored in the normal date format...

[Logician]

PHP Code:

$ago= mktime (date("H"), date("i"), date("s"), date("m")-30, date("d"), date("Y"));
$countrecentposts=$DB_site->query_first('SELECT COUNT(*) AS 30recent FROM post WHERE dateline >= $ago'); 
$total30recent=$countrecentposts['30recent']; 


If your DB size is large, this query can be a performance killer so if this is the case add an "index" to your dateline field in post table.

[dontpanic]

Thanks Logician,

Refresh my memory if you will...what benefit would adding the index give me? And would I need to change the code snip you provided if I did this?

Thanks!

[Logician]

dateline field in post table does not have an index by default so if your database is very large and you make queries using that field (like the code above), the query can take long to complete. (say a few seconds which is a long time for a query) If this query is used a lot of times (say used in forum home page), it will incresase your MYSQL work thus slow down the whole site.

To avoid this, you can add an "index" to dateline field of post table. An index in a field will boost queries that uses that field so after you have an index, your query will be completed in miliseconds regardless of your DB size.

You can not add an index in code end. You have to do it in MYSQL. For instance PHPmyadmin can do it for you but if you are not familiar with it, you'd better get someone who knows what he is doing (like your server admin) do it, because there are different types of indexes and believe me you would not want to add a wrong index

[dontpanic]

OK. I know how to add the index using PHPmyAdmin...but since you mentioned there were different types, what type would I want to use here to get the best results?

Thanks again!

[dontpanic]

Logician, thank you very much for your help.

I did try the code you provided and received this error:

Database error in vBulletin 2.3.0:

Invalid SQL: SELECT COUNT(*) AS 30recent FROM post WHERE dateline >= $ago
mysql error: Unknown column '$ago' in 'where clause'

mysql error number: 1054

Back to the drawing board I guess.

[Logician]

Please change ' as " :

$countrecentposts=$DB_site->query_first("SELECT COUNT(*) AS 30recent FROM post WHERE dateline >= $ago");

As for index: If you click "INDEX" link for dateline column in Structure/Action Sections of phpmyadmin, you should be fine..

[dontpanic]

Logician, thank you, it works great. I made one small change becuase the results I was getting with the previous code were "screwy" at best..they did not correlate well to the post count I did manually.

The final code below:

// get posts over the past 30 days
$ago= mktime (date("H")-720, date("i"), date("s"), date("m"), date("d"), date("Y"));
$countrecentposts=$DB_site->query_first("SELECT COUNT(*) AS 30recent FROM post WHERE dateline >= $ago");
$total30recent=$countrecentposts['30recent'];

Thanks again!