0

My Script needs to update the table if the name is already in there. But it is not doing it.

This is my code that i'm using

$sth = $db->prepare(
  'INSERT INTO track (rsname, overallranknow, overalllevelnow, overallxpnow)
       VALUES (:name, :Overalln, :Overall1, :Overall2)
       ON DUPLICATE KEY UPDATE 
       rsname = values(rsname), 
       overallranknow = values(overallranknow),
       overalllevelnow = values(overalllevelnow),
       overallxpnow = values(overallxpnow)'
);      
$sth->bindValue(':name', $name, PDO::PARAM_STR);
$sth->bindValue(':Overalln', $Overalln, PDO::PARAM_INT);
$sth->bindValue(':Overall1', $Overall[1], PDO::PARAM_INT);
$sth->bindValue(':Overall2', $Overall[2], PDO::PARAM_INT);
$sth->execute();

It should only update when the name is already there. I dont know mutch about PDO so thats why i'm asking so mutch about it.

~Kev (bad english = sorry)

Kev30
  • 119
  • 1
  • 9
  • 1
    Does the query work when you run it in phpMyAdmin or a similar client tool? Also check out [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) – Pekka Jul 10 '13 at 09:41
  • "But it's not doing it" - **HOW** did you determine it's not doing it? – N.B. Jul 10 '13 at 09:41
  • @N.B. its not doing it = Its not updating the table Also no error messages – Kev30 Jul 10 '13 at 09:54
  • Again, **HOW** did you determine it's not doing it? How do you know? How did you try to test it? Where's the rest of the code? Did you check input variables? I could go on, but you get the point. – N.B. Jul 10 '13 at 09:55
  • @N.B. when i enter my name in the input bar it inserts it in mysql ;) then when i do it another time it needs to update itself but it is inserting the name again (so there is 2 times the same name) Also the rest of the code is not needed for this. – Kev30 Jul 10 '13 at 09:58
  • 1
    There you go. This is your first step - the way it's not working is that it inserts the same name TWICE. What does that tell you? It tells you that `ON DUPLICATE KEY UPDATE` isn't working because there doesn't exist a mechanism that makes it work - a duplicate key. The only way to detect a duplicate key is to force to have UNIQUE key, meaning only 1 can exist and if you want to enter the same one again - `ON DUPLICATE KEY` gets triggered. Therefore, through logic you come to conclusion that you don't have a proper key where it should be. Then you google "mysql unique key". Not that hard. – N.B. Jul 10 '13 at 11:18

1 Answers1

2

Someone who taught you this query, didn't tell you that you need an unique key for it to work.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345