CODEWITHSUNDEEP
phpmysqliprepared-statement
Programit
Programit

Reputation: 575

Returning array and num rows mysqli prepared

I'm a bit new to the mysqli prepared statement and I would like to use fetch_array to return the results AND also return num_rows as an array value.

I have something like this

function getCategories($dbh, $catId) 
{
    $data = array();
    $s = "SELECT id, title FROM categories WHERE parent_id = ?";
    if ($stmt = mysqli_prepare($dbh, $s)) {
        mysqli_stmt_bind_param($stmt, "i", $catId);
        mysqli_stmt_execute($stmt);
        if (mysqli_stmt_errno($stmt)) {
            exit(mysqli_stmt_error($stmt));
        }
        mysqli_stmt_store_result($stmt);
        $count = mysqli_stmt_num_rows($stmt)) {
        if ($count) {
            $data['count'] = $count;
            $result = mysqli_stmt_get_result($stmt);
            while ($r = mysqli_fetch_assoc($result)) {
                $data[] = $r;
            }
        }

        return $data;
    } else {
        exit(mysqli_error($dbh));
    }
}

It seems that I cannot use mysqli_stmt_store_result and mysqli_stmt_get_result().

The store_result function seems to give a boolean and then I get this error: "mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given"

Hope this makes sense. Any help would be really appreciated.

Updated:

function getCategories($dbh, $catId) 
{
    $data = array();
    $s = "SELECT id, title FROM categories WHERE parent_id = ?";
    if ($stmt = mysqli_prepare($dbh, $s)) {
        mysqli_stmt_bind_param($stmt, "i", $catId);
        mysqli_stmt_execute($stmt);
        if (mysqli_stmt_errno($stmt)) {
            exit(mysqli_stmt_error($stmt));
        }

        $result = mysqli_stmt_get_result($stmt);
        $data['count'] = $result->num_rows;
        while ($r = mysqli_fetch_assoc($result)) {
            $data[] = $r;
        }

        return $data;
    } else {
        exit(mysqli_error($dbh));
    }
}

Upvotes: 0

Views: 297

Answers (1)

Skrat
Skrat

Reputation: 587

According to the PHP docs, mysqli_stmt_get_result returns FALSE on error:

Returns a resultset for successful SELECT queries, or FALSE for other DML queries or on failure. The mysqli_errno() function can be used to distinguish between the two types of failure.

You're then passing that into mysqli_fetch_assoc which complains because you're giving it a bool instead of the resultset it expects.

Do a little more erroring checking at that point and you'll be fine. There's probably something wrong with your SQL query. Call mysqli_errorno to determine if there's an error, as the docs state above.

EDIT:

Use the mysqli_error function to get a description of the mysql error. It would be best to use this everywhere you're checking for failure as having an error message will make debugging much easier than simply failing silently.

Upvotes: 1

Related Questions