PHP Mysql Insert Batch if not exist

Question

I have searched that there is already a way in inserting avoiding the duplicate error

ref: MySQL: Insert record if not exists in table

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Unknown' AS name, 'Unknown' AS address, '022' AS tele) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Unknown'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+---------+-----------+------+
| id | name    | address   | tele |
+----+---------+-----------+------+
|  1 | Rupert  | Somewhere | 022  |
|  2 | John    | Doe       | 022  |
|  3 | Unknown | Unknown   | 022  |
+----+---------+-----------+------+

is there a way for this to do in batch? or how is the format in adding data as a batch

ref: insert multiple rows via a php array into mysql

Planning to integrate this one

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

is there a way?

Can you use INSERT IGNORE? https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Jeff Vdovjak, Mar 12 '20 at 23:18

GMB · Accepted Answer · 2020-03-13T00:20:37.193

I would suggest using the ON DUPLICATE KEY syntax for this. This will simplify the query, and allow the use of the VALUES() statement, which is handy to pass parameters from your application.

For this to work, you need a unique (or primary key) constraint on colum name. Create it if it does not exist:

create unique index idx_table_listnames on table_listnames(name);

Then, you can do:

insert into table_listnames(name, address, tele)
values('Unknown', 'Unknown', '022')
on duplicate key update name = values(name)

The conflict clause traps violations on the unique index, and performs a no-op update.

Side note: use parameterized queries to pass data from your application to the query; escaping input is not enough to make your query safe.

thanks this might do the job. answer accepted. but if is there a way to do the following in batch that would be perfect. — user3492648, Mar 15 '20 at 16:29

PHP Mysql Insert Batch if not exist

1 Answers1