> 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]
