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