Is it possible to use mysqli_fetch_object with a prepared statement

Question

All the examples I see using mysqli_fetch_object use mysql_query(), I cannot get it to work with prepared statements. Does anyone know what is wrong with this code snippet, as fetch_object returns null.

$sql = "select 1 from dual";
printf("preparing %s\n", $sql);
$stmt = $link->prepare($sql);
printf("prepare statement %s\n", is_null($stmt) ? "is null" : "created");
$rc = $stmt->execute();
printf("num rows is %d\n", $stmt->num_rows);
$result = $stmt->result_metadata();
printf("result_metadata %s\n", is_null($result) ? "is null" : "exists");
$rc = $result->fetch_object();
printf("fetch object returns %s\n", is_null($rc) ? "NULL" : $rc);
$stmt->close();

The output is:

preparing select 1 from dual
prepare statement created
num rows is 0
result_metadata exists
fetch object returns NULL

Answer 1

I don't believe the interface works like that.

Going by the documentation and examples (http://www.php.net/manual/en/mysqli.prepare.php) it seems that $stmt->execute() does not return a resultset, but a boolean indicating success / failure (http://www.php.net/manual/en/mysqli-stmt.execute.php). To actually get the result, you need to bind variables to the resultset (aftere the execute call) using $stmt->bind_result (http://www.php.net/manual/en/mysqli-stmt.bind-result.php).

After you did all that, you can do repeated calls to $stmt->fetch() () to fill the bound variables with the column values from the current row. I don't see any mention of $stmt->fetch_object() nor do I see how that interface could work with a variable binding scheme like described.

So this is the story for "normal" result fetching from mysqli prepared statments.

In your code, there is something that I suspect is an error, or at least I am not sure you intended to do this. You line:

$result = $stmt->result_metadata();

assignes the resultset metadata, which is itself represented as a resultset, to the $result variable. According to the doc (http://www.php.net/manual/en/mysqli-stmt.result-metadata.php) you can only use a subset of the methods on these 'special' kinds of resultsets, and fetch_object() is not one of them (at least it is not explicitly listed).

Perhaps it is a bug that fetch_object() is not implemented for these metadata resultsets, perhaps you should file a bug at bugs.mysql.com about that.

Answer 2

This is the code I use to create an object from a prepared statement.
It could perhaps be used in a subclass of mysqli?

    $query = "SELECT * FROM category WHERE id = ?";
    $stmt = $this->_db->prepare($query);

    $value = 1;
    $stmt->bind_param("i", $value);

    $stmt->execute();

    // bind results to named array
    $meta = $stmt->result_metadata();
    $fields = $meta->fetch_fields();
    foreach($fields as $field) {
        $result[$field->name] = "";
        $resultArray[$field->name] = &$result[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $resultArray);

    // create object of results and array of objects
    while($stmt->fetch()) {
        $resultObject = new stdClass();

        foreach ($resultArray as $key => $value) {
            $resultObject->$key = $value;
        }

        $rows[] = $resultObject;
    }

    $stmt->close();