Proper way to use PDO with mass amount of INSERT Values

Question

I am using PDO to insert into a MySQL DB input from users on a form with 10 input fields. I make a numeric array containing the $_POST values by doing this:

foreach ($_POST as $key => $val)
{
     //testInput() sanitizes the input
     $postArray[] = testInput($val);
}

From all the examples and answers I have browsed through online and on SO using PDO to insert data, they do something like this(https://stackoverflow.com/a/767520/3040381):

$db = new PDO("...");
$statement = $db->prepare("insert into some_other_table (some_id) values (:some_id)");
$statement->execute(array(':some_id' => $row['id']));

The problem I'm having is, I have almost 10 user inputs to insert into my DB. Using the method above, I'd have to do something like this:

$statement->execute(array(':id1' => $row['id'], ':id2' => $row['id2'], ':id3' => $row['id3'], ........':id10' => $row['id10'], ':id11' => $row['id11']));

That's a lot of typing, and makes me wonder if I'm doing it correctly, but I'm not seeing any other way of doing mass INSERTs with PDOs online.

I simplified it a bit like this:

    //ConnectDB() is a method in another file that returns a PDO object
$dbHandle = ConnectDB();

/**
* Gets the columns of a MySQL table
* @param {String} $tableName is the name of the table
* @param {Object} $handle is the PDO object
* @return {array} returns a numeric array containing column names
*/
function getColumnNames($tableName, $handle) {
    $query = $handle->prepare("SHOW COLUMNS FROM $tableName");
    $query->execute();
    $table_fields = $query->fetchAll(PDO::FETCH_COLUMN);
    return $table_fields;
}

/**
* Builds an INSERT query using named placeholders
* @param {String} $tableName is the name of the table to be inserted
* @param {Object} $handle is the PDO object
* @return {String} returns a INSERT query
*/
function getColumnStrings($tableName, $handle) {

    $columnArray = getColumnNames($tableName, $handle);
    $size = sizeof($columnArray);
    $sql = "(";
    for($i = 0; $i < $size; $i++) {
        $sql .= $columnArray[$i] . ",";

    }
    $sql = rtrim($sql, ",");
    $sql .= ")";
    $values = "VALUES (";
    for($i = 0; $i < $size; $i++) {
        $values .= ":" . $columnArray[$i] . ",";

    }
    $values = rtrim($values, ",");
    $values .= ")";


    return "INSERT INTO " . $tableName . " ". $sql . " " . $values;

}

/**
* Builds an associative array to place inside query->execute()
* @param {array} $postArray numeric array containing $_POST values
* @param {Object} $handle is the PDO object
* @param {String} $tableName is the name of the table
* @return {array} returns an associative array with 
*                 values to be replaced
*/
function buildArr($postArray, $handle, $tableName) {
    $namesArray = getColumnNames($tableName, $handle);
    $size = sizeof($namesArray);
    $paramArray = [];
    for($i = 0; $i < $size; $i++) {
        $namesArray[$i] = ":" . $namesArray[$i];

    }

    for($i = 0; $i < $size; $i++) {
        $paramArray[$namesArray[$i]] = $postArray[$i];
    }

    return $paramArray;
}

I place the named placeholders in a numeric array, and then loop through the placeholder array simultaneously with the array containing $_POST values, and create a new associative array to place inside the $query->execute() argument.

I can also, instead of making a new array, do bindValue() and bind each index element from the two arrays as well, which I think would be an alternative. Is my approach to inserting huge amounts of user inputs the correct way?

Answer 1

Your example is a way to do it, but I will give my example of how I would handle this, especially when you can potentially have a significant number of inputs, without constantly needing to update SQL queries or the database when you were to add, remove, or reorder the input, or without needing to create an unnecessary dependency between the interface and database.

Let's assume we've already performed any checks we want to make, that we know whatever values are in the array are to be inserted, I would do something along the lines of:

<?php
//This array gives us a way to match our incoming data to where it needs to be stored.
$lookup = array( 'form1' => 'id1', [...]);

$columns_params = array();
$params_values = array();

foreach($lookup as $form_key => $column_key) {
    $param_key = ':'.$column_key;
    $columns_params[$column_key] = $param_key;
    $params_values[$param_key] = $_POST[$form_key];
}

$insert_statement = sprintf('insert into some_other_table (%s) VALUES (%s)', implode(',',array_keys($columns_params)), implode(',',array_values($columns_params)));

$statement = $db->prepare($insert_statement);
array_map(function($param, $value) use (&$statement) {
                $statemnt->bindParam($param, $value);
            }, $params_values);
$statement->execute();