Menu
As an example, when inserting a record into a table with a unique index, is it best to test first? e.g.,
$mysqli->query('SELECT email FROM tblUser WHERE email = '[email protected]');
then make sure 0 rows are returned, then do the insert?
$mysqli->query('INSERT INTO tblUser ...');
Or is it better to just skip the test and handle the error in the event there's a duplicate entry?
THANKS!
413
If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.
Insert Ignore statement in MySQL has a special feature that ignores the invalid rows whenever we are inserting single or multiple rows into a table. We can understand it with the following explanation, where a table contains a primary key column. The primary key column cannot stores duplicate values into a table.
First, you must specify the name of the table. After that, in parenthesis, you must specify the column name of the table, and columns must be separated by a comma. The values that you want to insert must be inside the parenthesis, and it must be followed by the VALUES clause.
MySQL INSERT statement is used to insert record(s) or row(s) into a table. The insertion of records or rows in the table can be done in two ways, insert a single row at a time, and insert multiple rows at a time.
It's better to insert and handle any duplicate key errors.
The reason is that if you test first, some other client can still insert the value in the brief moment between your test and your insert. So you'd need to handle errors anyway.
71
Broadly speaking, there are three ways to handle this situation with a single query (fewer queries is usually a good thing to shoot for), but none of them is a universal "best way". Which you should use depends on your needs.
The first is, as you mention, running the INSERT … blindly and handling any errors PHP. This is the best approach when a duplicate key indicates a procedural problem (a bug in the software, a user trying to register a name that's already been used, etc.), as it allows you to perform additional operations before committing to a database update.
Second, there is the INSERT IGNORE … syntax. I would tend to call this the least commonly-useful approach, as it discards your INSERT completely if the key already exists. Primarily useful when a row (or rows) may or may not have been added to the table previously, but the data is known not to have changed.
Lastly, you can use an INSERT … ON DUPLICATE KEY UPDATE … statement. These can get rather verbose, but are very handy, as they allow you to insert data into your table without worrying about whether older data exists. If so, the existing row is updated. If not, a new one is inserted. Either way, your table will have the latest data available.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
