php - 使用附加查询循环 MySQL 结果

Question

我希望有人可以帮助我为我正在编写的 PHP 页面构思一个解决方案。

实际上，我在 MySQL 数据库中有一个名为“服务器”的主表。其中有四个字段； 部门、服务器A、服务器B、服务器C。对于上下文，部门条目是部门的名称，serverA、serverB 和 serverC 对应于每个部门的三个服务器的主机名。

我想要做的是获取部门、serverA、serverB 和 serverC 的名称，并使用该数据来填充多个 SQL 查询。为了清洁和关系，我将数据分成多个表。这是我得到的；

<?php
$conn = mysqli_connect("server", "root", "root") or die('Error connecting to MySQLserver.');
mysqli_select_db($conn, "Database") or die("Failed to connect to database");
$sql = "SELECT * FROM Servers";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    $deptartment = $row['department']
    $serverA = $row['serverA'];
    $serverB = $row['serverB'];
    $serverC = $row['serverC'];
}
} else {
echo "Unable to connect to server.  Please check your settings.";
}
?>

然后我想做的是做一种foreach，其中服务器A的foreach记录，发生以下情况；

<?php
$conn = mysqli_connect("server", "root", "root") or die('Error connecting to MySQLserver.');
mysqli_select_db($conn, "Database") or die("Failed to connect to database");
$sql = "SELECT * FROM Data WHERE `serverName` = '$serverA' ORDER BY `timestamp` DESC LIMIT 1";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    $serverName = $row['serverName'];
    $serverType = $row['serverType'];
    $serverAge = $row['serverAge'];
}
} else {
echo "Unable to connection to server.  Please check your settings.";
}
?>

然后我可以使用相同的理论循环遍历 serverB 的结果，并让 serverB 的每条记录完成另一个查询以获取其他数据。

我似乎在为如何循环遍历每个结果并用它填充其他查询而苦苦寻找正确的语法。

任何可能的指导？

谢谢!

Sample Data:
department: Math
serverA: math-server-windows.school.edu
serverB: math-server-mac.school.edu
serverC: math-server-linux.school.edu

department: Psychology
serverA: psychology-server-windows.school.edu
serverB: psychology-server-mac.school.edu
serverC: psychology-server-linux.school.edu

department:  Arts
serverA: artsserver.school.edu
serverB: artsold.school.edu
serverC: artsbackup.school.edu

Answer 1

您可以使用 JOIN 来简化这一切。

表 Data 有一个字段 servername 对应于 serverA、serverB 或 serverC 在服务器表中。所以你可以像这样运行查询:

SELECT Data.*,
  A.serverA AS serverA, 
  B.serverB AS serverB, 
  C.serverC AS serverC 
FROM Data
JOIN servers as A on Data.servername = A.serverA
JOIN servers as B on Data.servername = B.serverB
JOIN servers as C on Data.servername = C.serverC
ORDER BY <whatever>

现在每一行都有每个部门的服务器信息，所以循环遍历结果:

while( $row = $result->fetch_assoc() ){
  echo "\nDept: ".$row['department'];
  echo "\nA: ".$row['serverA'];
  echo "\nB: ".$row['serverB'];
  echo "\nC: ".$row['serverB'];
}