mysqli_stmt_num_rows($stmt) always returns 0?

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

?>

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.