用 MySQL 数据填充 HTML 表(多个查询） - Populate HTML Table with MySQL data (Multiple Queries)

经过广泛的研究，我得出的结论是我的案例是独一无二的，我需要问。我的PHP知识非常有限，我试图仅根据Google结果进行这项工作。

目标：创建一个网页，显示包含 5 列（端口、L1 状态、L2 状态、成帧错误、活动呼叫）的 HTML 表格这些列中每一列的日期都存储在单个数据库表中，这就是诀窍，其中大部分数据来自同一字段......这意味着我需要创建 5 个不同的查询。我尝试创建一个查询（我相信如果可以的话会起作用），但我无法做到。

到目前为止的结果：仅包含第 5 个查询结果的表和表的其余部分仅重复填充第 1 个查询。

这是我的代码：

<!-- Simple HTML to Create the table layout -->
<table border=1 style="background-color:#F0F8FF;" >
<caption><EM>HEADER</EM></caption>
<tr>
<th>Port</th>
<th>L1 Status</th>
<th>L2 Status</th>
<th>Framing Errors</th>
<th>Active calls</th>
</tr>
<!-- END Simple HTML to Create the table layout -->
<?php
$server = "localhost";
$dbname = "database";
$user = "user";
$password = "password";
$con = mysql_connect($server,$user,$password) or die (mysql_error());
mysql_select_db($dbname) or die (mysql_error());
$query1="select right(name, 10) as 'Port' from items where hostid = (select hostid from hosts where name = 'MIAGATE01') and key_ like '%activeChannels[%' and key_ not like '%SNMPINDEX%' order by name";
$query2="select lastvalue as 'Layer1' from items where hostid = (select hostid from hosts where name = 'MIAGATE01') and key_ like '%statusLayer1[%' and key_ not like '%SNMPINDEX%' order by name";
$query3="select lastvalue as 'Layer2' from items where hostid = (select hostid from hosts where name = 'MIAGATE01') and key_ like '%statusLayer1[%' and key_ not like '%SNMPINDEX%' order by name";
$query4="select lastvalue as 'Framing_Errors'from items where hostid = (select hostid from hosts where name = 'MIAGATE01') and key_ like '%frameErrors[%' and key_ not like '%SNMPINDEX%' order by name";
$query5="select lastvalue as 'Active_Calls' from items where hostid = (select hostid from hosts where name = 'MIAGATE01') and key_ like '%activeChannels[%' and key_ not like '%SNMPINDEX%' order by name";
$result1=mysql_query($query1) or die(mysql_error());
$result2=mysql_query($query2) or die(mysql_error());
$result3=mysql_query($query3) or die(mysql_error());
$result4=mysql_query($query4) or die(mysql_error());
$result5=mysql_query($query5) or die(mysql_error());
while($row1 = mysql_fetch_array($result1)){
while($row2 = mysql_fetch_array($result2)){
while($row3 = mysql_fetch_array($result3)){
while($row4 = mysql_fetch_array($result4)){
while($row5 = mysql_fetch_array($result5)){
echo "<tr>";
echo "<td>" . $row1['Port'] . "</td>";
echo "<td>" . $row2['Layer1'] . "</td>";
echo "<td>" . $row3['Layer2'] . "</td>";
echo "<td>" . $row4['Framing_Errors'] . "</td>";
echo "<td>" . $row5['Active_Calls'] . "</td>";
echo "</tr>";
}
}
}
}
}
mysql_close($con);
?>

首先尝试此查询，看看它是否一次获得了所有必需的结果。

SELECT h.hostid, 
  RIGHT(port.name,10) AS 'Port', 
  l1.lastvalue AS 'Layer1', 
  l2.lastvalue AS 'Layer2', 
  fe.lastvalue AS 'Framing_Errors', 
  ac.lastvalue AS 'Active_Calls' 
FROM hosts h 
INNER JOIN items port 
  ON port.hostid = h.hostid 
    AND port.key_ LIKE '%activeChannels[%' 
    AND port.key_ not LIKE '%SNMPINDEX%' 
INNER JOIN items l1 
  ON l1.hostid = h.hostid 
    AND l1.key_ LIKE '%statusLayer1[%' 
    AND l1.key_ not LIKE '%SNMPINDEX%'
INNER JOIN items l2 
  ON l2.hostid = h.hostid 
    AND l2.key_ LIKE '%statusLayer2[%' 
    AND l2.key_ not LIKE '%SNMPINDEX%'
INNER JOIN items fe 
  ON fe.hostid = h.hostid 
    AND fe.key_ LIKE '%frameErrors[%' 
    AND fe.key_ not LIKE '%SNMPINDEX%'
INNER JOIN items ac 
  ON ac.hostid = h.hostid 
    AND ac.key_ LIKE '%activeChannels[%' 
    AND ac.key_ not LIKE '%SNMPINDEX%'
WHERE h.name = 'MIAGATE01'
ORDER BY h.name;

如果这有效，则只需要一个while循环即可填充表。

while ( $row = mysql_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['Port'] . "</td>";
  echo "<td>" . $row['Layer1'] . "</td>";
  echo "<td>" . $row['Layer2'] . "</td>";
  echo "<td>" . $row['Framing_Errors'] . "</td>";
  echo "<td>" . $row['Active_Calls'] . "</td>";
  echo "</tr>";
}

首先：

预期 HTML 表的每个行是否包含所有五个数据项？换一种问法，每个端口有一行吗？从你的问题中看不出来。从您的逻辑来看，不同参数的出现次数肯定不同。

无论如何...您需要将一堆包含 hostid，参数的虚拟表连接在一起，以组合您的显示器。我想是这样。（您的数据非常复杂）。

select hostid.hostid, port.Port, layer1.Layer1, layer2.Layer2
  from
            (select hostid, name
             from hosts
            where name = 'MIAGATE01'
            ) hostid
  left join
            (select hostid, right(name,10) as 'Port'
               from items
              where key_ like '%activeChannels[%' and key_ not like '%SNMPINDEX%'  
            ) port 
        on hostid.hostid = port.hostid
  left join
           (select hostid, lastvalue as 'Layer1'
              from items
             where key_ like '%statusLayer1[%' and key_ not like '%SNMPINDEX%'  
           ) layer1 
        on hostid.hostid = layer1.hostid
  left join
           (select hostid, lastvalue as 'Layer2'
              from items
             where key_ like '%statusLayer2[%' and key_ not like '%SNMPINDEX%'  
           ) layer2 
        on hostid.hostid = layer2.hostid
order by hostid.name, port.Port

看看这是怎么回事？您有很多小选择，这些选择会生成 hostid、值项的列表，其中值项与您的key_选择条件匹配。然后，根据它们的 hostid 值将它们全部连接在一起。第一个小选择只是为您想要的特定主机提供正确的 hostid。

我没有执行所有参数，但完成此查询应该不难。至于调试它....如果不看到您的items表的所有荣耀，就很难知道。这可能是一个大毛球。但你已经知道了。