Trying to force uniqueness when inserting

[scrupul0us]

I'm tryin to make a table that will hold browser stats... i want to be unique based on a midnight to midnight period and only add one record per IP address on any given day

 

so far i have this:

 

$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]')";

 

but it fails on the where condition... and that only checks for the IP address...  i have NO idea how to have it check for date time.. i will add that the column that i use to track time i a mysql timestamp type that is set to "update timestamp or update"

 

any ideas?

[benjaminbeazy]

you cant use insert with where, where is a conditional statement, insert creates a new row, are you trying to update a row, if so use

 

 

$sql = "UPDATE `stats` SET `bid`='$stats[0]', `bname`='$stats[1]', `bversion`='$stats[2]', `osname`='$stats[3]', `osversion`='$stats[4]', `type`='$stats[5]', `ip`='$stats[6]', `lang`='$stats[7]' WHERE `ip` <> '$stats[6]'";
$result = mysql_query($sql) or die(mysql_error());

 

 

[btherl]

If you want to keep only the first record for each ip each day, do a select before your insert.  If the select matches a row, don't do the insert.  That's basically the way to do it

 

If you're worried about efficiency, you could set a cookie or a session variable that lets you remember that you've already recorded that user that day.  The variable could record the date and time of the last record inserted.  Then you can check if the last record was inserted on the same day or not.

[scrupul0us]

If you want to keep only the first record for each ip each day, do a select before your insert.  If the select matches a row, don't do the insert.  That's basically the way to do it

 

If you're worried about efficiency, you could set a cookie or a session variable that lets you remember that you've already recorded that user that day.  The variable could record the date and time of the last record inserted.  Then you can check if the last record was inserted on the same day or not.

 

thanks man... thats what ill end up doing... time to break out my trusty notebook and write some pseudo code

[scrupul0us]

hows this look:

 

$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);

 

[fenway]

Actually you can use INSERT ... ON DUPLICATE KEY UPDATE...