3

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.

user17753
  • 3,083
  • 9
  • 35
  • 73
  • 2
    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 Oct 18 '12 at 18:31
  • See this http://pastebin.com/28cSmi2C. i've modified the class mentioned below to insert multiple rows in one query (execute statement). – whitewalker Jan 28 '17 at 20:04

2 Answers2

5

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.

gcochard
  • 11,408
  • 1
  • 26
  • 41
  • 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)? – user17753 Oct 18 '12 at 18:00
  • I'm not sure if it will block, but if you want to use multiple `VALUES()` lists, there is a class [here](http://www.php.net/manual/en/mysqli-stmt.bind-param.php#110363) 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. – gcochard Oct 18 '12 at 18:13
  • This was the best answer, but I decided to go with a different approach using `LOAD DATA INFILE` that was commented on. – user17753 Oct 23 '12 at 13:30
  • @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 Jan 28 '17 at 11:00
  • @gcochard here, i've modified these classes to BULK INSERT multiple rows in one query http://pastebin.com/28cSmi2C – whitewalker Jan 28 '17 at 20:00
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;

?>
BOMEz
  • 1,020
  • 14
  • 34
  • This doesn't fully answer the question, as it's unclear to me how to mimic [this type of structure](http://stackoverflow.com/questions/10635414/how-to-insert-bulk-data-into-database-at-a-time) with this. – user17753 Oct 18 '12 at 18:09
  • Ah my mistake. It seems that @Greg has answered it fully above. – BOMEz Oct 18 '12 at 18:11