400 个 SELECT 查询来填充 php 网格 [英] 400 SELECT queries to populate php grid

问题描述

我的数据库中有 2 个表，表 A 和表 B.我需要填充一个 20x20 方格的可视网格，总共 400 个方格.我只需要读取数据，而不是插入或更新.

I have 2 tables in my database, table A and table B. I need to populate a visual grid of 20x20 squares, 400 squares in total. I just need to read the data, not insert or update.

我的脚本所做的是对每个网格 ID 执行 FOR 循环，查询表 A 以查看数据是否存在，如果存在，则执行另一个 mysql 查询以从表 B 中提取数据.

What my script does is does FOR loop through each grid id, querys table A to see if data exists, and if it does, it does another mysql query to pulls data from table B.

正如您想象的那样，这使用了大量资源(400 个请求).我不是最擅长 PHP，但想知道是否有人知道更好的方法来做到这一点?

As you can imagine this is using a lot of resources (400 requests). I'm not the best at PHP, but wondered if anyone knew of a better way to do this?

注意:我不能合并这两个表，它必须是分开的.

Note: I can't combine the 2 tables, it has to be separate.

<table width="500" height="500" background="images/map.png"><?
$plot = 1;
for ($plot_y = 1; $plot_y <= 20; $plot_y++) {
  echo "<tr>";
  for ($plot_x = 1; $plot_x <= 20; $plot_x++) {
      $sql_result5 = mysql_query("SELECT * FROM turfs WHERE plot ='$plot'", $db);
      if (mysql_num_rows($sql_result5) != 0) {
          $rs5 = mysql_fetch_array($sql_result5);

          $nogo = 0; // SET COLOR TO rs5[color]
      } else {
          $nogo = 1;
      }
?><td width="23" height="23"><?php
      if ($nogo == 0) {
          $sql_result = mysql_query("SELECT * FROM properties WHERE plot = '$plot'", $db);
          if (mysql_num_rows($sql_result) > 0) {
              $rs = mysql_fetch_array($sql_result);

              echo "<img src=$rs[image]>";

          }
      }
      echo "</td>";
      $plot = $plot + 1;
  }
  echo "</tr>";
}
?></table>

推荐答案

在不了解您的表结构的情况下，我们所能做的最好的事情就是猜测它，但以下可能会给您一些新的想法.

Without knowing your table structure the best we can do is guess at it but following might give you some fresh ideas to work with.

它的要点是

• 使用一个 select 语句选择 GridID 和相关数据
• 遍历此语句的结果以填充您的网格

SELECT t.plot AS plot
       , t.plot MOD 20 AS plot_x
       , FLOOR(t.plot / 20) AS plot_y
       , CASE WHEN p.plot IS NULL THEN 'free' ELSE 'used' END
FROM   turfs AS t
       LEFT OUTER JOIN properties AS p ON p.plot = t.plot 

这篇关于400 个 SELECT 查询来填充 php 网格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！