How to handle three arrays for insertion into mysql table?

Question

I am trying to insert three different arrays into an sql table using the pdo prepared statement using parameters binding. My code is as shown below:

try {

    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {

    throw new \PDOException($e->getMessage(), (int)$e-getCode());
}

$count = $_POST['count'];
$first = $_POST['first'];
$last = $_POST['last'];

// $serial_no is the first array being defined as follows:
$serial_no = array_map(function($n) { return sprintf('0%05d', $n);}, range($first, $last));

// $identifier is the second array being defined as follows:

$i = 0;
$identifier = array();

while ($i++ < $count) {
    $identifier[] = rand(1000, 9999) . "-" . rand(1000, 9999) . "-" . rand(1000, 9999). "-" .rand(1000, 9999);
}

// $win_value is the third array being defined as follows:

$n = 0;
$win_value = array();

while ($n++ < $count) {
    $win_value[] = ceil(rand(10,80)/10)*10;
}


$stmt = $pdo->prepare("INSERT INTO tickets (serial_no, identifier, win_value) VALUES (?, ?, ?)");

try{
    $pdo->beginTransaction();
    foreach (array_keys($serial_no) as $key) 
    {
        $statement->bind_param($serial_no[$key], $identifier[$key], $win_value[$key]);
        $statement->execute();
    }
    $pdo->commit();

    header("Location: msg.php");

}catch (Exception $e) {

    $pdo->rollback();
    throw $e;

}

However, the values are not inserting into the table using this logic. Any help will be appreciated. Thanks.

You have a syntax error in your first catch : `(int)$e-getCode()` should be `(int)$e->getCode()` — Cid, Jun 28 '19 at 09:33
For some reaso you are using mysqli's bind_param() with PDO. Kindly read [how to use PDO](https://phpdelusions.net/pdo_examples/insert#multiple) — Your Common Sense, Jun 28 '19 at 09:47

score 1 · Accepted Answer · answered Jun 28 '19 at 09:35

I tend not to use bind_params, but just add the parameters to the execute call:

$statement->execute([$serial_no[$key], $identifier[$key], $win_value[$key]])

You also might want to check that count($serial_no) === $count

Are you getting an error?

Your foreach can also be foreach($serial_no AS $key=>$value) You'll ignore $value but this way the $key is already populated for you without the need to call array_keys.

How to handle three arrays for insertion into mysql table?

1 Answers1