Insert large amount of array data into DB

Question

I have a multi-dimensional array which contains ten thousands of data. A lot... The array is structured like this:

Array (  

  [0] => Array ( [0] => city [1] => code [2] => country )  

  [1] => Array ( [0] => city [1] => code [2] => country )     

  )

What I am trying to do is to insert the array values city, code and country into a table in a mysql database. I found posts that match exactly what I want to do, but for some reason it is not working with me. When I say it is not working I mean that the php doesn't even start. If I remove the here below code, the file runs normaly. So the problem really comes from that code portion. Hope someone will not mind helping me. Thank you in advance. Cheers. Marc.

//some code to build the array
//db_connect code

$sql = array(); 
foreach( $myarray as $row ) 
    {
    $sql[] = '("'.$row[0].'", "'.$row[1]).'","'.$row[2].'")';
    }

mysql_query('INSERT INTO test (t_city, t_code, t_country) VALUES '.implode(',', $sql));

The code look fine. You just can't implode ten thousands of data inside sql query. — seferov, Mar 15 '12 at 11:49
There must be another error because the php file doesn't even start running when that code is inserted... — Marc, Mar 15 '12 at 11:57
[PHP's queries are limited by the "max_allowed_packet"...](http://stackoverflow.com/questions/5735150/php-max-amount-of-inserts-in-one-sql-query) — ashein, Mar 15 '12 at 12:02
Hello ashein. Thanks for the info. I did not know that. Still there is a problem with the code in the curly brackets because if i remove it the php starts running and if I leave it it is not running... Do you see something wrong in that line between the curly brackets? — Marc, Mar 15 '12 at 12:07
There is a bracket in the middle (after $row[1]), that doesn't belong there. — TPete, Mar 15 '12 at 12:10
I am so blind. Sorry mate. Thanks. The code is running now... — Marc, Mar 15 '12 at 12:12
@TPete - Make an answer post. There is an accepted answer waiting for you... — Marc, Mar 15 '12 at 12:16

score 2 · Accepted Answer · answered Mar 15 '12 at 12:29

As said before, the error in building the sql array, is a surplus bracket. Change

$sql[] = '("'.$row[0].'", "'.$row[1]).'","'.$row[2].'")';

to

$sql[] = '("'.$row[0].'", "'.$row[1].'","'.$row[2].'")';

As ashein noted in comments, the query length is limited by the "max_allowed_paket" variable. If the query is larger than this, an error is raised and connection gets closed.

score 0 · Answer 2 · answered Mar 15 '12 at 12:12

0

There is a bracket after $row[1] :)

Use this (remove the bracket):

$sql[] = '("'.$row[0].'", "'.$row[1].'","'.$row[2].'")';

answered Mar 15 '12 at 12:12

Vytautas Krutulis

109
1
5

score 0 · Answer 3 · answered Mar 15 '12 at 12:13

0

You can try inserting every array record as separate sql-query.

foreach( $myarray as $row ) 
{
    mysql_query('INSERT INTO test (t_city, t_code, t_country) VALUES ("'.$row[0].'", "'.$row[1]).'","'.$row[2].'");
}

but there would be a lot of queries

answered Mar 15 '12 at 12:13

s.webbandit

16,332
16
58
82

Absolutely. I am trying to avoid this. Do you have any idea how? – Marc Mar 15 '12 at 12:17

score 0 · Answer 4 · answered Mar 15 '12 at 15:04

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

You want to dynamically build such a query by submitting multiple of the value pairs at once and not running into limits.

So what you do is to build the insert query while adding iteratively one row after the other. If adding a row would trigger the limit, the query is send and the query is reset:

# sample data
$data = array(
    array('city1', 'code', 'country'),
    array('city2', 'code', 'country'),
    array('city3', 'code', 'country'),
    array('city4', 'code', 'country'),
    array('city5', 'code', 'country'),
    array('city6', 'code', 'country'),
    array('city7', 'code', 'country'),
);

$max_allowed_packet = 1048576; # mysql default value
$max_allowed_packet = 128; # for demonstration purposes
$sql = new SQLInsertQuery('INSERT INTO test (t_city, t_code, t_country) VALUES ', $max_allowed_packet);
foreach($data as $row) {
    $sql->addRow($row);
}
$sql->query(); # manually query any potential left-over query.

This example outputs the following:

Running: INSERT INTO test (t_city, t_code, t_country) VALUES ('city1','code','country'),('city2','code','country');
Running: INSERT INTO test (t_city, t_code, t_country) VALUES ('city3','code','country'),('city4','code','country');
Running: INSERT INTO test (t_city, t_code, t_country) VALUES ('city5','code','country'),('city6','code','country');
Running: INSERT INTO test (t_city, t_code, t_country) VALUES ('city7','code','country');

Demo, Gist

You might want to add a counter for the queries run as well so you can validate after the loop and the final query if at all a query was sent (the limit can be too low so that no query is send at all - depending on your data - so it's worth to have a sanity check for this edge-case).

I hope this example is helpful.

Insert large amount of array data into DB

4 Answers4