Populate HTML Table with MySQL data (Multiple Queries)

Question

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.

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.

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.

Here's my code:

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

the better question to ask would be around making a single query, then go from there — user557846
– user557846, Commented May 31, 2012 at 21:59
You have to gather and store the results of each mysql_query before doing another. — Aaron W.
– Aaron W., Commented May 31, 2012 at 22:00
Fix your database design to put these values in their own column. — nickb
– nickb, Commented May 31, 2012 at 22:09

Paul · Accepted Answer · 2012-05-31 22:35:34Z

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

Looks good. But INNER JOIN will conceal the whole result row if some of the parameters are missing.
That's right. But the question sounds as if all data is available for each hostid. If not, you have to change all INNER JOINs to LEFT JOINs, of course.
thanks for the response. I completely understand that having to work with multiple while loops is not efficient and the best option is to compile one query that will give me the results I want. The query above does give me multiple rows of data, but its outputting data for only one "port" i'm working to tweek the query you provided. I'll see if I get this working i'll post it here.
I created a new post asking for help with the SQL query (shows the table layout and the desired results, check if this helps): stackoverflow.com/questions/10853565/…

O. Jones · Accepted Answer · 2012-05-31 22:26:32Z

First of all:

Does every ROW of your intended HTML table have all five data items? Asked another way, is there one single row for each Port? It's not obvious from your question. It's certainly possible from your logic to have different numbers of occurrences of the different parameters.

At any rate ... you need to join together a bunch of virtual tables containing hostid,parameter to put together your display. Like so, I think. (Your data is pretty complex).

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

See how that goes? You have lots of little selects that generate a list of hostid, value items where the value items match your key_ selection criteria. You then join those all together on their hostid values. The first little select simply comes up with the right hostid for the particular host you want.

I didn't do all your parameters but it shouldn't be hard for you to complete this query. As for debugging it.... it's hard to know without seeing your items table in all its glory. It could be a big hairball. But you already knew that.

Aren't network equipment logs just huge barrels full of fun?

Collectives™ on Stack Overflow

Populate HTML Table with MySQL data (Multiple Queries)

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related