PHP Multiple Mysql Insert script explanation. How?

Question

I need help understanding this clever PHP Multiple Mysql Insert code.

Allow me to stress that, I've found the parsing of JSON data online and the rest is mine.

It works flawlessly but there are things I don't fully understand about it...

1. How is it building the insert string? If you could comment the code that's wonderful...

2. Is it repeatedly inserting or is it one giant insert PDO execute?

3. I've notice that it uses bindValue instead of bindParameter. Is this because of the nature of this dynamic PHP script?

4. Optional: If you know of a simple and clear way to do this, by all means, let me know if you get a chance.

JSON POST DATA

[
   {
      "PK_LINE_ITEM_ID":555,
      "DESCRIPTION":"LINE ITEM 5",
      "QUANTITY":0,
      "UNIT":"SF",
      "COST":0,
      "TOTAL":"0.00"
   },
   {
      "PK_LINE_ITEM_ID":777,
      "DESCRIPTION":"LINE ITEM 7",
      "QUANTITY":0,
      "UNIT":"SF",
      "COST":0,
      "TOTAL":"0.00"
   },
   {
      "PK_LINE_ITEM_ID":999,
      "DESCRIPTION":"LINE ITEM 9",
      "QUANTITY":0,
      "UNIT":"SF",
      "COST":0,
      "TOTAL":"0.00"
   }
]

PHP script (data_post_json_insert_all.php)

<?php
/* Status Codes

return 0 = Nothing to Update (n/a)
return 1 = Successful Insert Query
return 2 = Database Connection refused
return 3 = MySQL Query Error OR Wrong URL Parameters */

/* Disable Warnings so that we can return ONLY what we want through echo. */
mysqli_report(MYSQLI_REPORT_STRICT);

// First get raw POST input
$raw_post = file_get_contents('php://input');
// Run through url_decode..
$url_decoded = urldecode($raw_post);
// Run through json_decode...
// false to allow for reference to oject. eg. $column->name instead of $column["name"] in the foreach.
$json_decoded = json_decode($url_decoded, true);

$table_name = 'tbl_xyz';

// INCLUDE DB CONNECTION STRING
include 'php_pdo_mysql_connect.php';

pdoMultiInsert($table_name, $json_decoded, $link);

function pdoMultiInsert($mysql_table, $json_decoded, $pdo_object) {

    //Will contain SQL snippets.
    $rows_sql = [];

    //Will contain the values that we need to bind.
    $to_bind = [];

    //Get a list of column names to use in the SQL statement.
    $column_names = array_keys($json_decoded[0]);


    //Loop through our $json_decoded array.
    // begin outter for each
    foreach($json_decoded as $array_index => $row) {

        $params = [];


        // begin inner for each --------------------------------
        foreach($row as $column_name => $column_value) {

            $param = ":" . $column_name . $array_index;

            $params[] = $param;

            $to_bind[$param] = $column_value;

        } // end inner for each --------------------------------


        $rows_sql[] = "(" . implode(", ", $params) . ")";

    } // end outter for each


    //Construct our SQL statement
    $sql = "INSERT INTO `$mysql_table` (" . implode(", ", $column_names) . ") VALUES " . implode(", ", $rows_sql);

    //Prepare our PDO statement.
    $pdo_statement = $pdo_object->prepare($sql);


    //Bind our values.
    foreach($to_bind as $param => $val) {
        $pdo_statement->bindValue($param, $val);
    }

    //Execute our statement (i.e. insert the json_decoded data).
    return $pdo_statement->execute();
}

$link = null;
$stmt = null;

// return 1 = Successful Insert Query
echo '1'; 

Thanks

Answer 1

1) The script uses a bidimensional array to make it easier to prepare the insert query.

It will create an array for each row (using the column name as the index and the field value as the value), and then a second array containing those rows. So the array represents all the data that will be inserted, exactly as it should be included.

Then, they implode each line using a coma as glue - so you'll have each value separated with a coma and put parenthesis on it. Then just implode the second array using a coma as glue again, wich will mount the whole insert values query.

2) Execute is runing outside any repeat loop, so it's only one giant insert.

3) bindParam will bound the query to a variable, and if this variable changes in the future, it will change in the query too. bindValue append the final value at the runtime. Take a look at this thread.

4) This script is meant to be generic and work with different table setups - and it's a good way of doing it.