Print result of prepared statement php (mysqli::num_rows not being set correctly)

Question

I've recently updated from normal statements

$result = mysql_query("SELECT user_id from users WHERE user_id = '$user_id'");

to prepared statements(security)

prepare("SELECT user_id FROM users WHERE user_id = ?");

I followed some tutorials, but this still won't work:

public function isUserRegistered($user_id) {
        print $user_id;
        $stmt = $this->conn->prepare("SELECT user_id FROM users WHERE user_id = ?");
        $stmt->bind_param("s", $user_id);
        if ($stmt->execute()) {
            $stmt->bind_result($user_id);
            $stmt->fetch();     

            $no_of_rows = $stmt->num_rows;
            print $no_of_rows;
            $stmt -> close();
            if ($no_of_rows > 0) {
                print "Finally";
                // user already registered
                return true;
            } else {
                print "Stupid";
                // user is not registered
                return false;
            }
        }
    }

The supplied id exists, because I can see it in the console being printed. The if ($stmt->execute()) is being executed, but for some reason nothing comes back.

How can I solve this and how can I print the result?

I've also tried:

while ($stmt->fetch()) {
        printf ("%s (%s)\n", $user_id);
    }

Answer 1

The issue is that $stmt->num_rows is not valid unless you store the result first as explained here: mysqli_stmt::$num_rows - Return the number of rows in statements result set

The fetch works fine and returns the rows of data as expected. Alas, the mun_rows property is zero so the isUserRegistered method fails. Even though it returned the correct data.

Note: changed to actually check the returned value from the fetch.

Conclusion:

• Use $stmt->store_result() to ensure $stmt->num_rows is useful.

• use the actual returned data to ensure it returned what you expected?

Example code: http://pastebin.com/wDvAru39

Final isUserRegistered code I used:

Code:

class UserRegister {

    protected $conn = null;

    public function __construct($dbConnection) 
    {
        $this->conn = $dbConnection;
    }   

    public function isUserRegistered($user_id) {
        print $user_id;
        $result_user_id = null;
        $stmt = $this->conn->prepare("SELECT user_id FROM users WHERE user_id = ?");
        $stmt->bind_param("s", $user_id);
        if ($stmt->execute()) {

            $stmt->store_result(); // need this to force the `num_rows` to be correct

            $stmt->bind_result($result_user_id);
            $stmt->fetch();     

            $no_of_rows = $stmt->num_rows; 

            var_dump(__METHOD__, 
                    'input user id: '. $user_id, 
                    'found user id: '. $result_user_id, 
                    'reported number of rows: '. ($no_of_rows),  __FILE__.__LINE__);

            $stmt->close();
            if (!empty($result_user_id)) { // check the returned data not the indicator
                print "Finally";
                // user already registered
                return true;
            } else {
                print "Stupid";
                // user is not registered
                return false;
            }
        }
    }    
}

Output from the var_dump statements:

Note: the store_result statement.

12321
string 'UserRegister::isUserRegistered' (length=30)
string 'input user id: 12321' (length=20)
string 'found user id: 12321' (length=20)
string 'reported number of rows: 1' (length=26)
string 'K:\developer\testmysql\index4.php78' (length=35)
Finally
string 'isUserRegistered : 

Comment out the 'store_result' statement gives:

12321
string 'UserRegister::isUserRegistered' (length=30)
string 'input user id: 12321' (length=20)
string 'found user id: 12321' (length=20)
string 'reported number of rows: 0' (length=26)
string 'K:\developer\testmysql\index4.php79' (length=35)
Finally
string 'isUserRegistered : true

Note: number of reported rows is zero.

Answer 2

Most tutorials are written by people whose knowledge is scarcely better than yours. And using num_rows() in the code is a sure sign of such a tutorial.

In fact, in PHP you don't need such a function ever.

Take, for example, your code. There is already a variable that can tell whether a user is registered or not - a selected user id. Which you can just return from the function. Or you can use just a constant value like this

public function isUserRegistered($user_id)
{
    $sql = "SELECT 1 FROM users WHERE user_id = ?";
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("s", $user_id);
    $stmt->execute();
    $stmt->bind_result($found);
    $stmt->fetch();     
    return $found;
}