SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count of users who authenticated this month

    I'm trying to get a count of users who've authenticated to my site in the previous 30 days (this month).

    I think I have a query to do the job, but the numbers look too good to be true.

    Can you look at my query and see if you see any issues?
    Code MySQL:
    mysql> SELECT count(*) FROM users WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= access;
    +----------+
    | count(*) |
    +----------+
    |   158551 | 
    +----------+
    1 row in set (0.72 sec)
     
    mysql> SELECT count(*) FROM users;
    +----------+
    | count(*) |
    +----------+
    |   167602 | 
    +----------+
    1 row in set (0.37 sec)
     
    mysql> describe users;
    +------------------+------------------+------+-----+---------+----------------+
    | Field            | Type             | Null | Key | Default | Extra          |
    +------------------+------------------+------+-----+---------+----------------+
    | uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
    | name             | varchar(60)      | NO   | UNI |         |                | 
    | pass             | varchar(32)      | NO   |     |         |                | 
    | mail             | varchar(64)      | YES  | MUL |         |                | 
    | mode             | tinyint(1)       | NO   |     | 0       |                | 
    | sort             | tinyint(1)       | YES  |     | 0       |                | 
    | threshold        | tinyint(1)       | YES  |     | 0       |                | 
    | theme            | varchar(255)     | NO   |     |         |                | 
    | signature        | varchar(255)     | NO   |     |         |                | 
    | created          | int(11)          | NO   | MUL | 0       |                | 
    | access           | int(11)          | NO   | MUL | 0       |                | 
    | status           | tinyint(4)       | NO   |     | 0       |                | 
    | timezone         | varchar(8)       | YES  |     | NULL    |                | 
    | language         | varchar(12)      | NO   |     |         |                | 
    | picture          | varchar(255)     | NO   |     |         |                | 
    | init             | varchar(64)      | YES  |     |         |                | 
    | data             | longtext         | YES  |     | NULL    |                | 
    | login            | int(11)          | NO   |     | 0       |                | 
    | timezone_name    | varchar(50)      | NO   |     |         |                | 
    | signature_format | smallint(6)      | NO   |     | 0       |                | 
    +------------------+------------------+------+-----+---------+----------------+
    20 rows in set (0.00 sec)
     
    mysql> select FROM_UNIXTIME(access) from users where uid = 3;
    +-----------------------+
    | FROM_UNIXTIME(access) |
    +-----------------------+
    | 2009-11-20 11:36:45   | 
    +-----------------------+
    1 row in set (0.00 sec)
     
    mysql>
    Last edited by kmillecam; Nov 20, 2009 at 10:59. Reason: Added MySQL formatting

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Looks fine to me. You should know if the numbers are right

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I find it hard to believe that 95&#37; of my registered users have logged into the site in the past 30 days.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the numbers are wrong

    thanks for providing the table layout, because by itself, the query ~seems~ to be okay
    Code:
    WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= access
    however, now consider this valuable clue --
    Code:
    access INT(11)
    try this instead --
    Code:
    WHERE UNIX_TIMESTAMP(
          DATE_SUB(CURDATE(),INTERVAL 30 DAY)
               ) <= access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ahhh, a date stored in a non-date column...

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect!
    Thanks!

    Code MySQL:
    mysql> SELECT count(*)
        -> FROM users
        -> WHERE UNIX_TIMESTAMP(
        -> DATE_SUB(CURDATE(),INTERVAL 30 DAY)
        -> )<= access;
    +----------+
    | count(*) |
    +----------+
    |     7940 | 
    +----------+
    1 row in set (0.02 sec)

    mysql>


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
  •