CODEWITHSUNDEEP
phpmysqltime
Latox
Latox

Reputation: 4695

Only insert a row if for that day and that user, no row exists already

When someone visits X page for the first time, I insert a new row into the table with the current unix time()stamp.

I want to insert new rows, for that user, every 24 hours.. so for example:

Example A) Bob, goes to my site, it inserts a row.. 12 hours later, Bob comes back, it doesn't insert a new row as 24 hours haven't passed yet.

Example B) Bob, goes to my site, it inserts a row.. 24 hours later, Bob comes back, it DOES insert a new row as 24 hours HAVE passed.

I am toying around with this, but cannot think if this is right or not due to my brain being fried.

$time = time();
$difference = 86400;
$timedifference = $time + $difference;

When inserting the row:

mysql_query("INSERT INTO `logs` (`time`, `who`, `site`, `type`) 
VALUES('" . $timedifference . "', '" . $ip . "', '" . $rid . "', 'out') ") 
or die(mysql_error());

When checking to see if it has been 24 hours or more:

mysql_query("SELECT * FROM `logs` 
WHERE `time` < '" . time() . "' AND `type` = 'out' 
AND `site` = '" . $rid . "' AND `who` = '" . $ip . "'");

Can somebody please tell me if it's right?

Upvotes: 3

Views: 235

Answers (4)

Latox
Latox

Reputation: 4695

Here is what I've come up with.. it seems to work:

//log check
$ip = ip2long($_SERVER['REMOTE_ADDR']);
$time = time(); //current time
$difference = 86400; //one day in seconds
$timedifference = $time + $difference; //time difference
$logQ = mysql_query("SELECT * FROM `logs` WHERE `time` > '" . time() . 
        "' AND `type` = 'out' AND `site` = '" . $id . 
        "' AND `who` = '" . $ip . "'");
$logR = mysql_num_rows($logQ);
if ($logR <= 0){
  mysql_query("INSERT INTO `logs` (`time`, `who`, `site`, `type`) VALUES('" . 
  $timedifference . "', '" . $ip . "', '" . $id . "', 'out') ") or 
  die(mysql_error());  
}

Upvotes: 2

ajreal
ajreal

Reputation: 47321

Try

insert ignore into logs
  select unix_timestamp(now()), who, site, type
  from logs
  where 
  who='{$ip}' and 
  site='{$rid}' and
  type='out' and
  unix_timestamp(time)<=unix_timestamp(now())-86400 limit 1;

And check if there a return affected_rows,
if so, meaning the new log added.

Upvotes: 1

buddhabrot
buddhabrot

Reputation: 1586

In your last query you are not checking whether there is an entry over 24 hours old, you are only checking if there's an entry that is older than NOW.

Correct procedure is:

Create an index on the records for the login time.
SELECT the last record by this ascending index, for a user ('who').
If there is a last, and last is less than 24 hours away from now (time()), then skip creation of a new record.
Otherwise, create one for now (time()).

Upvotes: 0

Mike Caron
Mike Caron

Reputation: 14561

  1. I would insert $time, rather than $timedifference.
  2. You need to check to see whether time is less than time() - 86400. If you made time a datetime column, you could do this directly in the query.

Upvotes: 0

Related Questions