How to solve the problem of memory exhaustion when looping a large amount of data in PHP_PHP Tutorial

How to solve the problem of memory exhaustion when looping a large amount of data in PHP

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

<ol class="dp-j"><li class="alt"><span><span>PHP Fatal error: Allowed memory size of </span><span class="number">268</span><span> </span><span class="number">435</span><span> </span><span class="number">456</span><span> bytes exhausted </span></span></li></ol>

The error message shows that the maximum allowed memory has been exhausted. I was surprised to encounter such an error at first, but after thinking about it, it is not surprising, because the program I am developing is to use a foreach loop statement to search the entire table in a table with 40,000 records. For data with specific characteristics, that is to say, 40,000 pieces of data must be taken out at a time, and then the daily data must be checked one by one. It is conceivable that if all 40,000 pieces of data are loaded into the memory, it would be strange if the memory does not burst.

After all these years of programming, I vaguely remember that PHP provides an API that does not load data all at once. It is a query method that can be used and lost like streaming media, and the data does not accumulate in the memory. After a simple search, I found the correct usage on the official website.

This problem is called Buffered and Unbuffered queries on PHP’s official website. PHP's default query mode is buffered mode. In other words, the query data results will be extracted into memory all at once for processing by the PHP program. This gives the PHP program additional functions, such as counting the number of rows, pointing the pointer to a certain row, etc. What's more important is that the program can repeatedly perform secondary queries and filtering operations on the data set. However, the disadvantage of this buffered query mode is that it consumes memory, that is, it trades space for speed.

In contrast, another PHP query mode is a non-buffered query. The database server will return data one by one instead of all at once. The result is that the PHP program consumes less memory, but it increases the load of the database server. Pressure, because the database will keep waiting for PHP to fetch the data until all the data is fetched.

Obviously, the buffered query mode is suitable for queries with small data volumes, while non-buffered queries are suitable for queries with large data volumes.

Everyone knows about PHP’s buffered mode query. The example listed below is how to execute the non-buffered query API.

Non-buffered query method one: mysqli

<ol class="dp-j"><li class="alt"><span><span><?php </span></span></li><li><span>$mysqli  = <span class="keyword">new</span><span> mysqli(</span><span class="string">"localhost"</span><span>, </span><span class="string">"my_user"</span><span>, </span><span class="string">"my_password"</span><span>, </span><span class="string">"world"</span><span>); </span></span></li><li class="alt"><span>$uresult = $mysqli->query(<span class="string">"SELECT Name FROM City"</span><span>, MYSQLI_USE_RESULT); </span></span></li><li><span> </span></li><li class="alt"><span><span class="keyword">if</span><span> ($uresult) { </span></span></li><li><span>   <span class="keyword">while</span><span> ($row = $uresult->fetch_assoc()) { </span></span></li><li class="alt"><span>       echo $row[<span class="string">'Name'</span><span>] . PHP_EOL; </span></span></li><li><span>   } </span></li><li class="alt"><span>} </span></li><li><span>$uresult->close(); </span></li><li class="alt"><span>?> </span></li></ol>

Non-buffered query method two: pdo_mysql

<ol class="dp-j"><li class="alt"><span><span><?php </span></span></li><li><span>$pdo = <span class="keyword">new</span><span> PDO(</span><span class="string">"mysql:host=localhost;dbname=world"</span><span>, </span><span class="string">'my_user'</span><span>, </span><span class="string">'my_pass'</span><span>); </span></span></li><li class="alt"><span>$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, <span class="keyword">false</span><span>); </span></span></li><li><span> </span></li><li class="alt"><span>$uresult = $pdo->query(<span class="string">"SELECT Name FROM City"</span><span>); </span></span></li><li><span><span class="keyword">if</span><span> ($uresult) { </span></span></li><li class="alt"><span>   <span class="keyword">while</span><span> ($row = $uresult->fetch(PDO::FETCH_ASSOC)) { </span></span></li><li><span>       echo $row[<span class="string">'Name'</span><span>] . PHP_EOL; </span></span></li><li class="alt"><span>   } </span></li><li><span>} </span></li><li class="alt"><span>?> </span></li></ol>

Non-buffered query method three: mysql

<ol class="dp-j"><li class="alt"><span><span><?php </span></span></li><li><span>$conn = mysql_connect(<span class="string">"localhost"</span><span>, </span><span class="string">"my_user"</span><span>, </span><span class="string">"my_pass"</span><span>); </span></span></li><li class="alt"><span>$db   = mysql_select_db(<span class="string">"world"</span><span>); </span></span></li><li><span> </span></li><li class="alt"><span>$uresult = mysql_unbuffered_query(<span class="string">"SELECT Name FROM City"</span><span>); </span></span></li><li><span><span class="keyword">if</span><span> ($uresult) { </span></span></li><li class="alt"><span>   <span class="keyword">while</span><span> ($row = mysql_fetch_assoc($uresult)) { </span></span></li><li><span>       echo $row[<span class="string">'Name'</span><span>] . PHP_EOL; </span></span></li><li class="alt"><span>   } </span></li><li><span>} </span></li><li class="alt"><span>?> </span></li></ol>

---

---

http://www.bkjia.com/PHPjc/1031254.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1031254.htmlTechArticleHow to solve the problem of memory exhaustion when looping a large amount of data in PHP. Recently, I encountered the following when developing a PHP program. Error: PHPFatalerror:Allowedmemorysizeof 268 435 456 bytesexhaust...