Question 108684 in PHP Question posted in
Official manual can be found here.

PHP-Page long loading time due to modal queries and dynamic data from datatables

Previously I had a post about a query that loads very long due to queries. I was advised to use microtime() to check the parts which loads long. Unfortunately, I was not able to find any problem since everything is loading less than 0.03 seconds. Due to my long troubleshooting, the question got deleted automatically. After a lot of trial and error, I found that the issue was really with the modal having multiple queries.

What happens is that, I load the datatable via query. Now I have a column involving a button that pops-up with dynamic data based on the row selected. The modal is inside the loop within the datatable. The data in my datatable is around 2,000. Now, this modal also populates data which results in this long loading time. I was able to figure it out by mistakenly removed the modal position from the loop resulting making it load faster.

Scenario Code

<table>
   <thead></thead>
   <tbody>
      //insert query here
      //while loop here
      { -- note the brace
      <tr>
        <td>//random value</td>
        <td> <button>
             <modal>
        </td>        
      </tr>
      } -- note the brace
   </tbody>
</table>

Sample Code

<button type="button" class="btn btn-info" data-target="#updateProc<?php echo $row['id'] ?>" data-toggle="modal"><i class="fa-solid fa-pencil"></i></button>

<div class="modal fade" id="updateProc<?php echo $row['id'] ?>" tabindex="-1" role="dialog" aria-labelledby="updateProc" aria-hidden="true">
   <div class="modal-body">
     <form>
       <div class="form-group">
          <label for="sponsor">Select Value</label>
          <select class="form-control" name="value" >
          <option value="">Select value</option>
            <?php
              $query = mysqli_query($db,"Select id, val from tbl;");
              while ($row = mysqli_fetch_array($query)) {
               
               //will compare existing to table to showcase selected value
               if ($previous_row_from_query['id']==$row ['id']){   ?>
                <option value="<?php echo $row ['id']; ?>" selected><?php echo $row ['val']; ?></option>
               <?php }
                                                                                        
               else{?>
                <option value="<?php echo $row ['id']; ?>"><?php echo $row ['val']; ?></option>
               <?php }
                }?>
              </select>
              <small id="val" class="form-text text-muted ml-2">Choose value.</small>
           </div>
     </form>
   </div>
</div>

Currently, I have 6 queries involved in this modal with the same coding pattern since this is more for editing/updating the data. In the code above, there is also an if else statement wherein I used it to compare data from the retrieved value to the existing value to showcase the selected option.

Currently, I am trying to find a way for the data to not load earlier on the modal since removing the modal from the while loop solves the problem.

Is there a way to simplify the code wherein data will only load if it was called thru button click rather than populating on page load?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Okay. I was able to find a solution. Aside from the suggestion above (which I am currently studying), I have found an alternate solution. I found this tutorial

    So I created another page aside from the main page. For the main page, I modified the button to add a data-id. Then added an ajax that will trigger a blank modal and post data from main page to the secondary page.

    Code main.php

    <button data-id="<?php echo $row['id']; ?>" class="userinfo btn btn-success" data-toggle="tooltip" data-placement="top" title="Update Info"><i class="fa-solid fa-pencil"></i></button> 

<script>
    $(document).ready(function(){
        $(document).on('click', '.userinfo', function(e) {
            var userid = $(this).attr('id');
            $.ajax({
                url: 'secondary.php',
                type: 'post',
                data: {userid: userid},
                success: function(response){ 
                    $('.modal-body').html(response); 
                    $('#empModal').modal('show'); 
                }
            });
        });
    });
</script>

    Blank Modal in main

    <div class="modal fade" id="empModal" role="dialog">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h4 class="modal-title">Title</h4>
                    <button type="button" class="close" data-dismiss="modal">×</button>
                </div>
                <div class="modal-body">
                </div>
            </div>
        </div>
</div>

    Secondary page

    <?php
include(dirname(__DIR__).'/db.php');

$userid = $_POST['userid'];
//I inserted a select query here based on userId
//i declared the form here together with the queries

?>

  2. 0

    There are several patterns you can use here, depending on how your data is actually organized.

    For example:

    <option value="">Select value</option>
        <?php
          $query = mysqli_query($db,"Select id, val from tbl;");

    Above, your query seems to be exactly the same for all rows. So you can pre-calculate it outside the loop, save all rows in an array, and then use the array, just modifying the one line for the loop:

              foreach ($results as $row) {

    In general you might have N different queries out of M different table rows, with M >> N. Then you run the queries beforehand and save them in a sub-array, then choose which array:

              foreach ($results['cities'] as $row) {

          foreach ($results[$tableRow['customerType']] as $row {

    Another possibility is to populate the rows client side in Javascript, e.g. with custom libraries like Handlebars, or by hand. The advantage is that you can save transmission time for a good chunk of the HTML.

    Otherwise, you can do this in AJAX. You do not issue any queries at all, and just use a button to "unlock" the select. In the table, all relevant SELECTs are replaced by a button which might hold the current value, for show:

       <div class="ajaxize" data-query="...SQL..."><button>[value]</button></div>

    The query will have to be verified server side of course. You don’t want to get on little Bobby Tables’ bad side.

    Using e.g. jQuery, you activate class "ajaxize", so when you click on the value, the value is replaced by a SELECT:

       $('#tableId').on('click', '.ajaxize button', function() {
        const $div=$(this).closest('.ajaxize');
        const query=$div.attr('data-query');
        $.post("/utilities/ajaxized.php", {
           query: query
        }).then(reply => {
           // Create a select
           const $select = $('select').attr({ name: "selectName" });
           for (let i = 0; i < reply.rows.length; i++) {
               $select.append($('option').attr({ value: reply.rows[i].value }).text(reply.rows[i].text);
           }
           // Replace the button
           $div.html($select);
        });
   });

    You would also need to save the current value in another data-attribute to be able to pre-select the current value while looping through the OPTIONs.

    The PHP "ajaxized.php" will get a query and return a JSON array with the desired rows. You can use $.get() to place the reply in the browser cache, to further improve response times.

    You can also go down that road fully by using a DataTable library (there are several), that allows you to obtain pageable, responsive datatables with AJAX loading. While (necessarily) slower overall, the result can appear to be much faster and be less server-intensive. I personally favour this last approach.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search