如何为数组的每个元素插入数据库？

Question

I have a booking system and need to insert the details into mysql database. All of my variables are strings and one of them is an array - so I need to insert the same information for each array element ($ticketnumber). I have tried doing it with a foreach loop, but it doesn't seem to work. How do I do this with only one INSERT statement as people may choose different number of tickets to buy?

Here is part of the code:

<?php

$day = $_SESSION['date'];
$time = $_SESSION['time'];
$name = $_REQUEST['name'];
$ticketnumber = $_REQUEST['tickets']; //this is the array variable

foreach ($ticketnumber as $ticket){
    $sql = "INSERT INTO table VALUES ('$name', '$day', '$time', '$ticket');";
    $handle = $conn->prepare($sql);
    $handle->execute();

}
?>

The values of my array in this case are(it varies depending on how many tickets you check):

array(2) { [0]=> string(3) "010" [1]=> string(3) "011" }

This matched the value of $ticket as well, why is that happening?

My session and connection to the database are established before this. I also tried replacing the array variable with a string and the insert statement works.

Any help is appreciated!

Thank you.

Answer 1

If you concatenate values into the SQL string as you are doing, there's not much benefit to using a prepared statement. Instead, prepare a statement with placeholders and execute it repeatedly for the various different values.

Create an array of parameters

$params['day'] = $_SESSION['date'];
$params['time'] = $_SESSION['time'];
$params['name'] = $_REQUEST['name'];

Create the prepared statement with placeholders where your values will be bound

$sql = "INSERT INTO table VALUES (:name, :day, :time, :ticket);";
$handle = $conn->prepare($sql);

Execute the statement in a loop for each value in $ticketnumber.

$ticketnumber = $_REQUEST['tickets'];

foreach ($ticketnumber as $ticket) {
    $params['ticket'] = $ticket; // assign the current ticket to the array of values
    $handle->execute($params);   // the values are bound to the prepared statement
}

You don't really need to explicitly close or null the connection (especially not inside the loop as others have pointed out).