Using PHP/HTML to combine and display data from two MySQL tables-CodePudding

For context, I have to tables named ho_shifts and ho_incidents. One which includes shifts, which there is always one unique and the other which includes incidents. One shift can have multiple incidents related to it.

The plan here is to make a PHP loop so that it displays each shift and all incidents related to that shift where one shift with all it's incidents is repeated as many times as there are shifts. These database values will change as shifts and incidents are added.

These are the layouts and values of the tables mentioned. The tables are related by the timenow values.

ho_shifts:

 --- ----------- ----------- ----------- ---------------- ---------------- --------------------- 
|id |sl_role    |rp_role    |mo_role    |shift_start     |shift_end       |timenow              |
 --- ----------- ----------- ----------- ---------------- ---------------- --------------------- 
|382|Person1    |Person2    |Person3    |31.10.2022 12:00|31.10.2022 12:00|2022-10-18 18:28:54.0|
|384|Person4    |Person5    |Person6    |28.10.2022 12:00|20.10.2022 12:00|2022-10-18 19:34:53.0|
 --- ----------- ----------- ----------- ---------------- ---------------- ---------------------

ho_incidents:

 ---- --------- ------ ----- ------- ------ ------ --------------------- 
|id  |ticket_nr|sys_id|title|summary|status|inc_nr|timenow              |
 ---- --------- ------ ----- ------- ------ ------ --------------------- 
|1172|1        |NULL  |3    |4      |1     |2     |2022-10-18 18:28:54.0|
|1174|5        |NULL  |7    |8      |1     |6     |2022-10-18 18:28:54.0|
|1176|1        |NULL  |3    |4      |1     |2     |2022-10-18 19:34:53.0|
|1178|5        |NULL  |7    |8      |2     |6     |2022-10-18 19:34:53.0|
 ---- --------- ------ ----- ------- ------ ------ ---------------------

This is the current PHP script I have written. It has the spirit, but does not work quite as I want. The $combined variable is the SQL query which returns the data from the database, it's returned as such:

$combined = "SELECT shift_start, shift_end, sl_role, rp_role, mo_role, status, ho_shifts.timenow AS shifts_time, ticket_nr, inc_nr, title, summary, ho_incidents.timenow AS incidents_time FROM ho_shifts, ho_incidents WHERE ho_shifts.timenow = ho_incidents.timenow";

if ($result = $connection->query($combined)) {
    while ($row = $result->fetch_assoc()) {
        $shiftStart = $row["shift_start"];
        $shiftEnd = $row["shift_end"];
        $sl_role = $row["sl_role"];
        $rp_role = $row["rp_role"];
        $mo_role = $row["mo_role"];
        $shifts_timenow = $row["shifts_time"];

        $in_stat = $row["status"];
        $tic_nr = $row["ticket_nr"];
        $in_nr = $row["inc_nr"];
        $in_ti = $row["title"];
        $in_sum = $row["summary"];
        $incidents_timenow = $row["incidents_time"];

        echo "

        <br>

        <table class='table table-bordered'>

          <thead>
            <tr>
              <th scope='col'>Shift Start</th>
              <th scope='col'>Shift End</th>
              <th scope='col'>Shift lead</th>
              <th scope='col'>Responder</th>
              <th scope='col'>Monitoring</th>
              <th scope='col'>Timenow</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td>$shiftStart</td>
              <td>$shiftEnd</td>
              <td>$sl_role</td>
              <td>$rp_role</td>
              <td>$mo_role</td>
              <td>$shifts_timenow</td>
            </tr>
            </tbody>

          <thead>
            <tr>
              <th scope='col'>Status</th>
              <th scope='col'>Ticket nr</th>
              <th scope='col'>Incident nr</th>
              <th scope='col'>Title</th>
              <th scope='col'>Summary</th>
              <th scope='col'>Timenow</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td>$in_stat</td>
              <td>$tic_nr</td>
              <td>$in_nr</td>
              <td>$in_ti</td>
              <td>$in_sum</td>
              <td>$incidents_timenow</td>
            </tr>
            </tbody>
        </table>

        ";
    }
    $result->free();
}

Query result which $combined returns for ease of understanding:

 ---------------- ---------------- ------- ------- ------- ------ --------------------- --------- ------ ----- ------- --------------------- 
|shift_start     |shift_end       |sl_role|rp_role|mo_role|status|shifts_time          |ticket_nr|inc_nr|title|summary|incidents_time       |
 ---------------- ---------------- ------- ------- ------- ------ --------------------- --------- ------ ----- ------- --------------------- 
|28.10.2022 12:00|20.10.2022 12:00|Person4|Person5|Person6|1     |2022-10-18 19:34:53.0|1        |2     |3    |4      |2022-10-18 19:34:53.0|
|28.10.2022 12:00|20.10.2022 12:00|Person4|Person5|Person6|2     |2022-10-18 19:34:53.0|5        |6     |7    |8      |2022-10-18 19:34:53.0|
|31.10.2022 12:00|31.10.2022 12:00|Person1|Person2|Person3|1     |2022-10-18 18:28:54.0|1        |2     |3    |4      |2022-10-18 18:28:54.0|
|31.10.2022 12:00|31.10.2022 12:00|Person1|Person2|Person3|1     |2022-10-18 18:28:54.0|5        |6     |7    |8      |2022-10-18 18:28:54.0|
 ---------------- ---------------- ------- ------- ------- ------ --------------------- --------- ------ ----- ------- ---------------------

So what I meant by "has the spirit, but does not work quite how I want" is below.

Current result:

Expected result:

I assume that I have to nest another loop into the current loop, but I have attempted so without any success.

CodePudding user response：

I assume that I have to nest another loop into the current loop, but I have attempted so without any success.

The question comes down to, how do you know to add a new shift line when you move to the next shift in the result set?

A simple solution is to keep track of something unique to the shift records in your for loop so you know when the record changes. Since timenow is essentially an ID for the shift/incident groups, you could use that to pick up when the next shift is.

$cur_shift_timenow = ""
while ($row = $result->fetch_assoc()) {
  if ($cur_shift_timenow  != $row['timenow']) {
    // print out your shift data
    $cur_shift_timenow = $row['timenow'];
  }
  // print out incident data
}

SELECT shift_start, shift_end, sl_role, rp_role, mo_role, status, ho_shifts.timenow AS shifts_time, ticket_nr, inc_nr, title, summary, ho_incidents.timenow AS incidents_time FROM ho_shifts, ho_incidents WHERE ho_shifts.timenow = ho_incidents.timenow

This is an outdated method of crafting an SQL join. A better method is with an explicit JOIN, something like:

SELECT shift_start, shift_end, sl_role, rp_role, mo_role, status, ho_shifts.timenow AS shifts_time, ticket_nr, inc_nr, title, summary, ho_incidents.timenow AS incidents_time 
FROM ho_shifts
LEFT JOIN ho_incidents ON ho_shifts.timenow = ho_incidents.timenow

As shifts and incidents always are inserted at the same time, the timenow value should always connect incidents and shifts

It shouldn't stop you from achieving your desired output, but it is really bizarre to me. You're essentially turning timenow into a shift id with a more complex data type (date vs integer). Using a foreign key from ho_incidents that references ho_shifts.id would make a lot more sense. You'd insert the shift record, get its last insert ID from the insert query result, then use that as part of the records to ho_shifts. That would be a much more "normal" way to link the data. But, like I said, this is weird to me, but I guess it works.