insert multiple values into multiple rows with prepared statement PHP, MYSQL

Question

I'm trying to insert data from a multiselect into a database with multiple rows.

I once managed to insert them all into ONE row, each with a comma which is not really good from the database perspective. The data from the multiselect is stored as an array in a variable, which then should be filled into multiple rows in the database with a foreach loop.

$array = implode((array)$_POST['multiselectdata']);

$sql = "INSERT INTO tbl_example (column1, column2) VALUES (?, ?)";

$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)){
    ....
    exit();
}
else {
    mysqli_stmt_bind_param($stmt, "ii", $array, $id);
    mysqli_stmt_execute($stmt);
    $result= mysqli_stmt_store_result($stmt);
    mysqli_fetch_all($result,MYSQLI_ASSOC);
    foreach($result as $row){
        echo $row["column1"];
    }
    exit()
}

As a result in each row there should be 1 value displayed of the array in column1 and in column2 there is always going to be the same id. Currently this only inserts 1 value into the database and only 1 row

If you want to insert multiple rows, why do you execute that query only once? — Nico Haase
– Nico Haase, Commented May 7, 2019 at 12:34
mysqli_stmt_execute($stmt); should be in the foreach loop that was my fault. Topic is answered perfectly below by Nick. This can be done with a foreach loop with an array or either with explode if the values are all available seperated with a comma. — Robin
– Robin, Commented May 7, 2019 at 12:42

Nick · Accepted Answer · 2019-05-07 08:35:03Z

2

Rather than implode the contents of $_POST['multiselectdata'] you can iterate over it as an array and execute your prepared statement for each value in the array:

$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)){
    // ....
    exit();
}
else {
    mysqli_stmt_bind_param($stmt, "ii", $value, $id);
    foreach (explode(',', $_POST['multiselectdata']) as $value) {
        mysqli_stmt_execute($stmt);
    }
}

Note that calling mysqli_stmt_store_result on an INSERT query makes no sense as there is no result set. That line and the foreach loop following should be removed from your code.

edited May 7, 2019 at 8:35

answered May 6, 2019 at 22:32

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Robin Over a year ago

Thank you very much for your answer. Unfortunately it is not quite working yet. The data in $_POST['multiselectdata'] is stored like this: 1,2,3,6. Do I need to mention the "," for the foreach loop to work properly?

Nick Over a year ago

@RobinP sorry I thought from your code that $_POST['multiselectdata'] was an array. In that case you will to explode it. See my edit.

Robin Over a year ago

thank you very much for your help! I also got confused first, because I initially thought it would be an array. It works fine now

Collectives™ on Stack Overflow

insert multiple values into multiple rows with prepared statement PHP, MYSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related