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