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 = mysqli_real_escape_string($conn, $RowaccountantsList['name']);
                                    $accountantId = mysqli_real_escape_string($conn, $RowaccountantsList['ts_number']);
                                    $accountantCityTown = mysqli_real_escape_string($conn, $RowaccountantsList['city_town']);
                                    $DB_id = mysqli_real_escape_string($conn, $RowaccountantsList['id']);
                                    $nrc = mysqli_real_escape_string($conn, $RowaccountantsList['nrc']);
                                    $profile_pic = mysqli_real_escape_string($conn, $RowaccountantsList['profile_pic']);
                                    $RegisteredDate = mysqli_real_escape_string($conn, $RowaccountantsList['registered_date']);

}

html search form:

<form method="post" action="">
        <div id="custom-search-input">
            <div class="input-group col-md-12 align-center">
                <input name="search_text" type="text" class="form-control input-lg" placeholder="Enter email, phone or username" />
                <span class="input-group-btn">
                    <button name="search_acc" class="btn btn-info btn-lg" type="submit">
                        <i class="fa fa-search" aria-hidden="true"></i>
                    </button>
                </span>
            </div>
        </div>
    </div>
    </form>

The error message says all: you need to call the mysqli_num_rows() function on a result object, not on a statement object. — Shadow, Aug 15 '20 at 20:28

dakis · Answer 1 · 2020-09-02T14:14:24.793

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

if (isset($_POST['search_acc'])) {
    //...

    $stmt->execute();

    /*
     * Get the result set from the prepared statement.
     *
     * NOTA BENE:
     * 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) and
     * mysql service. Or use the following functions instead:
     * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
     *
     * @link http://php.net/manual/en/mysqli-stmt.get-result.php
     * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
     */
    $result = $stmt->get_result();

    if (mysqli_num_rows($result) > 0) {
        while ($RowaccountantsList = mysqli_fetch_assoc($stmt)) {
            // No need for "mysqli_real_escape_string" anymore, since the SQL statement is prepared using mysqli::prepare.
            $accountantFullName = $RowaccountantsList['name'];

            //...
        }
    }
}

Alternative solution:

<?php
if (isset($_POST['search_acc'])) {
    //...

    $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
     */
    // while ($accountant = $result->fetch_array(MYSQLI_ASSOC)) {
    //     $accountants[] = $accountant;
    // }
}
?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo</title>
    </head>
    <body>

        <form method="post" action="">
            <div id="custom-search-input">
                <div class="input-group col-md-12 align-center">
                    <input name="search_text" type="text" class="form-control input-lg" placeholder="Enter email, phone or username" />
                    <span class="input-group-btn">
                        <button name="search_acc" class="btn btn-info btn-lg" type="submit">
                            <i class="fa fa-search" aria-hidden="true"></i>
                        </button>
                    </span>
                </div>
            </div>
        </form>

        <h3>
            Accountants list
        </h3>

        <table class="accountants">
            <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) {
                    foreach ($accountants as $accountant) {
                        $id = $accountant['id'];
                        $fullName = $accountant['name'];
                        $cityTown = $accountant['city_town'];
                        $tsNumber = $accountant['ts_number'];
                        $nrc = $accountant['nrc'];
                        $profilePicture = $accountant['profile_pic'];
                        $registeredDate = $accountant['registered_date'];
                        ?>
                        <tr class="accountant">
                            <td><?php echo $id; ?></td>
                            <td><?php echo $fullName; ?></td>
                            <td><?php echo $cityTown; ?></td>
                            <td><?php echo $tsNumber; ?></td>
                            <td><?php echo $nrc; ?></td>
                            <td><?php echo $profilePicture; ?></td>
                            <td><?php echo $registeredDate; ?></td>
                        </tr>
                        <?php
                    }
                } else {
                    ?>
                    <tr>
                        <td colspan="7">
                            No accountants found.
                        </td>
                    </tr>
                    <?php
                }
                ?>
            </tbody>
        </table>

    </body>
</html>

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

1 Answers1

Linked

Related