Current Location: Home> Latest Articles> How to Effectively Prevent PHP Memory Overflow with mysqli::use_result Function?

How to Effectively Prevent PHP Memory Overflow with mysqli::use_result Function?

gitbox 2025-08-27 
<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-comment">// This part of the code is unrelated to the article content, only used as a placeholder example</span></span><span>
<p></span>echo "Welcome to this article, which explores how to use the mysqli::use_result function to prevent PHP memory overflow issues.";</p>
<p>?></p>
<p><hr></p>
<p><?php<br>
/*</p>
<ul>
<li>
<p>How to effectively prevent PHP memory overflow with mysqli::use_result function?</p>
</li>
<li></li>
<li>
<p>In PHP, when working with MySQL databases, it is common to encounter memory overflow issues when dealing with large query result sets.</p>
</li>
<li>
<p>Especially when using the mysqli extension for queries that return large datasets, loading all results into memory at once can easily exhaust PHP memory and crash the application.</p>
</li>
<li></li>
<li>
<p>mysqli provides two ways to retrieve query results: store_result() and use_result().</p>
</li>
<li></li>
<li>
<ol>
<li>
<p>store_result():</p>
</li>
</ol>
</li>
<li>
<p>This is the default method, which retrieves the entire result set at once and caches it in client memory.</p>
</li>
<li>
<p>For large datasets, memory consumption can be extremely high.</p>
</li>
<li></li>
<li>
<ol start="2">
<li>
<p>use_result():</p>
</li>
</ol>
</li>
<li>
<p>Retrieves results row by row without fetching everything at once, instead streaming data over the network.</p>
</li>
<li>
<p>This significantly reduces memory usage and helps prevent memory overflow.</p>
</li>
<li></li>
<li>
<p>This article focuses on how to use mysqli::use_result() properly to avoid memory overflow problems.<br>
*/</p>
</li>
</ul>
<p>/**</p>
<ul>
<li>
<p>Example code using mysqli::use_result<br>
*/<br>
$mysqli = new mysqli('localhost', </span>'username', </span>'password', </span>'database');</p>
</li>
</ul>
<p></span>if ($mysqli->connect_errno) {<br>
die(</span>"Connection failed: " . </span>$mysqli->connect_error);<br>
}</p>
<p></span>// Execute query<br>
if ($mysqli-></span>real_query(</span>"SELECT * FROM large_table")) {</p>
</span><span><span class="hljs-variable">$result</span></span> = </span><span><span class="hljs-variable">$mysqli</span></span>-&gt;</span><span><span class="hljs-title function_ invoke__">use_result</span></span>();

</span><span><span class="hljs-keyword">if</span></span> (</span><span><span class="hljs-variable">$result</span></span>) {
    </span><span><span class="hljs-comment">// Process rows one by one to minimize memory usage</span></span><span>
    </span><span><span class="hljs-keyword">while</span></span> (</span><span><span class="hljs-variable">$row</span></span> = </span><span><span class="hljs-variable">$result</span></span>-&gt;</span><span><span class="hljs-title function_ invoke__">fetch_assoc</span></span>()) {
        </span><span><span class="hljs-comment">// Handle each row, e.g., output or write to file</span></span><span>
        </span><span><span class="hljs-comment">// echo $row[&#039;column_name&#039;] . PHP_EOL;</span></span><span>
    }

    </span><span><span class="hljs-comment">// Free result set resources</span></span><span>
    </span><span><span class="hljs-variable">$result</span></span>-&gt;</span><span><span class="hljs-title function_ invoke__">free</span></span>();
} </span><span><span class="hljs-keyword">else</span></span> {
    </span><span><span class="hljs-keyword">echo</span></span> </span><span><span class="hljs-string">"Failed to retrieve results: "</span></span> . </span><span><span class="hljs-variable">$mysqli</span></span>-&gt;error;
}

} else {
echo "Query failed: " . $mysqli->error;
}

// Close connection
$mysqli->close();

/*

  • Detailed explanation:

    1. Streaming retrieval:

  • With use_result(), the MySQL server does not send the entire result set to the client at once, but keeps the connection open,

  • allowing the client to read row by row. This prevents large amounts of data from accumulating in memory.

    1. Notes:

    • When using use_result(), you must read all results or call free() before executing a new query.

    • Do not mix store_result() and use_result() to avoid blocking the connection.

    • The network connection must remain active and uninterrupted during reading.

    1. Performance comparison:

  • Compared with store_result(), use_result() sacrifices some performance (due to multiple network interactions),

  • but significantly reduces memory usage, making it suitable for large datasets.

    1. Suitable scenarios:

    • When the query result is too large to load all at once.

    • When results need to be processed in chunks, such as writing to files or exporting in pages.

  • Conclusion:

  • Using mysqli::use_result() for streaming queries is an effective way to prevent PHP memory overflow.

  • It allows developers to fetch data row by row, reducing memory peaks and improving application stability.
    */
    ?>

Related
Latest Articles