Strange behavior with mysql_affected_rows () in PHP

Question

I have a table named user_ipsto track users if they delete their cookies or change their browser. One way or another, the following code is simple. It updates the entries in user_ips, which are equal to the user ID and IP. If the request did not update the rows, this means that the IP for this user is not in the table, so he 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 that mysql_affected_rows () sometimes returns 0, even if there is a string with the current user ID and IP. Thus, the code adds another row to the table with the same IP address.

In case you are interested, $ site is the mysql class that I made for my site, and the only query that it executes is the one passed to it by query (), and nothing else, so this is not a problem with the class . Oh, and the IP is stored as a long IP address, so it doesn't need quotes.

Answer 1

I am directly quoting the PHP documentation here:

When using UPDATE, MySQL will not update columns where the new value matches the old value. This creates the possibility that mysql_affected_rows () cannot actually equal the number of matching rows, but only the number of rows that were literally affected by the query.

, mysql_affected_rows() 0, UNIX_TIMESTAMP() (, ).

Answer 2

slipbull, , , SELECT , INSERT. , .

Answer 3

userid, ip. , , , .

Answer 4

MySQL REPLACE. http://dev.mysql.com/doc/refman/5.0/en/replace.html. , , .

.