Bogdan Daniel Bogdan Daniel - 7 months ago 15
PHP Question

Print result of prepared statement php

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

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.