MYSQLi bind_result allocates too much memory [duplicate]
I'm trying to fetch multiple rows from MYSQL, but when binding variables to the result, MYSQLi runs out of memory to allocate as it tries to fetch all rows at once and buffers the full LONGBLOB size even when not necessary.
The error is also discussed here. One poster seems to have solved the problem using mysqli_stmt_store_result
, but does not elaborate as to exactly how (and mysqli_stmt_store_result
is a procedural (not OO), method.
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes)
Ideally, I'd prefer to be using fetch_object()
anyway, but I can't figure out how to get it to function with my prepared statement.
public function display() {
$page_offset = ($this->get_开发者_如何学运维page_number()- 1)
* $this->notes_per_page;
if ($page_offset < 0) {
$page_offset = 0;
}
$sql = "SELECT title, date_posted, text, url
FROM notes ORDER BY date_posted DESC
LIMIT ?, ?";
$results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
$results->bind_result(&$title, &$date_posted, &$text, &$url);
//while ($row = $result->fetch_object()) { //store_result()) {
//echo 'success';
//var_dump($row);
//}
//$this->write($results);
}
// Here is the query function that $this->db->query() above refers to.
public function query() {
$args = func_get_args();
$statement = $this->db->prepare($args[0]);
$args = array_slice($args, 1);
call_user_func_array(array($statement, 'bind_param'), &$args);
$statement->execute();
return $statement;
}
Thanks for any and all help!
I've solved this by using the following code. There is still a problem as some returned data appears mangled, but I believe that deserves its own question. What was tricky for me was that store_result() needs to be called on the mysqli object, while fetch_object() needs to be called on the statement.
public function display() {
$page_offset = ($this->get_page_number()- 1)
* $this->notes_per_page;
if ($page_offset < 0) {
$page_offset = 0;
}
$sql = "SELECT title, date_posted, text, url
FROM notes ORDER BY date_posted DESC
LIMIT ?, ?";
$results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
$results = $this->db->store_result();
while ($row = $results->fetch_object()) {
var_dump($row);
}
//$this->write($results);
}
mysqli_stmt_store_result
has an OO form as well.
精彩评论