CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
joshua
joshua

Reputation: 33

Error inserting multiple values with mysqli prepared statement

I have an error when I try to insert multiple values into a mysql database using a 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();
}

Upvotes: 2

Views: 1240

Answers (3)

No name
No name

Reputation: 134

Try this logic, see whether works or not :)

$data = array(array(1,3,5), array(2,4,6));
$sql = 'INSERT INTO tranx (user, type, amount) VALUES (?, ?, ?)';

if($stmt = $conn->prepare($sql)){

$stmt->bind_param("iii", $usr, $type, $amt);

foreach ($data as $v) {

$usr = $v[0];
$type = $v[1];
$amt = $v[2];

$stmt->execute();

if($stmt->insert_id <= 0){
trigger_error('Insert fail. Error: ' . $stmt->error);
break;
}
}
$stmt->close();
$conn->close();
}
else{
trigger_error('Prepare fail.');
}

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157870

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();

Upvotes: -1

Qirel
Qirel

Reputation: 26450

You're trying to bind with variables, yet you assign them manually. In addition to this, your number of parameters don't match.

You've got 3 placeholders, 3 type-definitions, yet 4 values. These are in a one-to-one relationship, so the number of type-definitions (your iii) needs to match the number of placeholders ? and number of values you bind (in order of the placeholders).

// $keys = (?, ?, ?); // Removed this, syntax error and you bind it manually anyway later
// $types = "iii"; // You bind it manually later
$data = "1, 3, 500";

// Removed ',$keys' from your query, you manually put the placeholders
if ($stmt2 = $conn->prepare("INSERT INTO tranx (user, type, amount) VALUES (?, ?, ?)")) {
    $ortype = 1;

    // removed '.$types' - you bind it manually
    // Also removed $data - you have 3 values, 3 columns, not 4
    $stmt2->bind_param("iii", $userid, $ortype, $amount);
    $stmt2->execute();
    $stmt2->close();
}

And yes, your $data is a single value, not a list of parameters.

The documentation holds good examples as well.

References

Upvotes: 0

Related Questions