Weird behavior with mysql_affected_rows() in PHP

Question

I have a table named user_ips to keep track of users in case they delete their cookies or change browser. So anyway, the following code is simple. It updates entries in user_ips that are equal to the user's id and IP. If the query did not update any rows, then it means that IP for that user is not in the table, so it inserts it.

$site->query('UPDATE `user_ips` SET `last_time` = UNIX_TIMESTAMP(), `user_agent` = \''.$this->user_agent.'\' WHERE `ip` = '.$this->ip.' AND `userid` = '.$this->id);
if(mysql_affected_rows() == 0)
{
    $site->query('INSERT INTO `user_ips` SET `userid` = '.$this->id.', `ip` = '.$this->ip.', `first_time` = UNIX_TIMESTAMP(), `last_time` = UNIX_TIMESTAMP(), `user_agent` = \''.$this->user_agent.'\'');
}

The problem is mysql_affected_rows() sometimes returns 0 even if a row with the user's current ID and IP exists. So then the code adds another row to the table with the same IP.

In case you are wondering, $site is mysql class I made for my website and the only query it executes is the one passed to it by query(), and nothing more, so this is not a problem with the class. Oh and the IP is stored as a long IP, so it does not need quotes around it.

Answer 1

I'm directly quoting the PHP documentation here:

*When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.*

So in your case, mysql_affected_rows() will return 0 when UNIX_TIMESTAMP() returns the same value (for example, two requests from the same client in the same second).

Answer 2

To build on slipbull's answer, the simplest way to handle this is perhaps to simply perform a SELECT query to evaluate whether or not an INSERT is necessary. Another solution would be to simply INSERT a record at user creation, as this would guarantee a valid record.

Answer 3

You could just set your primary key to span both userid and ip. This would ensure you get no duplicate entries, but you would have to suppress the error on the insert query if you are not going to add a select to check a record exists.

Answer 4

Another option might be to use the MySQL REPLACE command. http://dev.mysql.com/doc/refman/5.0/en/replace.html. Which will delete the row if it already exists, and then insert the row.

Although that might not be suitable for your precise needs.