CODEWITHSUNDEEP
phpmysqlauto-incrementmysql-insert-id
TVA van Hesteren
TVA van Hesteren

Reputation: 1221

PHP MySQL AUTO_INCREMENT with INSERT INTO .. ON DUPLICATE KEY

I have searched for an answer for days, however I can't seem to find the right solution. Therefore, I ask the following question:

Suppose I have a table with a column ID which is an AUTO_INCREMENT field and a column Word which is unique. I run the following queries:

"INSERT IGNORE INTO Table (Word) VALUES('Test')"
"INSERT IGNORE INTO Table (Word) VALUES('Test1')"
"INSERT IGNORE INTO Table (Word) VALUES('Test2')"
"INSERT IGNORE INTO Table (Word) VALUES('Test')" //THIS ONE WILL BE IGNORED

The problem is I can't get the last $mysqli->insert_id from the last query, because it isn't inserting anything. However I need this ID which is already in the DB. therefore, I thought I should use a ON DUPLICATE KEY UPDATE statement, however this leads to the situation where AUTO_INCREMENT is skipping values, because it updates the value but ALSO increments the AUTO_INCREMENT value although this value isn't assigned to any row.

So in the end, I end up with a table like this:

ID |Word
1  |Test
2  |Test1
3  |Test2
//Trying to insert words that where already in the table..
12 |Test3
//Trying to insert words that where already in the table..
17 |Test4

Upvotes: 0

Views: 214

Answers (1)

snitch182
snitch182

Reputation: 723

My answer would be to first retrieve the id for the word from the table and only if it fails to insert it. In both cases you have the id ready.

My guess is also that it will be faster this way around since you are not creating any ignored errors in mysql.

Upvotes: 1

Related Questions