CODEWITHSUNDEEP
phpmysqlarrays
Marc
Marc

Reputation: 9537

Insert large amount of array data into DB

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));

Upvotes: 0

Views: 2205

Answers (4)

hakre
hakre

Reputation: 197767

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.

Upvotes: 0

TPete
TPete

Reputation: 2069

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.

Upvotes: 2

s.webbandit
s.webbandit

Reputation: 17000

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

Upvotes: 0

Vytautas Krutulis
Vytautas Krutulis

Reputation: 109

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

Use this (remove the bracket):

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

Upvotes: 0

Related Questions