php - MySQLi SELECT statement requires LIMIT

Question

I'm trying to display a listing of 6 columns from a database table. This table happens to have a bit over 30,000 records. What I've used in the past worked but this time the list ends at 10,939 records and won't display at all unless I add the LIMIT clause to the line.

The table name is phpbb_users and the column I'm sorting on is username. The goal is to display a listing of everyone, displaying about 8 other data fields. If I use this code:

$sql = "SELECT * FROM phpbb_users ORDER BY username";
$result = $conn->query($sql);

and then follow with the usual while ($row = $result->fetch_assoc()) stuff that displays every line the query fails and no listing appears.

If I change the code to:

$sql = "SELECT * FROM phpbb_users ORDER BY username LIMIT 10000";

the listing appears, but it only displays the first 10,000 records.

Is there some kind of limit to how many records can be read in a MySQLi SELECT query? I didn't have this problem when I was running PHP 5.6 and MySQL but I just upgraded to PHP 7.2 and MySQLi and the new code isn't working the same way.

Answer 1

I did check the error_log in FileZilla. It appears to be a memory limit.

[12-Jan-2021 13:14:12 UTC] PHP Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 39828320 bytes) in /home/tiffinrv/public_html/staff/member_search.php on line 202

In the past an admin would display the list and use CTRL-F to find the record they wanted. I plan on writing some code to make that easier to do by using a SELECT and INNER JOIN to access that info from two tables but first needed to see why this no longer worked. But next I'm going to have to figure out what to do about the memory limit. It worked fine before upgrading but it appears that the newer version has more overhead and leaves less memory available for this. Hopefully have enough RAM on the server and just can get it configured/allocated better.

I noticed another set of errors in the log.

[12-Jan-2021 13:14:11 UTC] PHP Warning: Use of undefined constant localhost - assumed 'localhost' (this will throw an Error in a future version of PHP) in /home/tiffinrv/public_html/staff/member_search.php on line 54

I have assigned values to variables used in my database connection. It works just fine as I have in the past but this error warns of future issues. From what I gather I may need to define them, such as define('USERNAME','myname;'); rather than just establish values, such as $username = 'myname'; to prevent issues with future upgrades?