PHP Warning : mysqli_num_rows() expects parameter 1 to be mysqli_result, object given

Question

I am trying to make some prepared statements using the LIKE keyword in sql but it's giving me:

Warning : mysqli_num_rows() expects parameter 1 to be mysqli_result, object given

PHP code:

if(isset($_POST['search_acc'])){
    $p = $_POST['search_text'];
    $search_text = "%p%";
    
    $stmt = $conn->prepare("SELECT * FROM accountants WHERE name LIKE ? ");
    $stmt->bind_param("s",$search_text);
    $stmt->execute();
}
if (mysqli_num_rows($stmt) > 0) {
    while ($RowaccountantsList = mysqli_fetch_assoc($stmt)) {
        $accountantFullName = $RowaccountantsList['name'];
        // and so on
    }
}

Answer 1

The problem:

The method mysqli::prepare returns an instance of the mysqli_stmt class, e.g. a prepared statement object. But the procedural function mysqli_num_rows requires an object of type mysqli_result as argument, e.g. a result set object.

The solution:

So, in order to be able to correctly call the function mysqli_num_rows, you'll first have to fetch a result set from the prepared statement by calling the method $stmt::get_result. The result set object ($result) can then be passed as argument to mysqli_num_rows:

<?php

$stmt->execute();

/*
 * Get the result set from the prepared statement.
 */
$result = $stmt->get_result();

if (mysqli_num_rows($result) > 0) {
    while ($RowaccountantsList = mysqli_fetch_assoc($result)) {
        $accountantFullName = $RowaccountantsList['name'];

        //...
    }
}

Fun fact:

You don't really need mysqli_num_rows() here, it's redundant. You can remove this condition altogether and outcome will be exactly the same. But get_result() is still needed, in order to let you fetch the actual data. So you can do just

$result = $stmt->get_result();
while ($RowaccountantsList = $result->fetch_assoc()) {
    $accountantFullName = $RowaccountantsList['name'];
    //...
}

Alternative solution:

Note that it's considered a better practice to accumulate the results into array first...

$stmt->execute();
$result = $stmt->get_result();
/*
 * Fetch all data at once and save it into an array.
 *
 * @link http://php.net/manual/en/mysqli-result.fetch-all.php
 */
$accountants = $result->fetch_all(MYSQLI_ASSOC);


/*
 * ...or fetch and save one row at a time.
 *
 * @link https://secure.php.net/manual/en/mysqli-result.fetch-array.php
 */
$accountants = [];
while ($accountant = $result->fetch_array(MYSQLI_ASSOC)) {
    $accountants[] = $accountant;
}

...and then use that data later, for example output it in HTML:

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Full Name</th>
            <th>City/Town</th>
            <th>TS Number</th>
            <th>NRC</th>
            <th>Profile Picture</th>
            <th>Registered Date</th>
        </tr>
    </thead>
    <tbody>
        <?php if ($accountants): ?>
            <?php foreach ($accountants as $accountant): ?>
                <tr>
                    <td><?= htmlspecialchars($accountant['id']) ?></td>
                    <td><?= htmlspecialchars($accountant['fullName']) ?></td>
                    <td><?= htmlspecialchars($accountant['cityTown']) ?></td>
                    <td><?= htmlspecialchars($accountant['tsNumber']) ?></td>
                    <td><?= htmlspecialchars($accountant['nrc']) ?></td>
                    <td><?= htmlspecialchars($accountant['profilePicture']) ?></td>
                    <td><?= htmlspecialchars($accountant['registeredDate']) ?></td>
                </tr>
            <?php endforeach ?>
        <?php else: ?>
            <tr>
                <td colspan="7">
                    No accountants found.
                </td>
            </tr>
        <?php endif ?>
    </tbody>
</table>

---

NOTA BENE:

$stmt::get_result available only with mysqlnd ("MySQL Native Driver")! If this is not installed, then uncomment extension=php_mysqli_mysqlnd.dll in PHP config file (php.ini) and restart web server (I assume Apache). Or use the following functions instead: mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch. See also on this topic:

• http://php.net/manual/en/mysqli-stmt.get-result.php
• Call to undefined method mysqli_stmt::get_result