How to solve the problem of memory exhaustion when a large number of data loops in PHP

I recently encountered the following error while developing a PHP program:

  
   
   

   
    
    
PHP Fatal error:allowed Memory size of 268 435 456 bytes Exhausted
  
   
   

The error message shows that the maximum allowable memory has been exhausted. I was surprised at first by such a mistake, but in the blink of an eye, it is not surprising, because I am developing the program is to use a foreach circular statement in a 40,000-record table search for a specific feature of the data, that is, one time to take out 40,000 data, and then check each day data. It can be imagined that 40,000 data are all loaded into memory, memory does not explode to blame.

After all the programming for so many years, I vaguely remember that PHP provides an API that does not load all of the data at once, and is a query method that, as with streaming media, is used with lost data and does not accumulate in memory. After a simple search, sure enough, the correct usage was found on the official website.

The problem is called the buffer and non-buffering queries (buffered and unbuffered queries) on the official PHP website. PHP's query default mode is buffering mode. In other words, the query data will be extracted all at once to the memory for the PHP program processing. This gives the PHP program extra functionality, such as counting the number of rows, pointing the pointer at a row, and so on. More importantly, the program can repeatedly two times query and filter data set operations. But this kind of buffer query pattern flaw is consumes the memory, namely uses the space to change the speed.

In contrast, another type of PHP query mode is not buffer query, the database server will be a piece of the return data, rather than return all at once, the result is that the PHP program consumes less memory, but it increases the pressure of the database server, because the database will be waiting for PHP to fetch data, Until all the data has been taken out.

Obviously, the buffer query mode is suitable for small data query, but not buffer query is suitable for large amount of data query.

For the PHP buffer mode query, as you know, the following example illustrates how to execute a non-buffered query API.

Non-buffered Query method one: mysqli

 
  
  

  
   
   
<?php
  
   
   
$mysqli = new Mysqli ("localhost", "My_user", "My_password", "World");
  
   
   
$uresult = $mysqli->query ("SELECT Name from City", Mysqli_use_result);
  
   
   

  
   
   
if ($uresult) {
  
   
   
while ($row = $uresult->fetch_assoc ()) {
  
   
   
echo $row [' Name ']. Php_eol;
  
   
   
}
  
   
   
}
  
   
   
$uresult->close ();
  
   
   
?>
 
  
  

Non-Buffered Query method two: Pdo_mysql

 
  
  

  
   
   
<?php
  
   
   
$pdo = new PDO ("Mysql:host=localhost;dbname=world", ' my_user ', ' my_pass ');
  
   
   
$pdo->setattribute (Pdo::mysql_attr_use_buffered_query, false);
  
   
   

  
   
   
$uresult = $pdo->query ("SELECT Name from City");
  
   
   
if ($uresult) {
  
   
   
while ($row = $uresult->fetch (PDO::FETCH_ASSOC)) {
  
   
   
echo $row [' Name ']. Php_eol;
  
   
   
}
  
   
   
}
  
   
   
?>
 
  
  

Non-Buffered Query method three: MySQL

 
  
  

  
   
   
<?php
  
   
   
$conn = mysql_connect ("localhost", "My_user", "My_pass");
  
   
   
$db = mysql_select_db ("World");
  
   
   

  
   
   
$uresult = Mysql_unbuffered_query ("SELECT Name from City");
  
   
   
if ($uresult) {
  
   
   
while ($row = Mysql_fetch_assoc ($uresult)) {
  
   
   
echo $row [' Name ']. Php_eol;
  
   
   
}
  
   
   
}
  
   
   
?>