Count number of logins per day in PHP Timeclock (PHP Mysql)

Question

What are the mysql codes to count the number of logins per day in PHP Timeclock?

timestamp bigint(14) is used and I have no idea how to separate them by date.

How do I count the number of ROWS per day?

Here's the command to create the table info:

CREATE TABLE info (
  fullname varchar(50) NOT NULL default '',
  `inout` varchar(50) NOT NULL default '',
  timestamp bigint(14) default NULL,
  KEY fullname (fullname)
) ENGINE=MyISAM;

Sorry I'm just a newbie trying to understand php and MySQL.

Anyway, I can add either of the two in info table:
timestamp timestamp default NULL, or logindate date default NULL,

Suppose I have this portion of the code saved in a php file, how can I modify it so date or timestamp is inserted in info table everytime a user logs in?

$time = time();
$hour = gmdate('H',$time);
$min = gmdate('i',$time);
$sec = gmdate('s',$time);
$month = gmdate('m',$time);
$day = gmdate('d',$time);
$year = gmdate('Y',$time);
$tz_stamp = mktime ($hour, $min, $sec, $month, $day, $year);

    if (strtolower($ip_logging) == "yes") {
        $query = "insert into ".$db_prefix."info (fullname, `inout`, timestamp, notes, ipaddress) values ('".$fullname."', '".$inout."', 
                  '".$tz_stamp."', '".$notes."', '".$connecting_ip."')";
    } else {
        $query = "insert into ".$db_prefix."info (fullname, `inout`, timestamp, notes) values ('".$fullname."', '".$inout."', '".$tz_stamp."', 
                  '".$notes."')";
    }

    $result = mysql_query($query);

As a side note, you might want to use an INT as a key (id INT(11) NOT NULL AUTO_INCREMENT) because searches in the future will be a lot faster that way, plus fullname might change? — casraf
– casraf, Commented Dec 29, 2013 at 11:28

casraf · Accepted Answer · 2013-12-29 11:22:56Z

2

Since it's a BIGINT (why, btw?) I assume it's a UNIX Timestamp. I haven't tested this, but something along the lines of this should work:

SELECT DATE(FROM_UNIXTIME(timestamp)) AS date, COUNT(*)
FROM info
GROUP BY date

You might wanna just store the timestamp as a TIMESTAMP column type, and then just use DATE(timestamp) to group by date.

answered Dec 29, 2013 at 11:22

casraf

21.8k10 gold badges60 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

BlitZ Over a year ago

COUNT(id) should be COUNT(*) - there is no column id in this table.

user3143736 Over a year ago

By the way, the codes are from PHP timeclock which is a web-based system. I'm just trying to modify it.

casraf Over a year ago

Well when logging in just run UPDATE info timestamp=UNIX_TIMESTAMP() WHERE fullname="the user's name" of course modify to your needs. Did the answer above work for you? If so please mark as accepted. Thank you!

user3143736 Over a year ago

The answer above almost worked. It's just that instead of showing the date under the timestamp column, several NULLs appear in rows.

user3143736 Over a year ago

It's now working. This command worked: SELECT DATE( from_unixtime( timestamp ) ) AS date, COUNT( * ) FROM info WHERE timestamp BETWEEN 0 AND 9999999999999999999999999999999 GROUP BY DATE( from_unixtime( timestamp ) ) ORDER BY timestamp

|

Gilad Pomer · Accepted Answer · 2013-12-29 11:32:08Z

0

Add a column of date only (without the time) and use this:

SELECT COUNT(*), dateColumn FROM info WHERE fullname='{The user full name}' GROUP BY dateColumn

answered Dec 29, 2013 at 11:32

Gilad Pomer

1854 silver badges7 bronze badges

2 Comments

user3143736 Over a year ago

Hi! I've edited my question. Can you look into the codes. I can't figure out how date can be inserted in the info table everytime a user logs in.

Gilad Pomer Over a year ago

You need to insert new row with the current date each time the user login

Collectives™ on Stack Overflow

Count number of logins per day in PHP Timeclock (PHP Mysql)

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related