使用php和mysql检索批量数据时浏览器冻结

Question

Good day, I have billing system that only works when the data in the database is not much but when I import a full items of data into the table the browser freeze and takes a long time to retrieve what I search, I have tried to add limit to my query as suggested here:

MySQL & PHP load more then 1000 items makes browser freeze

but the problem with that is, it does not retrieved all data from the table as its limited.

is there any other workaround that I can try?

Here is my piece of code:

<?php
    $query = "SELECT * FROM medications ";
    $result = $con->query($query);
?> 
<select id="disease"  style="width: 40%;" name="disease" required="true" data-toggle="tooltip" data-original-title="medications" class="date-picker form-control col-md-7 col-xs-12" data-rel="chosen">
    <option value="">Select Disease</option>
    <?php while ($row = $result->fetch_array(MYSQLI_ASSOC)) { ?>
        <option value="<?php echo $row['ICD10'] ?>"><?php echo $row['diagnosis']; ?> </option> 
    <?php } ?> 
</select>

Answer 1

If your browser itself freezes, meaning that the whole browser becomes unresponsive, it simply means that your browser itself is running out of capacity (likely due to low RAM on your 'puter). There's not much you can do about that in PHP/MySQL itself. Some things you can try:

• Optimize your HTML output to make it less heavy to parse where possible.
• Buffer up your HTML and output in one go in the end, instead of echoing incrementally.
• Load your data on-demand with the help of AJAX, instead of blurting out everything in one page-load. (Possibly remove older sections when new data is loaded.)
• Review how your CSS and jQuery(?) work. I see there's something going on with your data-toggle attribute. Dynamically applying a ton of events/classes will lead to issues.

And if by "freeze" you mean that the script takes a long time to execute... I see you query for all fields in the table, SELECT *. You can make it lighter for MySQL and your server RAM by only selecting the fields you actually require (SELECT ICD10, diagnosis ...). Also consider adding in pagination and using LIMIT in your query. (Sorry: That's not relevant here, since you're using the data for <select> options.) There's not a whole lot that can be said about your PHP here, but do look around and see if there are possible bottlenecks outside the code snipped you've posted.