mysql statement in html

[21MaAr07]

Hallo,

da mich Datenbanken immer wieder beruflich einholen, muss ich mich langsam damit intensiver auseinander setzen. Ich brauche einmal einen kleinen Denkanstoß. Ich frage per SNMP die OID von Drucker per NAGIOS ab. Dazu das entwickelte SQL-Script, welches alle nötigen Informationen zusammenfasst. Nun möchte ich gerne das in ein HTML Table ausgeben. Anbei der komplette Quellcode.

Zur Info, die einzelnen verschachtelten Statements lassen sich per HTML Table anzeigen, daher gehe ich davon aus, dass ich ein Problem mit "großen Statement" habe.

<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT distinct 
t1.host_object_id,
t1.display_name,
t1.address,
CASE WHEN t2.output IS NULL THEN '-' ELSE SUBSTRING(t2.output,11) END as 'Zusatzinformation',
CASE WHEN t5.output IS NULL THEN '-' ELSE SUBSTRING(t5.output,10) END as 'A3 SW',
CASE WHEN t6.output IS NULL THEN '-' ELSE SUBSTRING(t6.output,10) END as 'A3 Farbe',
CASE WHEN t7.output IS NULL THEN '-' ELSE SUBSTRING(t7.output,10) END as 'A4 SW',
CASE WHEN t8.output IS NULL THEN '-' ELSE SUBSTRING(t8.output,10) END as 'A4 Farbe',
CASE WHEN t9.output IS NULL THEN '-' ELSE SUBSTRING(t9.output,10) END as 'Seriennummer',
CASE WHEN t10.output IS NULL THEN '-' ELSE  SUBSTRING(t10.output,10 END as 'Standort'
FROM nagios_hosts t1 LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = upper('Zusatzinformation')
            ) t2 ON t1.host_object_id = t2.host_object_id
            LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = Upper('A3 SW')
            ) t5 ON t1.host_object_id = t5.host_object_id
            LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = upper('A3 Farbe')
            ) t6 ON t1.host_object_id = t6.host_object_id
            LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = Upper('A4 SW')
            ) t7 ON t1.host_object_id = t7.host_object_id
            LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = upper('A4 Farbe')
            ) t8 ON t1.host_object_id = t8.host_object_id
            LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = Upper('Seriennummer')
            ) t9 ON t1.host_object_id = t9.host_object_id
            LEFT JOIN
            (SELECT distinct t3.host_object_id, t3.service_object_id
            , t3.display_name
            , t4.output
            FROM nagios_services t3
            , nagios_servicestatus t4
            WHERE t3.service_object_id = t4.service_object_id
            and upper(t3.display_name) = Upper('Standort')
            ) t10 ON t1.host_object_id = t10.host_object_id
            WHERE upper( t1.display_name ) LIKE "P-%" ORDER BY t1.host_object_id";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>Name</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "
        <tr>
        <td>".$row["host_object_id"]."</td>
        <td>".$row["display_name"]."</td>
        <td>".$row["address"]."</td>
        <td>".$row["Zusatzinformation"]."</td>
        <td>".$row["A3 SW"]."</td>
        <td>".$row["A3 Farbe"]."</td>
        <td>".$row["A4 SW"]."</td>
        <td>".$row["A4 Farbe"]."</td>
        <td>".$row["Seriennummer"]."</td>
        <td>".$row["Standort"]."</td>
        </tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();
?>

 

[akretschmer]

Zur Info, die einzelnen verschachtelten Statements lassen sich per HTML Table anzeigen, daher gehe ich davon aus, dass ich ein Problem mit "großen Statement" habe.

Möglicherweise. Da Du Dein Problem nicht nennst, kann das aber keiner verifizieren. Darstellung von HTML und Programmierung in PHP ist übrigens hier vollständig Offtopic.