num_rows with object mysqli and statements

[Yesideez]

Is it possible to get the number of rows returned when using a SELECT query and a statement?

      $sql="SELECT * FROM users WHERE username=? AND password=? AND active=1";
      $stmt=$db->stmt_init();
      if ($stmt->prepare($sql)) {
        $stmt->bind_param("ss",$uname,$pword);
        $stmt->execute();
        $stmt->bind_result($id,$username,$password,$level,$added,$edited,$active);
        $stmt->fetch();
        echo "Matched: ".$stmt->num_rows.'<br>';
        echo "id: ".$id."<br>Added: ".$added."<br>";
        $stmt->close();

The "Matched" line always gives me 0. I've tried added parenthesis to make it num_rows() and I still get 0 regardless of whether my query returns data from my table or not. I've checked in the PHP manual and it doesn't give an example using a statement.

Edited April 19 by Yesideez

[Barand]

From the manual:

I'd recommend using PDO instead of mysqli.

[mac_gyver]

i also recommend the much simpler and more modern PDO extension. you can directly fetch data from a prepared query, the same as how you would fetch it for a non-prepared query.

if you are going to use the mysqli extension, forget about mysqli_int/mysqli_stmt_prepare. just use mysqli_prepare(). also, forget about the num_rows property. just fetch the data and test if there was any fetched data.

if you are querying to find a row of data matching an (active) user, you would not include the password comparison in the WHERE ... term. also, you should be using php's password_hash() and password_verify() for password hashing.

php finally realized the it should use exceptions for errors for things like database statement errors. as of php8, the mysqli (and PDO) connection, query, exec, prepare, and execute calls throw exceptions for errors. this means that any conditional error handling logic you have for these statements can be removed since they won't ever get executed upon an error, simplifying the code.

there's also generally no need to close prepared query handles, free up result sets, or close database connections in your code, since php destroys all resources when your script ends, simplifying the code.

pdo version -

$sql="SELECT * FROM users WHERE username=? AND active=1";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $uname ]);
if($user_data = $stmt->fetch())
{
    // a row was found
    // you can reference elements in $user_data, such as $user_data['id'], $user_data['added'], ...
}
else
{
    // no row was found
}

 

 

 

 

 

 

[Yesideez]

Thanks guys, I've changed it all over to PDO and finding it a lot easier. I decided to use MySQLi as I'd previously used MySQL years ago and thought it would be the natural progression and had no idea about PDO.