Search code examples
phpmysqlprepared-statement

Prepared statement returns no results

I have been chasing my tale with this for a long time. I have not been able to find an issue with this code:

 $query = "SELECT * FROM CUSTOMER WHERE username = ?";
 $stmt = $db->prepare($query);
 $stmt->bind_param("s", $username);
 $stmt->execute();
 echo $stmt->num_rows." ".$username;`

The CUSTOMER table in my database has three columns: username, pwd, and email. But nonetheless, no results are returned when I assign the $username variable to a value I know exists in the database. I know it exists because this query

$results = $db->query("SELECT * FROM CUSTOMER WHERE username ='$username'");
echo $results->num_rows;

Displays one row, which is what is expected. Can anybody please tell me why my prepared statement will not produce the correct results? I have tried several variations of quoting, not quoting, hardcoding the variable's value, but nothing works. I am on a university server so I have no control over PHP's or MySQL's settings, but I'm not sure that has anything to do with it. It seems like a coding issue, but I can't see anything wrong with the code.

Solution

  • num_rows will be populated only when you execute $stmt->store_result();.

    However, 99.99% of the time you do not need to check num_rows. You can simply get the result with get_result() and use it.

    $query = "SELECT * FROM CUSTOMER WHERE username = ?";
$stmt = $db->prepare($query);
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
foreach($result as $row) {
    // ...
}

    If you really want to get the num_rows, you can still access this property on the mysqli_result class. 

    $result = $stmt->get_result();
$result->num_rows;