a question about mysql_affected_rows()

[Ben]

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='$currentf ile',
uid='$cookie_va l',
referer='$ref', count = 1, accesstime='$fi leatime' ON
DUPLICATE KEY
UPDATE referer='$ref', count=count+1,
accesstime='$fi leatime'";
$result = mysql_query($qu ery, $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($qu ery, $link) or die("Could not
insert");
}
else {
mysql_query("UP DATE counter SET count=count+1, referer=$ref,
accesstime=$fil eatime WHERE page=$currentfi le 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

[Lucas]

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@glassgia nt.com> wrote in message news:<414F1194. 838AFBDB@glassg iant.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]