mysqli_stmt_num_rows($stmt) always returns 0? [duplicate]

Question

I am creating a login script for my web app and am trying to use $count = mysqli_stmt_num_rows($stmt); to find the number of rows returned from the sql select statement, so I can then decide if a session should be started.

The problem is, $count is always 0, even when I enter valid user name and password that matches the data in my database. I have tested the select statement, it works fine. No errors, syntax, SQL or otherwise are given, so i'm kinda stuck as to whats happening.

CODE:

<?php

    $link = mysqli_connect("localhost", "****", "****", "****");

    //check connection
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    // username and password sent from form 
    $myusername=$_POST['myusername'];   
    $mypassword=$_POST['mypassword']; 

// Move to MySQL(i) as MySQL is now obslete and use Prepare statment for protecting against SQL Injection in better and easier way
    $stmt = mysqli_prepare($link, 'SELECT username, password FROM `users` WHERE  `username` =  ? AND  `password` =  ?');

    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "ss", $myusername, $mypassword);

    /* execute query */
    mysqli_stmt_execute($stmt);

    /*count number of rows returned*/
    $count = mysqli_stmt_num_rows($stmt);

    /*display number of rows returned*/
    //echo $count;

    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $myusername, $mypassword);

    /* fetch value */
    mysqli_stmt_fetch($stmt);

    /* close statement */
    mysqli_stmt_close($stmt);

    if($count == 1) {

        session_start();
        $_SESSION['userid'] = $myusername;
        header("location:index.php");
        exit;

    } else {

        echo "Wrong Username or Password";
        echo "<form name='form5' action='main_login.html'>";
        echo    "<input type='submit' name='Submit' value='Log-in'>";
        echo "</form>";

    }

/* close connection */
mysqli_close($link);

?>

Viscose

Have you checked the mysqli_stmt_num_rows documentation? PS: why don't you use sql COUNT instead? – Men

You need to store encrypted passwords in the DB – Botel

Answer 1

+1 for using prepared statements.

You need to call store_result before you can check num_rows:

mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$count = mysqli_stmt_num_rows($stmt);

As other users have suggested ensure that you are only storing hashed passwords in the DB and not transferring unencrypted passwords in HTTP requests. You can do this by adding an input to the form with JS, hashing the password on the login form, remove the unhashed password field with JS and compare the hashed password from the form with the hashed password in the DB.

Also, if the check fails, you're better off using self-referencing forms than echoing out a new form for a subsequent login, this kind of approach will become unmanageable very quickly.

Likelihood

Hi, thanks for your response. I plan on adding encryption to the passwords. At the moment I am still new to mysqli (recently finished at Uni, we only used mysql php, not mysqli) so I am making sure I get the basics right before I add the encryption part in. – Viscose

Cool, best of luck. Look at the Object Oriented way of representing prepared statements as well -> makes the code a lot cleaner! – Likelihood

Hi, if your still there Leemo. After reviewing my code again, I cant see any reason to keep "mysqli_stmt_bind_result". As far as i can see I don't actually need this as i never use it. Also, fetch would be obsolete as well. Is there any reason (that I may have overlooked) that I need to keep these? – Viscose

store_result doesn't actually yield any retrieve any values or bind them to any variables. bind_result allocates variables to pass the result to once fetch is called so you need all of them. – Likelihood

Nice detailed explanation. :) – Zetland

I don't PHP often, but when I do, I waste half my night wondering why a statement that clearly returns results always returns 0... – Regardant

Answer 2

+1 to @leemo for answering first, but I'll expand the explanation a bit and mark my answer CW.

The MySQL client has no way of knowing how many rows are in the result set until it fetches all the rows. This is not because of PHP, actually -- it would be true even if you program in C using the MySQL client library directly.

So you either need to use mysqli_stmt_store_result() as @leemo says, which basically copies the full result set from the server to the client.

Alternatively, you could loop over mysqli_stmt_fetch() until you have fetched all rows. Then mysql_stmt_num_rows() will return the right number.

Unshakable