SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Generating database statistics based on date

    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:

    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.

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Bengal34 View Post
    The database collumn ''$date" is a varchar of length 20.
    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)
    Code:
    " AND date > '$date_30d'"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks :-) 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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    leaderboard:
    Code:
    SELECT somebody
         , COUNT(*) AS number_of_posts
      FROM sometable
    GROUP
        BY somebody
    ORDER
        BY number_of_posts DESC LIMIT 5
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •