CODEWITHSUNDEEP
phpmysql
user17753
user17753

Reputation: 3161

MySQL Bulk Insert Via PHP

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.

Upvotes: 3

Views: 17464

Answers (2)

gcochard
gcochard

Reputation: 11744

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.

Upvotes: 7

BOMEz
BOMEz

Reputation: 1010

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;

?>

Upvotes: 1

Related Questions