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.
// 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.
$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?