2

'INSERT IGNORE INTO', how would I get the column which causes the ignore (the column which is set to 'unique'/'primary') ?

Example of usage, I insert users to the table and it wont insert if the username or email is already inserted - I want to know whether it was the email or username (or both) which is already inserted so I can print an error message on the screen via javascript.

Ofcause, you might suggest me to use SELECT beforehand. However, by doing this I'd have to run 2 additional queries - I'm trying to keep the script as clean / quick as possible.

I know mysql_affected_rows() can tell me if it's inserted or not, but it doesn't return the columns which provented it from doing so.

I assume I could use mysql_error or something? I'm not sure since mysql_error only shows on an error - since I use 'IGNORE' it wont buffer an error.

Would @mysql_query('INSERT INTO ...') work if I parsed mysql_error or something?

1 Answers1

3

After your query, you can execute SHOW WARNINGS to see information about duplicate keys.

You can find an example of PHP code handling MySQL warnings in the answer to this question: Can I detect and handle MySQL Warnings with PHP?

Community
  • 1
  • 1
Dmytro Shevchenko
  • 33,431
  • 6
  • 51
  • 67
  • Thanks but it doesn't give me any 'UNIQUE column' errors. I'll try turning the 'IGNORE' off. –  Apr 19 '12 at 21:27
  • Yes, it works when I turn off the ignore. However, it gives me "Duplicate entry 'test' for key 2" any ideas how to make it tell me what column it is or should I just work with if($key == '2') ? –  Apr 19 '12 at 21:30
  • Hmm, I don't think there's a way to make it show the name of the key. – Dmytro Shevchenko Apr 19 '12 at 21:33