MySQL Bulk Insert Via PHP

Question

In PHP, I pull a large amount of JSON data from a URI, then serialize it into an associative PHP array via the built-in json_decode function.

Then, I create an array:

$inserts = array();

I loop through the JSON associative array, adding a new key/value pair to my $inserts array for each item in the JSON array:

foreach($JSON_data as $key => $value) {     
    $inserts[] = "(".mysql_real_escape_string($value["prop1"]).","
                    .mysql_real_escape_string($value["prop2"]).","
                    .mysql_real_escape_string($value["prop3"]).")";
}

Then, I perform a bulk insert simply by imploding the inserts I already prepared:

mysql_query("INSERT INTO `MyTable` (`col1`,`col2`,`col3`) VALUES ".implode(",",$inserts));

Anyways, I found that the mysql_* family is no longer suggested to be used. So I'm wondering how this type of pattern is suppose to be accomplished using prepared statements or w/e the new accepted constructs are? My concerns are to eliminate SQL injection, and also to update MySQL as quickly as possible with fewer than 10 concurrent, open connections (preferably 1). Also, to keep things as simple and quick as possible.

Or, if there's a new pattern or preferred method to perform such a bulk transaction.

BTW, if you want really fast inserts LOAD DATA INFILE is hard to beat. I don't suggest it in your case though (apart from anything else if you're pulling the data as JSON it's probably not that big), but worth bearing in mind if you need to insert many megabytes/gigabytes to MySQL at once. — John Carter
– John Carter, Commented Oct 18, 2012 at 18:31
See this pastebin.com/28cSmi2C. i've modified the class mentioned below to insert multiple rows in one query (execute statement). — whitewalker
– whitewalker, Commented Jan 28, 2017 at 20:04

gcochard · Accepted Answer · 2012-10-18 18:17:23Z

7

If you use a prepared statement, you can loop over your $JSON_data array with a foreach loop and run the INSERT with that chunk of the data.

Using prepared statements will reduce the overhead of building the query, simply sending the new data to the database on each iteration of the loop.

$query = mysqli_prepare("INSERT INTO `MyTable` (`col1`,`col2`,`col3`)
    VALUES(?,?,?)");

foreach($JSON_data as $key => $value) {
    $query->bind_param('sss',$value["prop1"],$value["prop2"],$value["prop3"];
    $query->execute();
}

Note that the first argument to bind_param() tells it how many values you will be binding, as well as the type for each value.
s corresponds to string data, i corresponds to integer data, d corresponds to double (floating point), and b corresponds to binary data.

One other word of caution, do NOT quote any string data, as the s datatype tells mysql to expect a string. If you quote the ? in the prepared statement, it will tell you the number of params is wrong. If you quote the strings, it will be quoted in mysql.

EDIT:

If you want to use the same paradigm (inserting multiple rows with one query), there are ways to do it. One way is to create a class that will aggregate the bind_param calls and do one bind_param when you execute the query. Code for that is here.

edited Oct 18, 2012 at 18:17

answered Oct 18, 2012 at 17:47

gcochard

11.9k1 gold badge28 silver badges41 bronze badges

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

5 Comments

user17753 Over a year ago

Does this block on each iteration of the loop? If so, wouldn't that make this take a lot longer to complete than a single query (as the number of iterations approaches a high number)?

gcochard Over a year ago

I'm not sure if it will block, but if you want to use multiple VALUES() lists, there is a class here which will help you do that. Just prepare the statement with a loop or str_repeat(), then use the mbind_param() in the loop, and delay calling execute() until after the loop is finished.

user17753 Over a year ago

This was the best answer, but I decided to go with a different approach using LOAD DATA INFILE that was commented on.

whitewalker Over a year ago

@gcochard I don't think you can insert multiple rows with one query even with that class you linked here, with prepared statements. As you said, it appends the values and types together. But to insert them you have to include EQUAL number of '?' in the prepared statements i.e nRows*nColumns. I tried using this class and inserting multiple rows at once and got this warning: PHP Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

whitewalker Over a year ago

@gcochard here, i've modified these classes to BULK INSERT multiple rows in one query pastebin.com/28cSmi2C

BOMEz · Accepted Answer · 2012-10-18 17:48:08Z

1

Use Mysqli or PDO

Here is how you would utilize prepared statements with Mysqli

<?php

//Basic layout to using parametized queries in PHP to prevent Bobby-tables

$VARIABLE = "Some Data";

$mysqli = new mysqli("SERVER","USER","PASSWORD","DATABASE");

$query = $mysqli->prepare("SELECT COLUMN_LIST FROM TABLE WHERE COLUMN = ?");
$query->bind_param('s',$VARIABLE); //'s' for string, use i for int d for double
$query->execute();

//Get results
$query->bind_result($VARIABLE_NAMES_MATCHING_COLUMN_NAMES_GO_HERE);
$query->fetch();

echo $VARIABLE_LIST_MATCHING_COLUMN_LIST;

?>

answered Oct 18, 2012 at 17:48

BOMEz

1,01014 silver badges35 bronze badges

2 Comments

user17753 Over a year ago

This doesn't fully answer the question, as it's unclear to me how to mimic this type of structure with this.

BOMEz Over a year ago

Ah my mistake. It seems that @Greg has answered it fully above.

Collectives™ on Stack Overflow

MySQL Bulk Insert Via PHP

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related