php - 将 MySQL 查询循环转换为 PHP 数组(表)

Question

我有一个系统，它会检查数据库，创建指定大小的表，然后根据数据库中是否有该特定位置的数据来填充单元格为满或空。

目前，它的工作原理是对每个单元格执行 SQL 查询，然后进行填充，但我的数据库中有 30,000 多条记录，即使使用 LIMIT 1，加载表格仍需要大约 5 秒的时间。

我想知道将整个内容转储到 PHP 数组中然后以这种方式查询是否会更好，但无法找出对其进行排序的最佳方法，欢迎提供任何提示。

当前(工作)代码:

echo <<<EOD
<table class="racktable"><tr>
<td colspan ="$colspan">Rack Details </td>
</tr>
<tr>
<td colspan ="$colspan"><center><a href="edit-rack.php?rack=$rackID">Edit Rack</a> / <a href="edit.php?query=emptyr&rack=$rackno&location=$location&user=$user">Empty Rack</a> / <a href="edit.php?query=delrack&rack=$rackID&user=$user&loc=$location">Delete Rack</a></center> </td>
</tr>
EOD;
//Loop through rows, creating a <tr> for each in the table
  for ($row1 = 1; $row1 <= $rows; $row1++) {
    echo <<<EOD
    <tr><td><a name="$row1"></a>$row1</td>
EOD;
    //Loop through columns creating <td> within <tr>
    for ($col1 = 1; $col1 <= $columns; $col1++) {
      $sql2 = "SELECT ID, sample, rack, srow, col, location FROM samples WHERE srow = $row1 and col = $col1 and location = '$location' and rack = '$rack' LIMIT 1";
      if (!$result2 = $db->query($sql2)) {
        die('There was an error running the query [' . $db->error . ']');
      }
      $row3 = $result2->num_rows;
      //If location is empty, colout green
      if ($row3 == 0) {
        echo "<td style=\"background-color: #A3CD81\">" . $col1 . "</td>";
      }
      else {
          //Location is not empty, colour red and link to sample
        while ($row2 = $result2->fetch_assoc()) {
          $columns1 = $row2['col'];
          $ID = $row2['ID'];
          $tooltip = $row2['sample'];
          $queryStr = $_SERVER['QUERY_STRING'];
          $spath = $_SERVER['PHP_SELF'] . "?" . $queryStr . "&sample=" . $ID;
          echo <<<EOD
        <td style="background-color: #FF0000" title="$tooltip"><a href="$spath">$col1 <img src="icon.png" style="border: 0" alt=""></a></td>
EOD;

        }
      }
    }
    echo "</tr>";
  }
  echo "</table>";

Answer 1

您可以使用以下代码将数据库中的所有数据直接转换为 php 数组:

while(($PHPToPHP[] = mysql_fetch_assoc($SqlResult )) || array_pop($dataToPHP));

可能适用于您的代码:

while(($dataToPHP[] = $result2->fetch_assoc()) || array_pop($dataToPHP))

看看它有什么用途 print_r($dataToPHP) .

管理错误 die('There was an error running the query [' . $db->error . ']');和if ($row3 == 0) { echo "<td style=\"background-color: #A3CD81\">" . $col1 . "</td>";}要着色，您需要进行一些测试才能正确管理。