Most efficient way to insert associative array with matching array to table keys

Question

Given the following array;

[0] => array(
    col1 => val1,
    col2 => val2,
    col3 => val3
),
[2] => array(
    col1 => val1,
    col2 => val2,
    col3 => val3
),
[3] => array(
    col1 => val1,
    col2 => val2,
    col3 => val3
)

And a database table with the same column names col1, col2 and col3

What's the most efficient way to insert this into a database?

I currently have something like this;

$i = 0;
$db_columns = '';

$column_values = array();

foreach ($rows as $k => $row)
{
    $columns = array();
    $fields = array();

    foreach ($row as $a => $b) 
    {
        $column_values[$a.$i] = $b;
        $columns[] = ':'.$a.$i;
        $fields[] = $a;
    }

    // build up sql values
    $db_columns .= ($db_columns ? ', ' : '').'('.implode(', ', $columns).')';

    $i++;
}

// perform insert
$sql = "INSERT INTO table (".implode(', ', $fields).") 
    VALUES $db_columns";

$query = $db->prepare($sql);
$status = $query->execute($column_values);

Which will build the query string like so;

INSERT INTO table (col1, col2, col3)
VALUES (:col10, :col20, :col30), (:col11, :col21, :col31), (:col12, :col22, :col32)

With an array to match the bound variables :col10, :col20, :col30, :col11 etc...

So all well and good. It does what I want and works, but the code to me seems overly complicated considering the input array keys match the table. There must be a cleaner way to do this.

this looks like it could be vulnerable to SQL injection, or potentially to syntax errors created by the content of the fields. — ADyson, Jun 06 '17 at 10:55
You didn't post your complete table structure, but if you are adding to all columns in the table, you don't need to name the columns; meaning remove `(col1, col2, col3)` — mickmackusa, Jun 06 '17 at 10:57
`splash58` that does sound like it would cut out a big chunk of the code. I hadn't thought of that. Will try. `ADyson` the array is not generated by user input. All array keys are manually set elsewhere in the code so AFAIK these should not be issues. — Novocaine, Jun 06 '17 at 11:16
@splash58 That solution worked for me and cut a lot of bloat, feel free to put it down as an answer. — Novocaine, Jun 06 '17 at 14:57

score 1 · Accepted Answer · answered Jun 06 '17 at 17:23

You can simplify a little the code by using question mark parameters. For example, in the way:

$db_columns = '(' .  implode('),(', array_fill(0, count($rows), implode(',', array_fill(0, count($rows[0]), '?')))) . ')';

$sql = "INSERT INTO table (".implode(', ', array_keys($rows[0])).") 
    VALUES $db_columns";

$query = $db->prepare($sql);
$status = $query->execute($column_values);    
$query->execute(array_merge(...array_map('array_values', $rows)));

demo on eval.in

Most efficient way to insert associative array with matching array to table keys

1 Answers1