Generating database statistics based on date

Databases
1

What I am trying to accomplish is to have a page on my site where administrators would have access to some statistics about what actions are being performed on the site.

Say I have four post types and I want to view the number of posts made in increments of time (with one month, one quarter, one year, two year, and lifetime).

Due to posts being added and removed periodically I decided to create a separate table to hold the date, action, type, userId and IP address. What I plan to do is to create a query to count the number of rows where the action and type match a specific criteria. This works all well and good, but when it comes to limiting that over a specific time span is where I’m having issues.

The code:


// Retrieve current date
$today = time();

// Create dates
$date_30d = $today - (60*60*24*30);
...
$date_2y = $today - (60*60*24*365*2);

// Assemble queries
$q_table1 = "SELECT * FROM metrics WHERE action = 'post' AND type = 'type1'";
...

// Calculate Lifetime totals
$c_l_type1 = mysql_num_rows(mysql_query($q_table1));
...

// Calculate 30 day totals
$c_l_type1 = mysql_num_rows(mysql_query($q_table1 . " AND date > $date_30d"));
...

I’m thinking that this should work, but it isn’t. What I think that the issue might be is how I’m creating my date stamps initially. The database collumn ‘’$date" is a varchar of length 20.


putenv("TZ=US/Eastern");
$date = date("m/d/y");

I believe that what I am doing is probably correct but information that I’m using to do the compare isn’t completely accurate.

Any advice on this matter?

-Jason

2

there’s your problem right there

with this datatype, 12/31/2009 comes after 11/30/2010

to fix your problem, you need to do two things

first, change the column to DATETIME (you may have to re-populate the table to get the values into that column properly, as you must specify the values in year-month-day sequence)

secondly, make sure you use quotes around your date strings (which again must be in year-month-day sequence)

" AND date > [COLOR="Red"][B]'[/B][/COLOR]$date_30d[COLOR="Red"][B]'[/B][/COLOR]"
3

Thanks :slight_smile: That did the trick. I also had to work with the formats of the dates I was using.

What about if I wanted to do a leaderboard? The leaderboard would probably use the data found in the 90day range and display the top five users to create posts.

Any advice on how to get started on that?

4

leaderboard:

SELECT somebody
     , COUNT(*) AS number_of_posts
  FROM sometable
GROUP
    BY somebody
ORDER
    BY number_of_posts DESC LIMIT 5