Mysqli Prepare Statement - Returning False, But Why

Mysqli Prepare Statement - Returning False, but Why?

I'm copying the solution into this answer so this can be given an upvote, otherwise the question will appear in the "unanswered questions" forever. I'm marking this answer CW so I won't get any points.

@Andrew E. says:

I just turned on
mysqli_report(MYSQLI_REPORT_ALL) to
get a better understanding of what was
going on - turns out that one of my
field names was incorrect - you'd
think that prepare() would throw an
exception, but it fails silently.

Prepared statement returning False

Problem:

... I use mysqli prepare statement to insert or select data from the database, the problem is that in many cases prepare statement return false.

That's because you're running the queries in out of order fashion i.e. you're executing ->prepare() before closing the previous statement object. Given your current code, add the following error reporting code in your prepared statements,

if(!$this->_connection->prepare(...)){
    printf('errno: %d, error: %s', $this->_connection->errno, $this->_connection->error);
    die();
}

If you look at $this->_connection->error, you'll see the following error,

Commands out of sync; you can't run this command now

This issue has been documented in many forums, such as:

From the MySQL documentation,
If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.
From this SO thread,
You can't have two simultaneous queries because mysqli uses unbuffered queries by default (for prepared statements;...

Solution:

Execute the commands in correct order, (See this example code)

Open up the connection (Once: at the very beginning, not during the execution of every query)
Create a prepared statement
Bind parameters
Execute the query
Bind result variables
Fetch value into those bound variables
Close the statement object
Close the connection (Once: at the very end, not during the execution of every query)

(Follow steps 2 to 7 for executing all of your queries, though one or more steps might be optional based on of your query)

So the solution is, close the previous statement object before calling ->prepare() again. Take this method call $this->unsetPsSelectUsers(); out of the setPsSelectUsers() method and place it before the if ($this->_psSelectUsers->fetch()){...}else{...} block of isPostingAllowed() and checkUserAuthentication() methods. Furthermore, save the status of $this->_psSelectUsers->fetch() method call in a variable and use it in the subsequent if block. So your code should be like this:

public function isPostingAllowed() {
    $this->setPsSelectUsers($this->_connection->prepare("Select * from userpermissions where userpermissions.UserId = ? and userpermissions.PermissionId = 1"));
    if(!$this->_psSelectUsers){
        printf('errno: %d, error: %s', $this->_connection->errno, $this->_connection->error);
        die();
    }
    $this->_psSelectUsers->bind_param('i',$this->UserId);
    $this->_psSelectUsers->execute();
    $status = $this->_psSelectUsers->fetch();
    $this->unsetPsSelectUsers();
    if ($status){
        return true;
    }else{
        return false;
    }
}

private function setPsSelectUsers($stmt){
    $this->_psSelectUsers= $stmt;
}

private function unsetPsSelectUsers() { 
    if (isset($this->_psSelectUsers)) { 
        $this->_psSelectUsers->close(); 
        unset($this->_psSelectUsers);
    }
}

public function checkUserAuthentication() {
    $this->setPsSelectUsers($this->_connection->prepare("SELECT UserLogInName FROM Users WHERE UserLogInName=? AND UserPassword=?"));
    if(!$this->_psSelectUsers){
        printf('errno: %d, error: %s', $this->_connection->errno, $this->_connection->error);
        die();
    }
    $this->_psSelectUsers->bind_param('ss',$this->UserLogInName, $this->UserPassword);
    $this->_psSelectUsers->execute();
    $status = $this->_psSelectUsers->fetch();
    $this->unsetPsSelectUsers();
    if ($status){
        return true;
    }else{
        return false;
    }
}

Moreover, you don't have to use this setPsSelectUsers() method anymore, you can directly use the property $_psSelectUsers in your methods like this:

$this->_psSelectUsers = $this->_connection->prepare(...);

MySQLi prepared statement returning false

prepare returns false if an error occurs. try

$stmt = $mysqli->prepare('SELECT u_id FROM `users` WHERE username=? LIMIT 1');
if ($stmt === FALSE) {
    die ("Mysql Error: " . $mysqli->error);
}

and some mysql error should be displayed.

$mysqli-prepare returns false, but $mysqli-error is an empty string

I've spent a while to find the error. I didn't find something here, so I post this Q&A-Answer:

$mysqli->error returned an empty string, because the destructor of reused variable $oStatement had reset the error text.

In the error case the second $mysqli->prepare returned false and has set the error text. Then the return value (false) will be assigned to $oStatement. $oStatement contains a mysqli statement object, whose destructor will be called as part of the assignment. The destructor is called without any errors and resets the error text.

The correct solution is either to use different variables for each statement or to reset the statement variable before each assignment:

$mysqli = new mysqli('host', 'user', 'password', 'database');

// First query (valid)
$oStatement = false;
if(($oStatement = $mysqli->prepare('SELECT column FROM table;')) === false)
  throw new Exception('Error in statement: ' . $mysqli->error);

// ...

// Second query (invalid)
$oStatement = false;
if(($oStatement = $mysqli->prepare('SELECT column_which_doesnt_exist FROM table;')) === false)
  throw new Exception('Error in statement: ' . $mysqli->error);

With this solution the error text is valid and can be thrown.

PHP MySQLI prepared statement returning false on execution

You are extracting the error from the wrong object. The error is stored inside the statement object, not the connection object. See:

http://php.net/manual/en/mysqli-stmt.error.php

/* execute query */
$stmt->execute();

printf("Error: %s.\n", $stmt->error);

Note that the code is using the same object is used to execute the statement and to retrieve the error. You need to use, in your case:

echo "<br />" . $p['GetAdmUsers']->error;

PHP - mysqli::prepare returning false

Before second usage mysqli::prepare() you must either free mysqli result or close current mysqli statement:

...
//do sth with the data

$mysqli->free(); // or $stmt->close();

$query = "SELECT * FROM answers WHERE question_id = ?";
$stmt  = $mysqli->prepare($query);
...

Prepare statement return false but insert successfully

See the documentation:

mysqli_stmt::get_result
Returns a resultset for successful SELECT queries, or FALSE for other DML queries or on failure. The mysqli_errno() function can be used to distinguish between the two types of failure.

So $res->get_result() works only as you expect for select.

To check if your prepared statement was executed correctly, you can directly use the return parameter for execute:

mysqli_stmt::execute
Returns TRUE on success or FALSE on failure.

You should probably use two different functions, one for select-statements that return a result set (that you by the way have to fetch(), otherwise further queries won't work, read the note in the documentation to execute()), and one for other statements, that return true or false.

And you obviously have to replace if ($result2) { with if ($result) {, but I assume that is just a typo.

Prepared statement returning false but query was successful

You need to remove $stmt->execute(); because the statement gets executed in the if-statement.

It's now doing it twice, which will not work.