将多个查询数据转换为单个HTML-Table - 只返回一行

Question

I want to display results from 4 querys into one single html-table (SQL Server 2012, PHP, HTML). I already can display the results from one database-table, but as soon as I start to query the other 3 tables it only returns one row from the first table.

The following code only has one query and works fine - it displays several information about patients (Name, Station, Room, Catheter, Personal-ID, Date of Catheter-Insertion..), at the moment there are three different patients at the table, therefore the html-table shows 3 rows:

<?php
require_once('header.php');

$today = date("d.m.y");
$yesterday = date("d.m.y", time() - 60 * 60 * 24);
$dbyesterday = date("d.m.y", time() - 2*(60 * 60 * 24));
?>

<div class="container" style="padding-top: 80px">
    <div id="table_admin" class="span7">
          <h3 id="name" style="color: orange">Visitenliste</h3>
         </br>

         <table class="table table-responsive table-hover">

                <thead>
                <tr>
                <th id="tc">Station</th>
                <th id="tc">Patientennummer</th>
                <th id="tc">Zimmer</th>
                <th id="tc">Name</th>
                <th id="tc">Katheterart</th>
                <th id="tc">Anlagedatum</th>
                <th id="tc"><?php echo $dbyesterday; ?></th>
                <th id="tc"><?php echo $yesterday; ?></th>
                <th id="tc"><?php echo $today; ?></th>
               </tr>
                </thead>


   <?php
require_once('server.php');


// DB-Query für die Visitenliste
$sql = "SELECT dbo.patients.Patientennummer, Vorname, Nachname, Station, Zimmer, Katheterart, Anlagedatum, VisTod FROM dbo.patients INNER JOIN dbo.cathinsert ON dbo.patients.Patientennummer = dbo.cathinsert.Patientennummer ORDER BY Station ASC, Zimmer ASC";

$result = sqlsrv_query($connection, $sql);

if($result === false) {
    die( print_r( sqlsrv_errors(), true) );
}

while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {

    echo        ("<tr class=\"clickable-row\" data-href=\"visitact.php?Patientennummer=".$row["Patientennummer"]."\">");
    echo        ("<td>".$row["Station"]."</td>");
    echo        ("<td>".$row["Patientennummer"]."</td>");
    echo        ("<td>".$row["Zimmer"]."</td>");
    echo        ("<td>".$row["Vorname"]." ".$row["Nachname"]."</td>");
    echo        ("<td>".$row["Katheterart"]."</td>");
    echo        ("<td>".$row["Anlagedatum"]."</td>");
    echo        ("<td>".$row["VisTod"]."</td></tr>");  

              }
            ?>


        </table>
    </div>
</div>
</body>
</html>  

As a result, I have a table with 3 rows and all information I needed. But as soon as I start adding more querys now, the additional information is displayed right, but only for the first row! Its the only row displayed at all. I really can't figure out, why this happens. This is the code not working (all information is displayed right, even the information from the additional querys, but only one row --> only the first patient, although 3 are saved at the database and although the first code displays all 3 patients in the right way):

<?php
require_once('header.php');
$today = date("d.m.y");
$yesterday = date("d.m.y", time() - 60 * 60 * 24);
$dbyesterday = date("d.m.y", time() - 2*(60 * 60 * 24));
?>

<div class="container" style="padding-top: 80px">
<div id="table_admin" class="span7">
       <h3 id="name" style="color: orange">Visitenliste</h3>
        </br>
         <table class="table table-responsive table-hover">

               <thead>
                <tr>
                <th id="tc">Station</th>
                <th id="tc">Patientennummer</th>
                <th id="tc">Zimmer</th>
                <th id="tc">Name</th>
                <th id="tc">Katheterart</th>
                <th id="tc">Anlagedatum</th>
                <th id="tc"><?php echo $dbyesterday; ?></th>
                <th id="tc"><?php echo $yesterday; ?></th>
                <th id="tc"><?php echo $today; ?></th>
                </tr>
                </thead>

<?php
require_once('server.php');



// DB-Query für die Visitenliste

$sql = "SELECT dbo.patients.Patientennummer, Vorname, Nachname, Station, Zimmer, Katheterart, Anlagedatum FROM dbo.patients INNER JOIN dbo.cathinsert ON dbo.patients.Patientennummer = dbo.cathinsert.Patientennummer ORDER BY Station ASC, Zimmer ASC";
$visdbyes = "SELECT (SELECT VisiteKind FROM dbo.visits WHERE VisiteDate = '$dbyesterday') AS VisiteKind";
$visyes = "SELECT (SELECT VisiteKind FROM dbo.visits WHERE VisiteDate = '$yesterday') AS VisiteKind";
$vistod = "SELECT (SELECT VisiteKind FROM dbo.visits WHERE VisiteDate = '$today') AS VisiteKind";

$result = sqlsrv_query($connection, $sql);
$result_vdy = sqlsrv_query($connection, $visdbyes);
$result_vy = sqlsrv_query($connection, $visyes);
$result_vt = sqlsrv_query($connection, $vistod);

$data = array();

while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){$data['row'] = $row;}
while($row = sqlsrv_fetch_array($result_vdy, SQLSRV_FETCH_ASSOC)) {$data['dby'] = $row;}
while($row = sqlsrv_fetch_array($result_vy, SQLSRV_FETCH_ASSOC)) {$data['yes'] = $row;}
while($row = sqlsrv_fetch_array($result_vt, SQLSRV_FETCH_ASSOC)) {$data['tod'] = $row;}

echo    ("<tr class=\"clickable-row\" data-href=\"visitact.php?Patientennummer=".$row["Patientennummer"]."\">");  
echo    ("<td>".$data['row']["Station"]."</td>");
echo    ("<td>".$data['row']["Patientennummer"]."</td>");
echo    ("<td>".$data['row']["Zimmer"]."</td>");
echo    ("<td>".$data['row']["Vorname"]." ".$data['row']["Nachname"]."</td>");
echo    ("<td>".$data['row']["Katheterart"]."</td>");
echo    ("<td>".$data['row']["Anlagedatum"]."</td>");
echo    ("<td>".$data['dby']["VisiteKind"]."</td>"); //vorgestern
echo    ("<td>".$data['yes']["VisiteKind"]."</td>"); // gestern
echo    ("<td>".$data['tod']["VisiteKind"]."</td>"); //heute
                    ?>

       </table>
    </div>
</div>
</body>
</html>

I hop you can help! Thanks in advance,

dirk

Answer 1

I think You should use one query for every days, then check dates in while loop. You can also reduce 'VisiteKind' field in $data array:

$visd = "SELECT VisiteKind FROM dbo.visits";
$result_v = sqlsrv_query($connection, $visd);
$data = ['dby'=> [], 'yes'=> [], 'tod'=> []];
while($row = sqlsrv_fetch_array($result_v, SQLSRV_FETCH_ASSOC)) {
    switch($row['VisiteKind']){
        case $dbyesterday:
            array_push($data['dby'],$row['VisiteKind']);
            break;
        case $yesterday:
            array_push($data['yes'],$row['VisiteKind']);
            break;
        case $today:
            array_push($data['tod'],$row['VisiteKind']);
            break;
    }
}