MySQL Bulk Insert via PHP

Question

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

Then I create an array:

 $inserts = array(); 

I go through the JSON associative array, adding a new key / value pair to my $inserts array for each element of 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 bulk insertion simply by undermining the already inserted inserts:

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

In any case, I found that the mysql_* family is no longer offered to be used. Therefore, I wonder how this type of template is supposed to be executed with the help of prepared instructions or without new accepted constructions? My problems are to eliminate SQL injection, and also upgrade MySQL as soon as possible with less than 10 concurrent open connections (preferably 1). In addition, to make everything as simple and quick as possible.

Or, if there is a new template or preferred method for performing such a bulk transaction.

Answer 1

If you use a prepared statement, you can $JSON_data over the $JSON_data array using the foreach loop and run INSERT with this piece of data.

Using prepared statements will reduce the overhead of building a query by simply sending new data to the database at 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 bind, 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.

Another caveat: DO NOT quote any string data since the data type s tells mysql to expect a string. If you specify ? in the prepared statement, it will tell you that the number of parameters is incorrect. If you specify strings, it will be specified in mysql.

EDIT:

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

Answer 2

Use Mysqli or PDO

Here is how you could use prepared statements using 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; ?>