Prepared statement returning False

Question

I know there are many questions similar to my question. But I really can not figure the problem here. I have class named 'UsersClass' which is responsible on every task related to users. And I use mysqli prepare statement to insert or select data from the database, the problem is that in many cases prepare statement return false. I solved the issue by making new connection every while, but this caused another issue in other functions "2006: Mysql server has gone away" See code below please: Here and in other functions the prepare return false.

function isPostingAllowed() {
  $this->setPsSelectUsers($this->_connection->prepare("Select * from userpermissions where userpermissions.UserId = ? and userpermissions.PermissionId = 1"));
 $this->_psSelectUsers->bind_param('i',$this->UserId);
 $this->_psSelectUsers->execute();
        if ( false===$this->_psSelectUsers ) {
        die('prepare() failed: ' . htmlspecialchars($this->_connection->error));}
 if ($this->_psSelectUsers->fetch()){
     return true;
 }else{
     return false;
     }}


function setPsSelectUsers($stmt) {
    $this->unsetPsSelectUsers();
           // mysqli_close($this->_Connection);
           // $this-> __construct();

    $this->_psSelectUsers= $stmt;}

When I uncomment these two lines The first function will work and prepare staement will not return false, but in this case the following function will throw error 2006:

 function checkUserAuthentication() {
 $this->setPsSelectUsers($this->_connection->prepare("SELECT UserLogInName FROM Users WHERE UserLogInName=? AND UserPassword=?"));
 $this->_psSelectUsers->bind_param('ss',$this->UserLogInName, $this->UserPassword);
 $this->_psSelectUsers->execute();
 if ($this->_psSelectUsers->fetch()){
     return true;
 }else{
     return false;
     }}

So How to solve the first problem without making new problem?

Answer 1

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)

1. Open up the connection (Once: at the very beginning, not during the execution of every query)
2. Create a prepared statement
3. Bind parameters
4. Execute the query
5. Bind result variables
6. Fetch value into those bound variables
7. Close the statement object
8. 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(...);