Mysql bulk insert not inserting all data

Question

I have to insert many data from excel to a table (about 4000 rows ) throught Ajax inside a loop i use :

$query = "INSERT IGNORE INTO ".$params["table"]." (".$colonnes_string.") VALUES (".$row.")";

This query works fine but it insert only 1000 rows of 4000, may be some insert have error why i set IGNORE statement, but it still not insert above 1000 rows.

How can i skip insert error and continue the query ?

Thank you.

if you use Insert ignore the row actually won't insert if it results on duplicate key ... no wonder why only 1000 rows get inserted — Kiran Muralee, Nov 05 '16 at 19:11
May be this post is helpful http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Kiran Muralee, Nov 05 '16 at 19:16

score 1 · Answer 1 · edited Apr 13 '17 at 12:45

1

Please use INSERT... ON DUPLICATE KEY UPDATE syntax instead of IGNORE Here's a link to a similar question https://softwareengineering.stackexchange.com/questions/238472/how-to-insert-update-a-large-amount-of-data-into-mysql-using-php

edited Apr 13 '17 at 12:45

Community

1
1

answered Nov 05 '16 at 19:15

HENOK MILLION

309
1
9

JazZ · Answer 2 · 2016-11-06T05:09:28.000

0

You can insert multiple rows with that syntax :

$query = "
    INSERT INTO some_table 
        (column_name) 
    VALUES 
        ('value'),
        ('another value'),
        ('and again'),
        ...
        ('a last one');
";

So, you'll have to build the query with all your VALUES and then INSERT them all at once.

i.e. :

// Set the first part of the query with table name and column(s)
$query = "INSERT INTO some_table (some_column) VALUES ";

foreach ($rows as $row) {
    // concatenate the query with values in a loop
    $query .= "(".$row."),";
}

// replace last comma with semi-column to get the right syntax
$query = substring($query, 0, -1).";";

Hope it helps.

edited Nov 06 '16 at 05:09

answered Nov 05 '16 at 19:12

JazZ

4,469
2
20
40

Hello, i don't understand your query, why $rows[$i] than $row[$i] ? – Nov 05 '16 at 19:36
Sorry, my bad. I fixed my answer. – JazZ Nov 05 '16 at 19:38
Thank you, so row is an array? i have not to implode this ? – Nov 05 '16 at 20:03
No, `$rows` is the array. `$row` handles the value. You could use this syntax too : `foreach ($rows as $key => $value) {$query += "(".$value."),";}` – JazZ Nov 05 '16 at 20:11
Hello, why i have 0 when echoing query variable ? $query = "INSERT INTO ".$params["table"]." (".$colonnes_string.") VALUES "; foreach ($rows as $row) { $query += "(".$row."),"; } echo $query; – Nov 05 '16 at 20:26
Do you use `+=` to add values to the `$query` string ? – JazZ Nov 05 '16 at 20:29
could you explain me please the meaning of $query = substring($query, 0, -1).";"; ? – Nov 05 '16 at 20:36
As i use wordpress i execute query like this : $insert = $wpdb->query($query); but it produce 500 error – Nov 05 '16 at 20:39
Sorry correct operator is `.=`. The meaning of the substring function is commented. – JazZ Nov 06 '16 at 05:11
Hi, thank you for your help, i solved it, the error is about characters not escaped, i have to put " around string than ', mysql_real_escape_string not wroking on my case. – Nov 06 '16 at 16:04
Glad to hear it. You can post your own answer just for future users. – JazZ Nov 06 '16 at 18:06

Mysql bulk insert not inserting all data

2 Answers2