Proper way to bind an array to prepared statements?

Question

This has probably been asked a handful of times already, but I'm having a hard time understanding how to bind an array to a mysqli prepared statement.

What I'm trying to do is query for a list of user id's in a message thread, and then insert into the messages table so the user can be notified of a new message reply.

This is what I've tried:

//now check which users are in the message thread
$stmt2 = $mysqli->prepare("SELECT DISTINCT user_id_2
FROM uc_user_messages
WHERE thread_id =  ?");
$stmt2->bind_param("i", $this->thread_id_clean);
$stmt2->bind_result($user_2_id);
$stmt2->execute();

$stmt3 = $mysqli->prepare("
INSERT INTO `users`.`uc_user_messages`(
    `id` ,
    `message_id` ,
    `user_id_2` ,
    `read` ,
    `thread_id`
    )
    VALUES (
    NULL , ?, ?, ?, ?
    );
");

//now insert the message into the user_messages table so the user can be notified of a new message
while ($row = $stmt2->fetch()){
    $stmt3->bind_param("iiii", $inserted_id, $user_2_id, $read, $this->thread_id_clean );
    $stmt3->execute();
}

What am I doing wrong? I've tried putting the prepared statement inside the loop too, but I keep on getting this error:

Fatal error: Call to a member function bind_param() on a non-object

I've also ran the query manually with test data, so I know it's the loop that's causing the value not to bind correctly.

Edit: I should add that the select query is working fine and that it's the insert query that is causing an error.

Yeah. I just can't figure out how to bind the $user_2_id variable from the previous query. I've also tried printing out each value that I'm binding to the query, and all results are as expected. — Joel Murphy, May 16 '13 at 20:37
Hardly ever use `mysqli_`, but `mysqli_error()` might tell you something. However, you know [you can just do this in 1 query](http://dev.mysql.com/doc/refman/5.1/en/insert-select.html)? `INSERT INTO uc_user_messages SELECT DISTINCT NULL, ?, user_id, ?, ? FROM uc_user_messages WHERE ....` — Wrikken, May 16 '13 at 20:44
`Fatal error: Call to a member function bind_param() on a non-object` leads me to think that the `prepare` is failing for `$stmt3`. — Knyri, May 16 '13 at 22:45

Zsolt Szilagyi · Answer 1 · 2013-05-16T22:55:18.147

once a var has been bound to your prep statement, it stays bound.

That means that cou call bind_param only once (outside of the loop), and then just repeat the two-step-loop "change var values / execute".

php.net: Check section #3 INSERT prepared once, executed multiple times

Also be sure that the quotes you used for db and table names don't interfere with PHPs backtick operator. (In that specific case, you can ommit those quotes.)

score 0 · Answer 2 · answered May 16 '13 at 22:49

Fatal error: Call to a member function bind_param() on a non-object

This means that $stmt2 or $stmt3 is not an object. The prepare() function returns false if there was any problem parsing or validating the query. But false->bind_param() is just not going to work.

This is a very common mistake made by many MySQL developers. The solution is you have to check that prepare() returned success and not false.

Example:

$stmt2 = $mysqli->prepare("...");
if ($stmt2 === false) {
  // do something to report $mysqli->error, and return
}

Also see http://php.net/manual/en/mysqli.error.php

score 0 · Answer 3 · edited May 23 '17 at 11:43

You are not checking for the errors. Do it the way explained here: https://stackoverflow.com/a/15447204/285587
Most likely the error is something like "Commands out of sync" one. You have to use store_result() on the first query to avoid that. Strangely, Bill has a perfect answer which is first on google search - you can refer to it for the details.

I only can't get which arrays you are talking about.

Proper way to bind an array to prepared statements?

3 Answers3