使用MySQL数据填充HTML表（多个查询） [英] Populate HTML Table with MySQL data (Multiple Queries)

问题描述

经过大量研究，我得出的结论是我的案子是独一无二的，我需要问一下。我对PHP的了解非常有限，我正试图完全根据Google的结果来完成这项工作。

After extensive research I have come to the conclusion my case is unique and I need to ask. I have a very limited PHP knowledge and I'm trying to make this work solely out of Google results.

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

Goal: Create a webpage that displays a an HTML table with 5 columns (Port, L1 Status, L2 Status, Framing Errors, Active Calls) The date for each of these columns is stored in a single database table and here's the trick, the majority of this data is from the same field...this means I need to create 5 different queries. I've tried to create a single query (which I believe would work if I could) but I am not able to.

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

Results so far: A table with only the 5th query's results and the rest of the table is populated with only the 1st query repeatedly.

这是我的代码：

<!-- 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);
?>

推荐答案

请首先尝试执行此查询，看看是否能解决所有问题

Try this query first and see if it got all your required results at once.

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;

如果这可行，则只需要一个，而循环以填充表。

If this works, you just need a single while loop to populate your table.

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>";
}

这篇关于使用MySQL数据填充HTML表（多个查询）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！