Fatal error: call to a member function fetch_array() on boolean
When you encounter the PHP fatal error call to a member function fetch_array() on boolean, it is almost always a signal that your database query returned nothing or failed, but your code treated the result as an object anyway. In the scenario provided, the script attempts to iterate over a result set from oc_product using fetch_array(). The error occurs specifically because $backup is not a mysqli_result object; it is a boolean false.
This happens when $mysqli->query($bup) fails or returns an empty set depending on the driver version and configuration. In older versions of MySQLi, returning an empty result set might behave differently than in modern setups, but relying on that behavior is a recipe for crashes. The immediate fix requires checking the return value of the query before attempting to fetch rows.
Diagnosing the Query Failure
The root cause lies in the $backup variable assignment:
$backup = $mysqli->query($bup);
while($row = $backup->fetch_array(MYSQLI_ASSOC)) {
// ...
}
If $bup contains a syntax error, references a non-existent table, or encounters a permission issue, query() returns false. When the code immediately proceeds to $backup->fetch_array(), PHP tries to call a method on false, triggering the fatal error.
To resolve this, you must validate the query result immediately after execution. A robust pattern involves checking if $backup is an object before entering the loop:
$backup = $mysqli->query($bup);
if (!$backup) {
// Handle error immediately
die('Query failed: ' . $mysqli->error);
}
while ($row = $backup->fetch_array(MYSQLI_ASSOC)) {
// Process data
}
However, there is a deeper architectural issue in the provided code that persists even after fixing the crash. The logic attempts to construct a massive REPLACE statement by concatenating values for every product in memory before executing it. This approach scales poorly and introduces significant security risks.
The String Concatenation Trap
The variable $megainsert is built by appending strings inside the while loop:
$megainsert .= "('...', '...'),";
If your database contains 5,000 products, this string grows to hundreds of kilobytes. While PHP can handle large strings, passing them directly to a SQL query creates two major problems:
- Memory Exhaustion: Constructing the full string in memory before sending it to the database consumes unnecessary RAM.
- SQL Injection Vulnerability: Although
real_escape_stringis used for individual fields, the structure of the SQL statement itself is constructed dynamically. If any field contains a single quote (e.g.,'O''Clock'), the concatenation logic breaks, or ifreal_escape_stringis misconfigured for the specific connection charset, the entire batch fails silently or corrupts the data.
The safer and more performant approach is to prepare the statement once and execute it inside a loop using bind_param. This allows the database driver to handle parameter binding securely and efficiently without constructing massive strings.
Optimizing with Prepared Statements
Instead of building one giant query, use a prepared REPLACE statement (or better yet, an INSERT ... ON DUPLICATE KEY UPDATE if you are strictly updating products) within the loop. Here is how you refactor the logic to be secure and scalable:
function backup()
{
global $mysqli;
// Define the SQL structure once
$sql = "REPLACE INTO oc_product_backup
(product_id, ean, image, model, status, price_sync, modified_by, date_modified, name, description, language_id, meta_description, meta_keyword, tag)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
return $mysqli->error;
}
// Bind parameters. Note: Use specific types for better performance and safety.
// i = integer, d = double (for floats), s = string
$stmt->bind_param("isssdissds",
$product_id, $ean, $image, $model,
$status, $price_sync, $modified_by, $date_modified,
$name, $description, $language_id, $meta_description, $meta_keyword, $tag
);
// Fetch and bind data row by row
while ($row = $backup->fetch_array(MYSQLI_ASSOC)) {
$product_id = $row['product_id'];
$ean = $row['ean'];
$image = $row['image'];
$model = $row['model'];
$status = $row['status'];
$price_sync = $row['price_sync'];
$modified_by= $row['modified_by'];
$date_modified = $row['date_modified'];
$name = $row['name'];
$description = $row['description'];
$language_id = $row['language_id'];
$meta_description = $row['meta_description'];
$meta_keyword = $row['meta_keyword'];
$tag = $row['tag'];
$stmt->execute();
}
$stmt->close();
return true;
}
By binding parameters, you eliminate the need for real_escape_string inside the loop (though it is harmless to keep if you are unsure of the context, prepared statements generally handle this). More importantly, you avoid building a massive string and ensure that special characters in data like descriptions or meta keywords do not break the SQL syntax.
Conclusion on Data Migration Patterns
The original code attempted to optimize by batching data into one query, but it ignored the trade-offs regarding memory usage and security. Modern MySQLi drivers support executing prepared statements repeatedly without a performance penalty comparable to string concatenation overhead. When implementing backup or migration scripts in PHP, prioritize safety through parameter binding over premature optimization of query construction. This ensures your application remains stable even when processing large datasets from tables like oc_product.