时间戳

首页 > 解决方案 > 如何多次使用mysql查询

php - 如何多次使用mysql查询

问题描述

我在 for 循环中有这个选择菜单，所以查询运行了多次。还有很多这样的查询，所以页面加载速度很慢。

<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>

由于每次我想将查询移出循环并将查询本身放在循环之前，查询都是相同的。就像是

<?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
?>

但现在while($row_kwal = mysqli_fetch_array($res_kwal))给出了一个空的结果。有什么建议可以在每个循环中保留一个查询而不是一个查询？

标签： phpmysql

解决方案

您应该在第一次运行查询时将所有行读入一个数组，然后在循环中迭代该数组，for例如

$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;
}

然后在你的循环中，替换

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

和

foreach ($rows as $row) {

请注意，如果您安装了 MySQL 本机驱动程序 ( mysqlnd)，则可以使用

$rows = mysqli_fetch_all(MYSQLI_BOTH);

代替while循环加载$rows数组。

笔记

如果您有一个特别大的结果集无法加载到内存中，那么您可以mysqli_data_seek在开始从结果集读取之前重置数据指针。所以你在哪里

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

将其更改为

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

推荐阅读