Person Counter Script
[View]
[Reply]
[Top]
Posted by Gamma Cpt Goatrider
On 2008-05-08 17:00:55
|
Ok, so I think I've screwed myself here...I've got a script that I threw together the other day to count how many times certain people walk into a room. Think of it as one of those little silver clicky things that count people at gates, etc (see image here) - it's really just a joke script, we want to see how many times employees enter or loiter in rooms they shouldn't be in, so every time we see a person walk in, we select their name from a dropdown, click submit, and their name is entered into a mysql database row, along side a unix timestamp of the click.
This is all well and good, but now that I've got unix timestamps, I want to make a report of who had the most "clicks" in any given day, but I cant figure out how to make a report using the unix timestamps...I can make it "the past 24 hours" but if someone wants to look for stats for say, May 2nd, I cant figure out a way to do that. Should I convert the timestamps to actual dates, then use regex to sort the days? Grr...
By the way, this is in PHP/MySQL.
--goat
|
RE: Person Counter Script
[View]
[Reply]
[Top]
Posted by Gamma Cpt ranok
On 2008-05-11 12:56:38
|
You can do a mktime to make a timestamp for say noon on May 2nd, and then select all timestamps that are 12 hours before or after that to get all emplyees from that day.
|
RE: Person Counter Script
[View]
[Reply]
[Top]
Posted by Ret. Lambda Gen Goldfish
On 2008-05-15 00:56:31
|
There's a lot of stuff built into mySQL for such purposes. Have a look around:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
You'll find most of these functions are worth having a reasonable knoweldge of cause they seem to be required more often than you'd think...
P
On 2008-05-11 12:56:38, ranok wrote
>You can do a mktime to make a timestamp for say noon on May 2nd, and then select all timestamps that are 12 hours before or after that to get all emplyees from that day.
|
|
RE: Person Counter Script
[View]
[Reply]
[Top]
Posted by Lambda Mar booto
On 2008-05-08 17:49:35
|
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp
--rj
|
Yeah
[View]
[Reply]
[Top]
Posted by Gamma Cpt Goatrider
On 2008-05-08 18:18:12
|
I can get between the unix stamp and date formats (I can do that with PHP after I pull the stamp from the database) - but the main issue I have is how to sort the raw data entries by day, using either the unix timestamp or the regular date format.
--goat
|
Helpful links though...
[View]
[Reply]
[Top]
Posted by Gamma Cpt Goatrider
On 2008-05-08 18:19:52
|
I didn't realize MySQL had so many functions built in!
--goat
|
RE: Helpful links though...
[View]
[Reply]
[Top]
Posted by Delta Tr dopel
On 2008-05-09 22:48:43
|
I don't know PHP so this is psuedo code, but couldnt yuo just do something like this?
$nix_stamp = dateDiff(currentDate, 24, "hours") #i.e. subtract 24 hours from current time and set in unix format
$sql = "select count(*) from tablname where person = 'someperson' and timestamp > " & $nix_timestamp
|
|
|
|
|
|
