Visitor Stats... Forcing Uniquness (IP/Date.Time)

scrupul0us

Im setting up a DB to collect visitor data... i want it to be unique based on a 24 hour period

for giggles lets say i have this stored in my db (completely hypothetical)

127.0.0.1 | Mozilla | NT | 1.8 | 11:30pm

now lets say i refresh the page... what would my query look like such that it would check to see if what im trying to enter is unique by IP and date/time

the time bound would be midnight to midnight, not 24 hours after the first appearance of a given IP address (this way i have accurate daily collection based on my servers timezone)

i already have the data collection taken care of... its just accurately entering it into the DB

scrupul0us

I'm thinking something along the lines of:

INSERT INTO stats
COLUMNS ('IP','BROWSER','OS','VERSION','TIME')
VALUES ('$IP','$BROWSER','$OS','$VERSION','$TIME')
WHERE (TIME < $TIME)
LIMIT 1;

its really just that time equation thats got me stumped...im not sure how to make it compare 'now' with the time of the last recorded entry

scrupul0us

this is driving me nuts... im at least trying to compare the ip address... but that aint working

$query = "
INSERT INTO 
stats (bid,bname,bversion,osname,osversion,type,ip,lang)
VALUES ('$stats[0]','$stats[1]','$stats[2]','$stats[3]','$stats[4]','$stats[5]','$stats[6]','$stats[7]')
WHERE (ip <> '$stats[6]')";

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (ip <> '192.168.1.1')'

scrupul0us

bump

hows this look for pseudo code:

$expire = strtotime("midnight today");
$ip = $_SERVER['REMOTE_HOST'];

//db connect stuff

$result = mysql_query("SELECT ip, UNIX_TIMESTAMP(date) as date FROM stats WHERE ip = $ip");

if (mysql_num_rows($result) == 0)
{
//insert stats
}
else
{
$last = array_pop($result)
     if ($last['date'] > $expire)
     {
     //insert stats
     }
}
mysql_free_result($result);

liquorvicar

AFAIK you can't use INSERT...WHERE.

You could try using REPLACE or INSERT...ON DUPLICATE KEY.