joshua joshua - 11 days ago 6
MySQL Question

Error inserting multiple values with prepared statement using php

I have an error when i try to insert multiple values into a mysql database using prepared statement.

I keep getting this error

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables.


I think it's seeing $data as a single value, i don't know what to do now

$keys = (?, ?, ?);
$types = "iii";
$data = "1, 3, 500";
if ($stmt2 = $conn->prepare("INSERT INTO tranx (user, type, amount) VALUES (?, ?, ?),$keys")) {
$ortype = 1;
$stmt2->bind_param("iii".$types, $userid, $ortype, $amount, $data);
$stmt2->execute();
$stmt2->close();

}

Answer

I think it's seeing $data as a single value

Yes, of course. Why would it do otherwise if by any means it is a single value?

i don't know what to do now

Well, the best thing you could do is to ask a question. Not that stub you asked here but a real question explaining what are you trying to do and why. As there is no such question we can only guess that you need to do a multiple insert, but some peculiar way.

To do so, create a single array that holds all the data.

$data = [];
$data[] = $userid;
$data[] = $ortype;
$data[] = $amount;
$data[] = 1;
$data[] = 3;
$data[] = 500;
$count = count($data);

then create a string with placeholders

$values = implode(',', array_fill(0,  $count, '(?, ?, ?)'));

then create a string with types

$types = str_repeat("iii", $count);

and finally create your query and execute it

$stmt = $conn->prepare("INSERT INTO tranx (user, type, amount) VALUES $values");
$stmt->bind_param($types, ...$data);
$stmt->execute();