An usual solution to the problem is something like this.
Code: Perl
my $user_id = 1000;
my $ip = $ENV{'REMOTE_ADDR'};
my $stmt = $db->prepare('SELECT * FROM visits WHERE user_id=?');
$stmt->execute($user_id);
if($stmt->rows) ## a entry for the page id exists, update the record
{
my $update_stmt = $db->prepare('UPDATE visits SET ip = ? WHERE user_id=?');
$update_stmt->execute($ip,$user_id);
}
else ## no such id, insert a new entry with count 1
{
my $insert_stmt = $db->prepare('INSERT INTO visits VALUES(?,?)');
$insert_stmt->execute($user_id,$ip);
}
Code: PHP
$user_id = 1000;
$ip = $_SERVER['REMOTE_ADDR'];
$res = mysql_query("SELECT * FROM visits WHERE user_id=$user_id");
if(mysql_num_rows($res)) // a entry for the page id exists, update the record
{
$result = mysql_query("UPDATE visits SET ip = '$ip' WHERE user_id=$user_id");
}
else // no such id, insert a new entry with count 1
{
$result = mysql_query("INSERT INTO visits VALUES('$user_id','$ip')");
}
Enter: REPLACE
The REPLACE statement very similarly or exactly like INSERT statment, except for the fact that in case a record with the same primary key exists in the one that's going to be inserted then the old row is deleted before the new one is inserted. You need to have an unique index or a primary key to use REPLACE statment.
REPLACE is not part of the SQL standard, it's an useful extension by MySQL, I guess there are many other RDBMSs which have come up with similar extensions.
So, now that we have REPLACE, we can rewrite above code snippets in the following manner:
Code: Perl
my $user_id = 1000;
my $ip = $ENV{'REMOTE_ADDR'};
my $stmt = $db->prepare('REPLACE INTO visits VALUES(?,?)');
$stmt->execute($user_id,$ip);
Code: PHP
$user_id = 1000;
$ip = $_SERVER['REMOTE_ADDR'];
$res = mysql_query("REPLACE INTO visits VALUES('$user_id','$ip')");
That's it, isn't that easier guys!!
Read more about REPLACE statement here, http://dev.mysql.com/doc/refman/5.0/en/replace.html
Alternative Approach
A forgotten or unknown feature among the masses is the ON DUPLICATE KEY UPDATE, it causes an UPDATE when a duplicate key error is encountered during an insert. The above code snippet again be re-written like this,
Code: Perl
my $user_id = 1000;
my $ip = $ENV{'REMOTE_ADDR'};
my $stmt = $db->prepare('INSERT INTO visits VALUES (?,?) ON DUPLICATE KEY UPDATE ip=?');
$stmt>execute($user_id,$ip,$ip);
Code: PHP
$user_id = 1000;
$ip = $_SERVER['REMOTE_ADDR'];
$res = mysql_query("INSERT INTO visits VALUES ('$user_id','$ip') ON DUPLICATE KEY UPDATE ip='$ip'");
You can read more about this feature here, http://dev.mysql.com/doc/refman/5.0/...duplicate.html
Posts
