a question about mysql_affected_rows()

Hi all,
I want to store unique data about my page visit. This is done using
cookie value. In my database, the page name and cookie value(ip
address) are both primary keys. This ensures unique entry. The
following code works fine:
$query = "INSERT INTO counter SET page='$currentfile',
uid='$cookie_val',
referer='$ref', count = 1, accesstime='$fileatime' ON
DUPLICATE KEY
UPDATE referer='$ref', count=count+1,
accesstime='$fileatime'";
$result = mysql_query($query, $link) or die("Could not insert");

But I'd also like to know how do I achieve the same effect using
mysql_affected_rows() function. I tried doing as follows but it
doesn't seem to work:
if (mysql_affected_rows($link) < 1) {
$query = "INSERT INTO counter VALUES ('$currentfile', '$cookie_val',
'$ref', 1, '$fileatime')";
$result = mysql_query($query, $link) or die("Could not
insert");
}
else {
mysql_query("UPDATE counter SET count=count+1, referer=$ref,
accesstime=$fileatime WHERE page=$currentfile AND
uid=$cookie_val",
$link);
}
As you can see, if there are no rows, I'd like to "insert", if not,
"update". Problem here is, the code somehow does not go inside else
block. And I echoed the mysql_affected_rows()'s value. It gives me -1.
What am I doing wrong? How do I fix it?

I am using php version 5.0.1 and mysql version 5.0.0-alpha in WinXP.

Thanx!
Ben

re: a question about mysql_affected_rows()

Hi everybody,

this is a very common newbie problem. The nature of numerous problems
requires inserting a new record into a table if it does not exists and
yet updating it if there is already one. As of MySql 4.1.0 it is
possible to render the following query:

INSERT INTO statistics (counter) VALUES (1) ON DUPLICATE KEY UPDATE
counter=VALUES(counter)+1

This is actually a correct/optimized implementation of a REPLACE query
as it works around the DELETE/INSERT race condition.


Best Regards,

Lucas


Shawn Wilson <shawn@glassgiant.com> wrote in message news:<414F1194.838AFBDB@glassgiant.com>...[color=blue][color=green]
> > As you can see, if there are no rows, I'd like to "insert", if not,
> > "update". Problem here is, the code somehow does not go inside else
> > block. And I echoed the mysql_affected_rows()'s value. It gives me -1.
> > What am I doing wrong? How do I fix it?[/color]
>
> I don't know what the problem is with your code, but you might consider using
> REPLACE instead of 2 INSERT/UPDATE queries.
>
> Read the comments here for more information:
> http://ca3.php.net/manual/en/functio...ected-rows.php
>
>
> Shawn[/color]