MySQLi failing to prepare a statement

问题:

I'm running two queries in my script room.php. Both are using MySQLi prepared statements, and their code are as follows:

/* Get room name */
$stmt = $mysqli->prepare('SELECT name FROM `rooms` WHERE r_id=?');
$stmt->bind_param('i', $roomID);
$stmt->execute();
$stmt->bind_result($roomName)

/* Add this user to the room */
$stmt = $mysqli->prepare('INSERT INTO `room_users` (r_id, u_id) VALUES (?, ?)');
$stmt->bind_param('ii', $roomID, $_SESSION['userID']);
$stmt->execute();

When I run the script, I get this error:

Fatal error: Call to a member function bind_param() on a non-object in C:\wamp\www\room.php on line 24

Which is the second query. If I remove the first query from the script, everything runs fine. Likewise if I remove the second query. Which leads me to believe there's a problem because I'm reusing the $stmt object. If I try the second query using $stmt2 I still get the error.

All my database tables and fields exist, so there's nothing wrong with the queries.

回答1:

All of the mysqli functions/methods can fail in which case they will return false. I.e. if prepare() fails $stmt isn't an object you can call a method on but a bool(false). You have to check the return values and add some error handling, e.g.

$stmt = $mysqli->prepare('SELECT name FROM `rooms` WHERE r_id=?');
if ( !$stmt ) {
    printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
    die;
}

$b = $stmt->bind_param('i', $roomID);
if ( !$b ) {
    printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}

$b = $stmt->execute();
if ( !$b ) {
  and so on and on

see http://docs.php.net/mysqli-stmt.errno et al

---

in this case you probably bumped into the problem that you can't create an other statement while there are still results/result sets pending for the previous statement.
see http://docs.php.net/mysqli-stmt.close:

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.