Member Avatar for BleepyE

Hey Guys,

Ive got a MyBB forum and im looking at making a plugin to see what day had the most posts. In theory ive worked out how to do it, but I cant put it down in code.

In the MyBB stats table (mybb_stats), I have a dateline (unix timestamp) column and a posts column. The posts column is a running total of all posts made. A new row is added every 24 hours.

I need to work out which day had the most posts by subtracting the current post count by the post count of the previous day, and then that value subtracted by the previous day post count etc etc so I have daily post counts instead of a running total.

All I need to do then is find the largest number and return the dateline.

The section in bold italics is the part im stuck on. Im not sure how I should be attempting this, I'm pritty sure I require a new table to store all the daily post counts which I can easily create. Its just the subtracting to get the running total of post count down to the daily post count.

If anyone has any information that could be of help, it would be much appreciated.
Thank you

  • 5 Contributors
  • 7 Replies
  • 136 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by cereal
Member Avatar for cereal

If you can make an array with unix timestamp as key and post numbers as value you can do this:

<?php

$a = array(
	'1322828101' => '500',
	'1322827133' => '450',
	'1322826151' => '360',
	'1322825391' => '200',
	'1322824530' => '180',
	'1322823000' => '100'
);

foreach($a as $key => $value)
{
	echo current($a) - next($a);
}

?>

bye.

Member Avatar for pzuurveen

You can do some thing like that in mysql

SELECT count(), date(rowname,'unixepoch') as datum FROM tablename WHERE 1 GROUP BY datum
Member Avatar for pritaeas

If you show your table structure, I am sure someone can give you a query that works.

Member Avatar for diafol

I'm sure you don't need to do this. You can query per day to start with and save that to a new table. Then set up a crontab to query at midnight every day to check the previous day's number of posts. That should then do everything you need. No subtractions, no intensive sql on thousands of posts EVERY time you want a post per day list.

Member Avatar for BleepyE

Hi, sorry for the delayed reply

@pritaeas Sorry I should have included this: [img]http://imgcrave.com/u/WbIXt.jpg[/img]

@ardav How would I go about doing that? The process would only happen once a day, the only time where more than a regular amount of actions would be performed would be at installation when all previous days post counts are calculated.

@cereal Im struggling to see how I could fetch the data from the sql table and format it in such a way.

Member Avatar for diafol

1. create new table (thedate, thecount).
get grouped output from existing table:

SELECT thedate, COUNT(*) AS thecount FROM mytable GROUP BY thedate

save data to an array:

$vals="";
while($data=mysql_fetch_array($result)){
  $vals .= "({$data['thedate']},{$data['thecount'})";
}
$q = mysql_query("INSERT INTO newtable thedate,thecount VALUES($vals)");

Or something like that - I'm rushing at the mo - have to get to work.


THat should populate your table up to this point in time. Perhpas you'll need to delete today's data before your run the cronjob for the first time.

Now set up the cronjob (a php file with connection details and SQL statement) to go at midnight every day.

using

$date = date("Y-m-d");

1. get number of posts for today
2. inseert a row into the newtable.

Edited by diafol because: n/a
Member Avatar for cereal

@cereal Im struggling to see how I could fetch the data from the sql table and format it in such a way.

<?php
# ...
# mysql connection
# ...

$q = mysql_query("select dateline, posts from mybb_stats limit 30 order by dateline desc"); # last 30 days

$arr = array();
while($row=mysql_fetch_object($q))
{
    $arr[$row->dateline] = $row->posts;
}

foreach($arr as $key)
{
	echo current($arr) - next($arr);
}

?>

bye :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.