Mysqli Prepared Stmt returns 0 num_rows

Question

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;
}
}

score 2 · Accepted Answer · answered Aug 04 '17 at 10:03

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;
}

@kingraphaII It would be great if you would mark Twinfriends' answer as "accepted", so that other users can benefit from this correct solution too. — , Aug 08 '17 at 16:52
@aendeerei my bad. Got too excited and forgot. Juc made up for that mistake. Thanx for the reminder. — Redgren Grumbholdt, Aug 11 '17 at 12:18
@kingraphaII No problem. You are welcome. And, if you ever need a complete example: [How to use mysqli prepared statements and exception handling](https://stackoverflow.com/questions/45092344/how-do-i-echo-rows-that-have-a-specific-variable-in-it-from-database/45097253#45097253). My recommendation: begin using PDO. There are more good arguments for that. For it: [PDO prepared statements and exception handling in PHP:](https://stackoverflow.com/questions/45183736/php-mysql-pdo-query-fill-up-variable-with-query-result/45184628#45184628). Good luck. — , Aug 11 '17 at 18:19

Mysqli Prepared Stmt returns 0 num_rows

1 Answers1

Linked

Related