[RESOLVED] Daily Time Stamp Program

[mski0687]

Hello all,

I'm trying to build a daily user attendance program to track each day a user logs in.

The problem I'm having is limiting the number of times a user can be added to the database on a certain day (ideally just 1 entry).

I'm thinking if I can pull the last time stamp I could compare it to the current time and if the day matches the current day then the program will just override the last entry. If not then a new entry would be made.

It seems easy in logic, but I'm sure it won't be once I get started. I just have a question on how to compare the last entry of the array and adding a new entry to the array if a new day is needed. Is this possible or worth the time to code it??

Thanks in advance

[bradgrafelman]

Not too sure what you're after here, but it sounds like you could have two columns, user and date (date being just a date - YYYY-MM-DD). You could then add a UNIQUE index on the two columns (one index - two columns, not two separate indexes).

That way, when doing your INSERT queries, you can use the ON DUPLICATE KEY UPDATE... syntax (or just detect the 1062 error number and output an error message, depending upon what it is you're planning to do).

[devinemke]

$result = mysql_query('SELECT login_id FROM login_table WHERE user_id = 123 AND DATE(date_time) = CURDATE()') or exit(mysql_error());
if (mysql_num_rows($result))
{
	$row = mysql_fetch_assoc($result);
	mysql_query('UPDATE login_table SET date_time = NOW() WHERE login_id = ' . $row['login_id']) or exit(mysql_error());
	echo 'you have now updated todays\'s login at ' . date('Y-m-d H:i:s');
}
else
{
	mysql_query('INSERT INTO login_table SET user_id = 123, date_time = NOW()') or exit(mysql_error());
	echo 'you have logged in for the first time today at ' . date('Y-m-d H:i:s');
}

[mski0687]

Thanks for the reply,

I'm trying to build an attendance program that the user wouldn't see. Just something to log each day a user was on that I could export to an excel sheet when they go trough a log on..

I'll see if I can find sometime today to mess around with your idea...

[bradgrafelman]

devinemke's suggestion breaks the steps down into separate queries and would be the workaround if your SQL server doesn't support some type of 'ON DUPLICATE KEY UPDATE' type of syntax.

If it does, I would recommend you use it; it reduces three queries (and the associated PHP code) down to just one.

[mski0687]

Thanks for the help!

I'll see if I can get something going!

[Joseph_Sliker]

Hi;

I just did something like this myself a few days ago.

I created a table with 3 fields:

CREATE TABLE LOGRECORDS (\n LOGINID int(10) NOT NULL auto_increment COMMENT 'Primay key',\n USER varchar(25) NOT NULL,\n LOGGEDIN timestamp NOT NULL default CURRENT_TIMESTAMP,\n PRIMARY KEY (LOGINID)\n) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

When the user logs in, the table gets a new record that includes the username and is automatically timestamped.

Then, I can query this table in any way I like to view the patterns of use.

ie.

SELECT DATE_FORMAT(LOGGEDIN,'%a, %m/%e/%Y') AS LOGDATE, USER, COUNT(LOGINID) AS LOGSTODAY FROM LOGRECORDS GROUP BY LOGDATE, USER ORDER BY LOGGEDIN, USER

This will give me a recordset that looks something like this:

LOGDATE USER LOGSTODAY
Thu, 06/11/2009 llikirjm 2
Thu, 06/11/2009 rhillivr 2
Thu, 06/11/2009 niilijy 1
Thu, 06/11/2009 hilin 2
Fri, 06/12/2009 lLIKiRJM 2
Fri, 06/12/2009 dukilk 4
Fri, 06/12/2009 hilminjl 1
Fri, 06/12/2009 kriulm 2
Fri, 06/12/2009 rriiwim1 3
Fri, 06/12/2009 kohnkidx 1
Fri, 06/12/2009 browndl1 1

The result of the query shows only one row for each user per day (since it is grouped on USER) but it preserves the information on how many times the user visited that day. Since the LOGGEDIN field is a complete date and time stamp, it could also be used for analysis of what times of day or days of the week or month your site is most used.

[mski0687]

Wow! That's exactly what I was looking to do.

Thanks!