Insert Posted PHP array into MYSQL

Question

I have built the array below to insert multiple rows that is passed from a multi row html form into a MYSQL Database. What I am running into and cannot figure out is how to modify what I have already built to insert multiple rows correctly. I have provided the code and examples below.

   <?php 
        include 'connect.php'; 
            $records= array(
              'palid' => $_POST['LPN'],
              'auditor' => $_POST['ANAME'],
              'itnum' => $_POST['Part'],
              'ordid' => $_POST['Order'],
              'pckusr' => $_POST['Picker'],
              'expected' => $_POST['Eaches'],
              'actual' => $_POST['Actual']);

    $keys = implode(', ', array_keys($records));

    $col = array();
    foreach ($records as $rowValues) {
        foreach ($rowValues as $key => $rowValue) {
             $rowValues[$key] = $rowValues[$key];
        }

        $col[] = "(" . implode(', ', $rowValues) . ")";
    }

    $query = "INSERT INTO audit ($keys) VALUES " . implode (', ', $col);
    echo $query;

  $result = mysqli_query($connection, $query) or die(mysqli_error($connection));

  ?>

The echo $query; shows the below which is just the values for all three rows of each column concatenated together instead of each individual row concatenated together.

    INSERT INTO audit(palid, auditor, itnum, ordid, pckusr, expected, actual) VALUES(0010070382, 0010070382, 0010070382), (aud01, aud01, aud01), (2616M, 2216T, 1216F), (5167-2, 5167-2, 5167-2), (LION, LION, LION), (30, 300, 402), (30, 300, 402)

As it should look like this:

    INSERT INTO audit(palid, auditor, itnum, ordid, pckusr, expected, actual) VALUES(0010070382, aud01, 2616M, 5167-2, LION, 30, 30), (0010070382, aud01, 2216T, 5167-2, LION, 300, 300), (0010070382, aud01, 1216F, 5167-2, LION, 402, 402)

When I use var_dump($records); the array passes the below information, but I have yet to figure out how to form the information into each associated group to pass the three rows into my MYSQL database.

    array(7)
    {
        ["palid"] => array(3)
        {
            [0] => string(10) "0010070382"
            [1] => string(10) "0010070382"
            [2] => string(10) "0010070382"
        }
        ["auditor"] => array(3)
        {
            [0] => string(5) "aud01"
            [1] => string(5) "aud01"
            [2] => string(5) "aud01"
        }
        ["itnum"] => array(3)
        {
            [0] => string(5) "2616M"
            [1] => string(5) "2216T"
            [2] => string(5) "1216F"
        }
        ["ordid"] => array(3)
        {
            [0] => string(6) "5167-2"
            [1] => string(6) "5167-2"
            [2] => string(6) "5167-2"
        }
        ["pckusr"] => array(3)
        {
            [0] => string(4) "LION"
            [1] => string(4) "LION"
            [2] => string(4) "LION"
        }
        ["expected"] => array(3)
        {
            [0] => string(2) "30"
            [1] => string(3) "300"
            [2] => string(3) "402"
        }
        ["actual"] => array(3)
        {
            [0] => string(2) "30"
            [1] => string(3) "300"
            [2] => string(3) "402"
        }
    }

Answer 1

You should be doing this with prepared statements, not just jamming user-provided information into a database query. That's a very bad idea! Try this PDO code:

<?php
$db_name = "";
$db_user = "";
$db_pass = "";
$db = new PDO("mysql:host=localhost;dbname=$db_name", $db_user, $db_pass);

$stmt = $db->prepare("INSERT INTO audit(palid, auditor, itnum, ordid, pckusr, expected, actual) VALUES (?, ?, ?, ?, ?, ?, ?)");

// refactor the POST arrays
$recordcount = count($_POST["LPN"]);
for ($i = 0; $i < $recordcount; $i++) {
    $records[] = [
        $_POST["LPN"][$i],
        $_POST["ANAME"][$i],
        $_POST["Part"][$i],
        $_POST["Order"][$i],
        $_POST["Picker"][$i],
        $_POST["Eaches"][$i],
        $_POST["Actual"][$i],
    ];
}

foreach ($records as $record) {
    $stmt->execute($record);
}

You're building a query with placeholders ? for each variable. Within a loop, that query gets executed, which replaces those placeholders with the correct value. This also takes care of escaping any unsafe characters – with your existing code, anyone could easily wipe out your database.

Answer 2

@miken32 pointed your one of the most important elements for avoiding sql injections this is something that you should definitely use. But here is an solutions that should do the trick for your implementation in case you are just curious how to solve this.

    include 'connect.php'; 
    $records= array(
          'palid' => $_POST['LPN'],
          'auditor' => $_POST['ANAME'],
          'itnum' => $_POST['Part'],
          'ordid' => $_POST['Order'],
          'pckusr' => $_POST['Picker'],
          'expected' => $_POST['Eaches'],
          'actual' => $_POST['Actual']
    );

    $col = [];
    $keys = [];
    $valuesForInsert = [];

    foreach ($records as $recordKey => $rowValues) {
        $keys[] = $recordKey;
        foreach ($rowValues as $key => $rowValue) {
            $valuesForInsert[$key][] = $rowValues[$key];
        }
    }

    foreach ($valuesForInsert as $rowValue) {
        $col[] = "(".implode(', ', $rowValue).")";
    }

    $query = "INSERT INTO audit ($keys) VALUES ".implode(', ', $col);
    echo $query;