使用一个查询填充多选择菜单HTML PHP - Using one query to populate Multiple Select Menu HTML PHP

Using one query to populate Multiple Select Menu HTML PHP

这更像是一个代码优化问题，而不是需要解决问题的通常直接问题。我有4个选择菜单，从SQL查询中获得它们的值。问题是，我重复相同的查询4次，以填充4选择菜单列表。下面是SQL代码。(我使用PHP和SQL Server 2008)

 <select name="cities" id="" class="brkcity" style="width:120px;">
       <option></option>
        <?php
    foreach($country as $makassi1){ $selectcities = "SELECT City, Country, Rate FROM tblPerdiem_Rates WHERE Country = '$makassi1'";$checkcity = sqlsrv_query($conn,$selectcities, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ))or die(print_r( sqlsrv_errors(), true));
 while($row=sqlsrv_fetch_array($checkcity))
 {
     $countries = ($row['Country']);
     $names =($row['City']) ;
     $rate =($row['Rate']) ;
     $ratess=$names."-".$countries
     ?> 
 <option id="cityoptrates"  value="<?php echo $rate; ?>"><?php echo $ratess; ?></option>
 <?php       
 }
 }  
 sqlsrv_free_stmt($checkcity);      
         ?>
      </select></td>
      <td width="93" id="tdbreakfast"><input name="brkfastchk" class="breko" type="checkbox" value=""    id="" />
        <label for="brkfasttxt"></label>
        <input style="" value=""   name="brkfasttxt" class="breko" type="text" id="" size="3" readonly="readonly" />
      <label for="brkfastchk"></label></td>
      <td width="133" id="tdlnchcities"><select name="cities" id="" class="lnhcity" style="width:120px;">
       <option></option>
        <?php foreach($country as $makassi1) { $selectcities = "SELECT City, Country, Rate FROM tblPerdiem_Rates WHERE Country = '$makassi1'"; $checkcity = sqlsrv_query($conn,$selectcities, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ))or die(print_r( sqlsrv_errors(), true));

 while($row=sqlsrv_fetch_array($checkcity))
 {
     $countries = ($row['Country']);
     $names =($row['City']) ;
     $rate =($row['Rate']) ;
     $ratess=$names."-".$countries
     ?> 
 <option id="cityoptrates"  value="<?php echo $rate; ?>"><?php echo $ratess; ?></option>
 <?php       
 }   
 }sqlsrv_free_stmt($checkcity); 
         ?>
      </select>

这是我用来填充列表菜单的代码。但我不喜欢它，因为它是低效的重复相同的查询4次在同一页面。我尝试将查询和"for each"部分放在页面的顶部，然后在选择菜单html部分使用"while"部分，但它只适用于第一个菜单。

我能做得更好吗?还是这已经是最好的了?感谢所有帮助

将单个查询的结果缓存到一个数组中，然后使用该数组填充下拉列表:

$data = array()
while($row = sqlserv_fetch_array($checkcity)) {
   $data[] = $row;
}

然后执行foreach循环而不是while/fetch:

foreach($data as $row) {
     $countries = $row['Country'];
     $city = $row['City'];
     $rate = $row['rate'];
     etc...
}

更新如果需要的话，

也可以缓存"per $makassi1":

while(...) {
    $data[$makassi1][] = $row;
}

但是，考虑重写您的查询以使用连接或其他结构，这样您就不会多次运行那么多几乎相同的查询。