The fetch_assoc Trap in PHP Prepared Statements
A common stumbling block when working with MySQLi prepared statements is a misunderstanding of where the result set lives after execution. Many developers, looking for a seamless transition from procedural to object-oriented styles, assume that $stmt->execute() returns the result set directly, allowing them to call $result->fetch_assoc() on the return value of execute. This assumption leads to the specific error seen in your logs: Call to a member function fetch_assoc() on a non-object.
In the MySQLi extension, mysqli_stmt::execute() does not behave like a query execution method that returns results. Instead, it performs two critical actions: it binds the parameters provided via bind_param, and it sends the SQL command to the server. Crucially, it returns a boolean indicating success or failure. It never instantiates a result set object. That is why attempting to call fetch_assoc() on the $rows variable immediately after execute failsâthe variable holds a boolean, not an object.
To retrieve data as an associative array (where you can access columns by name, e.g., $data['id']), you must store the result of the query execution in a dedicated variable before iterating. The corrected logic flow for your prepared statement looks like this:
if ($stmt = $this->mysqli->prepare("SELECT * FROM `mytable` WHERE `rows1`=?")) {
// Bind parameters first
$stmt->bind_param("i", $id);
// Execute the query and capture the result object
if ($stmt->execute()) {
$result = $stmt->get_result();
while ($data = $result->fetch_assoc()) {
$statistic[] = $data;
}
echo "<pre>";
var_dump($statistic);
echo "</pre>";
} else {
// Handle execution errors if needed
echo "Query failed: " . $stmt->error;
}
$stmt->close();
}
The key correction is the insertion of $result = $stmt->get_result();. This method explicitly fetches the mysqli_result object from the statement handle. Once you have this object, you can then use standard iteration methods like fetch_assoc() to build your array. Without calling get_result(), the connection pointer remains bound to the statement, and there is no intermediate result object to iterate upon.
It is worth noting a trade-off regarding performance and resource usage when using get_result(). This method requires the server to transmit all metadata about the columns (names, lengths, types) before any row data is sent. In older versions of PHP or specific MySQL configurations, this might involve an extra round-trip to the database server. While modern PHP 7+ and MySQLi drivers often handle this efficiently within a single packet stream, it is a distinct step in the lifecycle of a prepared statement that separates execution from result retrieval.
Another pitfall to avoid is mixing up the procedural mysqli_query style with the object-oriented mysqli_stmt style. If you were using raw queries (non-prepared), mysqli->query() does return the result set directly. However, once you switch to prepare(), that direct return behavior is lost for security and parameter binding reasons. You must always invoke get_result() explicitly if you intend to fetch rows immediately after execution.
If you are migrating legacy code or working within a framework like Laravel, understanding this distinction helps prevent runtime errors related to undefined methods. Frameworks often wrap database interactions, but the underlying MySQLi API contract remains strict: execute returns a boolean; get_result returns the data iterator. Ensuring your code adheres to this contract guarantees that associative arrays are populated correctly without triggering fatal errors regarding non-object method calls.