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
– seferov, Commented Mar 15, 2012 at 11:49
There must be another error because the php file doesn't even start running when that code is inserted... — Marc
– Marc, Commented Mar 15, 2012 at 11:57
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
– Marc, Commented Mar 15, 2012 at 12:07
There is a bracket in the middle (after $row[1]), that doesn't belong there. — TPete
– TPete, Commented Mar 15, 2012 at 12:10

TPete · Accepted Answer · 2012-03-15 12:29:29Z

2

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.

answered Mar 15, 2012 at 12:29

TPete

2,0594 gold badges24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vytautas Krutulis · Accepted Answer · 2012-03-15 12:12:22Z

0

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

Use this (remove the bracket):

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

answered Mar 15, 2012 at 12:12

Vytautas Krutulis

1091 silver badge5 bronze badges

Comments

s.webbandit · Accepted Answer · 2012-03-15 12:13:01Z

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, 2012 at 12:13

s.webbandit

17.2k17 gold badges60 silver badges82 bronze badges

1 Comment

Marc Over a year ago

Absolutely. I am trying to avoid this. Do you have any idea how?

hakre · Accepted Answer · 2012-03-15 15:04:04Z

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.

Collectives™ on Stack Overflow

Insert large amount of array data into DB

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related