Solution to the Problem of PHP Running Out of Memory When Querying a Large Amount of Data

Content not enough prompt when querying a large amount of data from the database:

PHP Fatal error: Allowed memory size of 268 435 456 bytes exhausted

This issue on the PHP official website is called Buffered and Unbuffered Queries. The default query mode of PHP is buffered mode. That is to say, the query data results will be extracted all at once into memory for the PHP program to process. This gives the PHP program additional functions, such as calculating the number of rows, pointing to a certain row, etc. More importantly, the program can perform secondary queries and filtering operations on the data set repeatedly. However, the defect of this buffered query mode is that it consumes memory, that is, it uses space for speed.

On the other hand, another PHP query mode is unbuffered query, where the database server returns data one by one instead of all at once. This results in the PHP program consuming less memory, but it also increases the pressure on the database server because the database has to wait for the PHP program to fetch the data until all the data is fetched.

It is obvious that buffered query mode is suitable for small data volume queries, while unbuffered query is suitable for large data volume queries.

As everyone knows, buffered query mode in PHP is used for small data volume queries, and unbuffered query is suitable for large data volume queries.

　Unbuffered 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; 
  } 
} 
?> 

That's all for this article. Hope it will be helpful to everyone's study, and also hope everyone will support the Yelling Tutorial more.

Declaration: The content of this article is from the Internet, the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email to report abuse, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)