php - while loop taking too long to load

Question

I am having trouble in loading time. I tried removing the first if-else statement and it works Very fast but not when added back. I have more than 40000 rows in the tables.

while ($qry->fetch()){
    $form .= "
        <div class='formSections' id='formSections-$formSectionCounter'>
        <h4 style='text-align: center; color: white; width: 100%; font-size: 20px; margin: 10px auto;'>$sname</h4>
    ";

    $qry2 = $con->prepare("SELECT FieldId, FieldName, FieldType, FieldFilter, DisplaySubSection from qrp.agency_webform_section_fields where SectionId = ? ORDER BY FieldOrder ASC");
    // $response_array['data'] .= $con->error;
    $qry2->bind_param("s", $sid);
    $qry2->execute();
    $qry2->store_result();
    if ($qry2->num_rows > 0)
    {
        $qry2->bind_result($fid, $fname, $ftype, $ffilter, $DisplaySubSection);
        while ($qry2->fetch())
        {

            if($DisplaySubSection != ''){
                $form .= "
                    <h4 style='text-align: center; color: white; width: 100%; font-size: 20px; margin: 10px auto;'>$DisplaySubSection</h4>
                ";
            }

            unset($defval);
            unset($dov);
            unset($doid);
            unset($iof);
            unset($reqf);
            $qry3 = $con->prepare("SELECT DefaultValue,IncludeOnForm,Required from qrp.agency_webform_fields where WebformId = ? and FieldId = ?");
            $qry3->bind_param("ss", $wfid, $fid);
            $qry3->execute();
            $qry3->store_result();
            if ($qry3->num_rows > 0)
            {
                //FOUND FIELD IN Webform
                $qry3->bind_result($defval, $iof, $reqf);
                $qry3->fetch();
                if ($defval != '')
                {
                    $qry4 = $con->prepare("SELECT OptionValue,OptionId from qrp.agency_webform_field_options where OptionId = ?");
                    $qry4->bind_param("s", $defval);
                    $qry4->execute();
                    $qry4->store_result();
                    if ($qry4->num_rows > 0)
                    {
                        $qry4->bind_result($dov, $doid);
                        $qry4->fetch();
                    }
                    else
                    {
                        $dov = $defval;
                    }

                }
            }

            $form .= "
                <div class='BothinputsAndLabelsAndCheckbox'>
                <div class='inputsAndLabels'>
                    <label>$fname</label>";
                    if ($ftype == 'String' || $ftype == 'INT'){
                        $form .= "
                            <input name='$fid-default' id='$fid-default' type='text' class='' value='$dov' data-toggle='tooltip' data-placement='top' title='If you set an answer here, it will be the default value in the form'>
                        ";
                    } else if ($ftype == 'Date'){
                        $date = date('Y-m-d', strtotime($dov));
                        $form .= "
                            <input name='$fid-default' id='$fid-default' type='date' class='' value='$date' data-toggle='tooltip' data-placement='top' title='If you set an answer here, it will be the default value in the form'>
                        ";
                    } else if ($ftype == 'Checkbox'){
                        $form .= "
                            <input name='$fid-default' id='$fid-default' type='checkbox' class='' value='$dov' data-toggle='tooltip' data-placement='top' title='If you set an answer here, it will be the default value in the form'>
                        ";
                    } else if ($ftype == 'SelectList'){
                        $form .= "
                        <select name='$fid-default' id='$fid-default' class='' data-toggle='tooltip' data-placement='top' title='If you set an answer here, it will be the default value in the form'>";
                            $qry3 = $con->prepare("SELECT OptionValue,OptionId,FieldFilterId from qrp.agency_webform_field_options where FieldId = ? Order By OptionValue");
                            $qry3->bind_param("s", $fid);
                            $qry3->execute();
                            $qry3->store_result();
                            $form .= "<option value=''>Please Select an Option</option>";
                            if ($qry3->num_rows > 0)
                            {
                                $qry3->bind_result($optv, $optid, $filid);
                                while ($qry3->fetch())
                                {
                                    if (!isset($defval))
                                    {
                                        $form .= "<option value='$optid' id='$filid' class='$filid'>$optv</option>";
                                    }
                                    else
                                    {
                                        if ($doid == $optid)
                                        {
                                            $form .= "<option value='$optid' id='$filid' class='$filid' selected>$optv</option>";
        
                                        }
                                        else
                                        {
                                            $form .= "<option value='$optid' id='$filid' class='$filid'>$optv</option>";
        
                                        }
                                    }
                                }
                            }
                            $form .= "                          
                        </select>
                        ";
                    }
                    $form .= "
                </div>"; // end inputsAndLabels div
                    $form .= "
                        <div class=''>
                            <div class='includeDiv'>
                                <input type='checkbox' class='includeButton' id='$fid-include' data-value='$fid' name='$fid-include' "; if ($iof > 0){ $form .= " checked"; }$form .= " data-toggle='tooltip' data-placement='top' title='If you set an answer here, it will be the default value in the form'>
                                <label class='' for='$fid-include'> Include</label>
                            </div>";
                            if ($ftype != 'Checkbox'){
                                $form .= "
                                <div class='requiredDiv'>
                                    <input type='checkbox' class=' requiredButton' id='$fid-required' data-value='$fid' name='$fid-required' "; if ($reqf > 0){ $form .= " checked"; }$form .= " data-toggle='tooltip' data-placement='top' title='If you set an answer here, it will be the default value in the form'>
                                    <label class='' for='$fid-required'> Require </label>
                                </div>
                                ";
                            }
                            $form .= "
                        </div>
            </div>
            ";
        }
    }
    $form .= "
    </div> <!-- close formSections -->
    ";

    $formSectionCounter++;
}

question from:https://stackoverflow.com/questions/65851271/while-loop-taking-too-long-to-load

Answer 1

If you are doing a loop over 40000 Rows, this is basically not a problem. The Problem begins if, for every fetched row, you prepare a new SQL Statement to select related information for each row id. In your example, you are creating at least 40000 times the number of rows in subsequent SQL selects, new select statements, and that is slowing down the page load

You can solve it, by extending your first SQL Statement with a Left Join or a subselect to get all Data within one SQL fetch iteration.

I suggest saving your results into arrays. Don't mix up SQL preparation and HTML Markup. This makes the code much cleaner, and once you got your data with optimized SQL into arrays, you can search for the problem much better. Create your HTML Markup as usual with nested loops, but inside those loops access your Arrays, which you have prefilled with the Data from the Database.