phpmysqliprepared-statementmysql-num-rows

Mysqli Prepared Stmt returns 0 num_rows


Help. I am getting 0 num_rows but if i execute the query in the console i am getting results. I m kinda new to prepared stmts. Here is my code

Database connection class:

class DbConnection {
const  HOST = "localhost";
const  USR = "root";
const  PWD = "";
const  DB = "club_db";
}

Login class:

class UsrLogin extends DbConnection {

private $conn; /*db connector*/

/*login vars*/
private  $usr;
private  $pwd;

/*ctrl var*/
public $AccessGranted = false;

function __construct($username,$password){
    /*initialize db connection*/
    $this->conn = new mysqli(DbConnection::HOST,DbConnection::USR,DbConnection::PWD,DbConnection::DB);
    /*set login vars*/
    $this->usr = $username;
    $this->pwd = $password;

}

public function login(){
    $sql = "SELECT * FROM sys_usr WHERE uid = ? AND passwd = ?";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("ss", $usr,$pwd);

    $usr = $this->usr;
    $pwd = $this->pwd;

    $stmt->execute();

    echo  $stmt->num_rows;
}
}

Solution

  • While in my opinion the two given answers here are not correct, I think I know where the problem is.

    Well first, as said, no need to assign values to variables before you bind them. Thats not true at all. It makes me quite angry, because I read this over and over here on stackoverflow... and its wrong. Simply. Wrong. If it would be true, you couldn't perform multiple prepared statements with different values. Even if its old and many people don't like to see it here, a olink from W3: https://www.w3schools.com/php/php_mysql_prepared_statements.asp It shows that what you're trying is totally possible and it also shows the possibility that you have with prepared statements.

    So, now to your problem:

    What you're doing is totally fine. But there's another thing you're missing, and I think thats what cause the error. The missing store_result() function.

    Give this code a chance and tell me if it works:

    public function login(){
        $sql = "SELECT * FROM sys_usr WHERE uid = ? AND passwd = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ss", $usr,$pwd);
    
        $usr = $this->usr;
        $pwd = $this->pwd;
    
        $stmt->execute();
        $stmt->store_result(); // Quite sure you need this to perform a num_rows...
        echo  $stmt->num_rows;
    }