> Hi!
>
> Sorry for my ugly English.
> I use mysql in my PHP script(for calculating INs and OUTs from site).
> And I meet some strange thing there.
>
> Example goal: store statistics for clicks on some links.
> I use next algorithm:
>
> ---
> $key='somekeyvalue';
> dbquery("update sometable set count=count+1 where keyfield='$key'");
> if(mysql_affected_rows()<1)
> dbquery('insert into sometable set keyfield='$key', count=1');
> ---
>
> First question: this is good method? Or I MUST use pre-query with
> "select count(...) from sometable where keyfield='$key'" for detection:
> exists needle record(for update) or not(for insert)???

This method is fine. That is to say that I have seen it before in older
code. However, you might take alook at REPLACE:
http://dev.mysql.com/doc/mysql/en/REPLACE.html

And for MySQL 4.1.x you might take a look at  INSERT ... ON DUPLICATE KEY
UPDATE
http://dev.mysql.com/doc/mysql/en/INSERT.html

>
> Second... My script work on many different hosts with different
> hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> version. It works fine excepting one thing... Sometime happens errors
> like:
> MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
> sometable set keyfield='somekeyvalue', count=1

I would look at your logic. What is $key? Where does it come from? If
`keyfield` is a unique index then you cannot have duplicate values.

>
> Why this happens? On some hosts this messageis very rare, but on others
> - so often... I cant understand reason :(
> My script use DB locks, so two clients cant make this situation(i
> think).

Do a little research to find out if your locking is working.

>
> Only one idea: sometime 'update' query dont work(or work, but dont
> return good result), and 'insert' query started... But WHY?

Again, I would look at your logic and try to use REPLACE or INSERT ... ON
DUPLICATE KEY UPDATE.

Regards,

Jim Grill



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to