Jump to content

num_rows with object mysqli and statements

Go to solution Solved by mac_gyver,

Recommended Posts

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";
      if ($stmt->prepare($sql)) {
        echo "Matched: ".$stmt->num_rows.'<br>';
        echo "id: ".$id."<br>Added: ".$added."<br>";

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 by Yesideez
Link to comment
Share on other sites

  • Solution

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'], ...
    // no row was found







Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.