PHP中mysql_affected_rows（）的奇怪行为

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).