php - Cannot get the number of rows and fetch when using MySQLi prepared statement

Question

I want to get the number of rows from the database, but when I try to do this the $g_check variable will be equal to 0 and my code will echo the $sugg_title message which is in the else statement. But in the database there are 4 inserted groups so the num_rows property should return 4.

$sql = "SELECT DISTINCT gp.logo, gp.name
        FROM gmembers AS gm
        LEFT JOIN groups AS gp ON gp.name = gm.gname
        WHERE gp.creator != ? AND gm.mname != ? LIMIT 10";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$g_check = $stmt->num_rows;
if ($g_check > 0){
  $result = $stmt->get_result();
  while ($row = $result->fetch_assoc()) {
    $agList .= '<a href="group.php?g='.$row["name"].'"><img class="group_margin" src="groups/'.$row["name"].'/'.$row["logo"].'" alt="'.$row["name"].'" title="'.$row["name"].'" width="70" height="70" /></a>';
  }
}else{
  $sugg_title = "You have no group suggestions at the moment. Click ";
  $sugg_title .= '<a href="all_groups.php">here</a> to view all groups.';
}

I put the strore_result() and the fetch() functions after execute() but then I get this error message: "Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean"

Answer 1

If you want to use mysqli_stmt::$num_rows (that is, check the number of rows on the prepared statement), you need to use $stmt->store_result() after executing the prepared statement before being able to check the number of rows. That means that the result is stored into memory before we check how many rows was returned.

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$stmt->store_result(); // Need to store the result into memory first
if ($stmt->num_rows) {
    // ...

However, if you want to use mysqli_result::$num_rows (on the MySQLi-result you convert from the statement result), you need to do that after doing $result = $stmt->get_result();, and use $result->num_rows;, like shown below.

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss',$log_username,$log_username);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows) {
    while ($row = $result->fetch_assoc()) {
    // ....

In the end, they should both end up doing the same thing - provide a number of the rows returned by the original prepared query.

Note
It's important to note that you cannot use store_result() and get_result() on the same statement. Which means that in the first example, you can not convert to a mysqli-result object (by using get_result(), which allows you to use the standard fetch_assoc() method). As store_result() stores the result into memory, there are nothing for get_result() to convert, and vice-versa.

This means that if you use store_result(), you need to fetch through the statement-fetch, mysqli_stmt::fetch() and bind the results though mysqli_stmt::bind_result(). If you use get_result(), you should check the number of rows on the resulting MySQLi-result object (as shown in the second example).

You should therefor construct your code such that you only need to use one of them.

---

That being said, using affected_rows like suggested in the comments, isn't the right tool for the job - as per the manual on mysqli_stmt::$affected_rows (same thing applies for a regular query, mysqli::$affected_rows):

Returns the number of rows affected by INSERT, UPDATE, or DELETE query.
This function only works with queries which update a table. In order to get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead.

• PHP.net on mysqli_stmt::store_result()
• PHP.net on mysqli_stmt::get_result()
• PHP.net on mysqli_stmt::$num_rows