How to use mysql query multiple times

Question

I have this select menu in a for loop, so the query is running multiple times. And a lot of more of these queries so page loads very slow.

<div class="col-md-4">
    <label for="edit_kwaliteit_id['.$i.']">Kwaliteit</label>
    <div class="form-group input-group has-success has-feedback" id="edit_kwaliteit_id_div['.$i.']">
        <span class="input-group-addon"><span style="display: inline-block; width:15px; text-align:center;" class="fab fa-squarespace" aria-hidden="true"></span></span>

          <select class="form-control" id="edit_kwaliteit_id['.$i.']" name="edit_kwaliteit_id" onchange="validate_edit_table_1(this, '.$i.')">';

              $sql_kwal = "SELECT
                            id,
                            kwaliteit
                            FROM kwaliteit
                            ORDER BY kwaliteit ASC
                            ";

                if(!$res_kwal = mysqli_query($mysqli, $sql_kwal)) { echo '<div class="alert alert-danger text-center" role="alert">Er is helaas iets fout gegaan.</div>'; }

                while($row_kwal = mysqli_fetch_array($res_kwal)) {

                    echo '<option ';if($row_table_1['kwaliteit_id'] == $row_kwal['id']) { echo 'selected="selected"';} echo 'value="'.$row_kwal['id'].'">'.$row_kwal['kwaliteit'].'</option>';
                }
                echo '
          </select>

        <span class="glyphicon glyphicon-ok form-control-feedback" id="edit_kwaliteit_id_glyp['.$i.']"></span>
    </div>
</div>

Since the query is the same everytime I wanted to move the query out of the loop and place the query itself before the loop. Something like

<?php
// some code
$sql_kwal = "SELECT
            id,
            kwaliteit
            FROM kwaliteit
            ORDER BY kwaliteit ASC
            ";

if(!$res_kwal = mysqli_query($mysqli, $sql_kwal)) { echo '<div class="alert alert-danger text-center" role="alert">Er is helaas iets fout gegaan.</div>'; }

// start loop

<div class="col-md-4">
    <label for="edit_kwaliteit_id['.$i.']">Kwaliteit</label>
    <div class="form-group input-group has-success has-feedback" id="edit_kwaliteit_id_div['.$i.']">
        <span class="input-group-addon"><span style="display: inline-block; width:15px; text-align:center;" class="fab fa-squarespace" aria-hidden="true"></span></span>

          <select class="form-control" id="edit_kwaliteit_id['.$i.']" name="edit_kwaliteit_id" onchange="validate_edit_table_1(this, '.$i.')">';

                while($row_kwal = mysqli_fetch_array($res_kwal)) {

                    echo '<option ';if($row_table_1['kwaliteit_id'] == $row_kwal['id']) { echo 'selected="selected"';} echo 'value="'.$row_kwal['id'].'">'.$row_kwal['kwaliteit'].'</option>';
                }
                echo '
          </select>

        <span class="glyphicon glyphicon-ok form-control-feedback" id="edit_kwaliteit_id_glyp['.$i.']"></span>
    </div>
</div>

// end loop
?>

But now while($row_kwal = mysqli_fetch_array($res_kwal)) gives an empty result. Any suggestions to keep one query instead of an query in each loop?

Could be a scope issue. Try changing if(!$res_kwal = mysqli_query($mysqli, $sql_kwal)) {... to $res_kwal = mysqli_query($mysqli, $sql_kwal); if (!$res_kwal) {... — ADyson
– ADyson, Commented Feb 3, 2020 at 11:58

Nick · Accepted Answer · 2020-02-03 12:32:36Z

3

You should read all the rows into an array when you first run the query, and then iterate that array in your for loop e.g.

$sql_kwal = "SELECT
            id,
            kwaliteit
            FROM kwaliteit
            ORDER BY kwaliteit ASC
            ";

if(!$res_kwal = mysqli_query($mysqli, $sql_kwal)) { echo '<div class="alert alert-danger text-center" role="alert">Er is helaas iets fout gegaan.</div>'; }
$rows = array();
while ($row = mysqli_fetch_array($res_kwal)) {
    $rows[] = $row;
}

Then in your loop, replace

while ($row = mysqli_fetch_array($res_kwal)) {

with

foreach ($rows as $row) {

Note that if you have the MySQL native driver (mysqlnd) installed, you can use

$rows = mysqli_fetch_all(MYSQLI_BOTH);

in place of the while loop to load the $rows array.

Note

If you have a particularly large result set that is impractical to load into memory, then you can instead use mysqli_data_seek to reset the data pointer before you start reading from the result set. So where you have

while ($row = mysqli_fetch_array($res_kwal)) {

change it to

mysqli_data_seek($res_kwal, 0);
while ($row = mysqli_fetch_array($res_kwal)) {

edited Feb 3, 2020 at 12:32

answered Feb 3, 2020 at 11:59

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jamie Belcher Over a year ago

Wouldn't it be better to iterate over each row without loading into an array? This way you don't run into problems on large result sets. I'm not seeing a LIMIT BY in the query...

Nick Over a year ago

@JamieBelcher as I understand OPs question he is executing the same query multiple times, which is slow. The intention of this is to fetch the data once and then read it from the array each time it is required after the first time.

Nick Over a year ago

@JamieBelcher I've added an alternate solution using mysqli_data_seek should the size of the result set be an issue. Given that it is being used to make a select, I think it's unlikely but it doesn't hurt to have options.

Muiter Over a year ago

Very, very nice :)

Nick Over a year ago

@Muiter :-) I'm glad you like it.

failedCoder · Accepted Answer · 2020-02-03 12:02:10Z

It seems that the code you provided here lacks the outer loop, so I'm going to work with what I have.

If that loop really slows down your page you should consider using ajax to load the content of the menu after the page has loaded, something like this:

<select class="form-control" id="edit_kwaliteit_id['.$i.']" name="edit_kwaliteit_id" onchange="validate_edit_table_1(this, '.$i.')">';

     // add a loader while page loads
     <div id="menu-loader"></div>
</select>

Fetch the menu content after page loads

$(window).on("load", function() {

    $.ajax({
        url: '/endpointForFetchingMenuContent',
        type: 'POST',
        data: {},
        success: function( response ) {
            response = JSON.parse( response );

            if(response) {
                $("#menu-loader").hide();
                $("#edit_kwaliteit_id").append(response);
            }
        },
        error: function (xhr, status, error) {
            console.log( xhr.responseText );
        }
    });

});

Php endpoint:

// do your query
....
// loop through the result and build your menu
ob_start();
while($row_kwal = mysqli_fetch_array($res_kwal)) {

                echo '<option ';if($row_table_1['kwaliteit_id'] == $row_kwal['id']) { echo 'selected="selected"';} echo 'value="'.$row_kwal['id'].'">'.$row_kwal['kwaliteit'].'</option>';
            }
$output = ob_get_contents();
ob_end_clean();

echo json_endcode($output); exit();

Collectives™ on Stack Overflow

How to use mysql query multiple times

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related