Single HTML Table from multiple MySQL tables

Question

Hi!

I've been struggling for a while with this one; I'll try to explain it here as simply as possible.

Consider this MySQL table:

+----------+-----------+---------+--------+
|status_id |session_id |pilot_id |present |
+----------+-----------+---------+--------+
|1         |61         |901      |1       |
|2         |63         |901      |1       |
|3         |62         |901      |0       |
|4         |62         |902      |1       |
|5         |63         |903      |1       |
+----------+-----------+---------+--------+

Both session_id and pilot_id are foreign keys making reference to a primary key in another table. The same pilot_id can be associated with different session_id, but every pilot_id-session_id combination is unique.

I need to make an HTML table (in PHP) that would display the data like this:

+----------+---------+---------+---------+
|          |61       |62       |63       |
+----------+---------+---------+---------+
|901       |X        |         |X        |
|902       |         |X        |         |
|903       |         |         |X        |
+----------+---------+---------+---------+

Hence, rows are pilot_id and columns are session_id. When a pilot_id-session_id combination has a present value of 1, the corresponding cell should be checked. (ie. when the row-combination is zero or the combination does not exist in the MySQL table, nothing should appear in the HTML table)

Phew.

Any ideas?

Thanks!

---

I've tried the answer proposed by erisco, but I'm quite confused. (the comment field is much too small for my explanation, hence this update to my question).

This is the actual data I am working with:

+----------+-----------+---------+--------+
|status_id |session_id |pilot_id |present |
+----------+-----------+---------+--------+
|7         |65         |33       |1       |
|8         |66         |33       |1       |
|9         |65         |17       |0       |
|10        |66         |16       |1       |
+----------+-----------+---------+--------+

I use $rows = mysqli_fetch_array($result);. I have confirmed the query is returning the right data.

However, when I use the answer proposed by ericso, I am getting seemingly arbitrary data. Here's the generated HTML table:

+----------+---------+---------+---------+---------+
|          |1        |3        |6        |7        |
+----------+---------+---------+---------+---------+
|1         |X        |         |         |         |
|3         |         |         |         |         |
|6         |         |         |         |         |
|7         |         |         |         |         |
+----------+---------+---------+---------+---------+

Furthermore the 'X' position stays the same irrelevantly of present values.

Any ideas why this is happening?

Thanks!

Answer 1

You can use algorithm like this:

$sql = "SELECT DISTINCT session_id AS sid FROM pilot_session ORDER BY 1 ASC";
$rs = mysql_query($sql, $conn);
$sessions = array();
while(false !== ($r = mysql_fetch_array($rs))){
    $sessions[] = $r['sid'];
}

$sql = "SELECT DISTINCT pilot_id AS pid FROM pilot_session ORDER BY 1 ASC";
$rs = mysql_query($sql, $conn);
$pilots = array();
while(false !== ($r = mysql_fetch_array($rs))){
    $pilots[] = $r['pid'];
}

$pilot_presence = array();
$sql = "SELECT session_id, pilot_id, present FROM pilot_session";
$rs = mysql_query($sql, $conn);
while(false !== ($r = mysql_fetch_array($rs))){
    $s_presence[$r['pilot_id']][$r['session_id']] = $r['present'];
}

echo "<table><tr><td>&nbsp</td>";
foreach($sessions as $s){
    echo "<td>$s</td>";
}
echo "</tr>";
foreach($pilots as $p){
    echo "<tr><td>$p</td>";
    foreach($sessions as $s){
        $tp = '';
        if(isset($s_presence[$p][$s])){
            if($s_presence[$p][$s] == '1'){
                $tp = 'X';
            }
        }
        echo "<td>".$tp."</td>";
    };
    echo "</tr>";
}
echo "</table>";

Answer 2

Luckily you only need one query. Presuming $rows is the format of your data withdrawn from the database:

<?php

$rows = array(
  array(
    'status_id' => 1,
    'session_id' => 61,
    'pilot_id' => 901,
    'present' => 1,
  ),
  array(
    'status_id' => 2,
    'session_id' => 63,
    'pilot_id' => 901,
    'present' => 1,
  ),
  array(
    'status_id' => 3,
    'session_id' => 62,
    'pilot_id' => 901,
    'present' => 0,
  ),
  array(
    'status_id' => 4,
    'session_id' => 62,
    'pilot_id' => 902,
    'present' => 1,
  ),
  array(
    'status_id' => 5,
    'session_id' => 63,
    'pilot_id' => 903,
    'present' => 1,
  )
);

$session_ids = array();
$pilot_ids = array();
$crosses = array();

foreach ($rows as $row) {
  $session_ids[$row['session_id']] = $row['session_id'];
  $pilot_ids[$row['pilot_id']] = $row['pilot_id'];
  if ($row['present'] == 1) {
    $cross_index = $row['session_id'].'.'.$row['pilot_id'];
    $crosses[$cross_index] = $cross_index;
  }
}

sort($session_ids);
sort($pilot_ids);

?>

<table>
  <tr>
    <th></th>
  <?php foreach ($session_ids as $sess_id): ?>
    <th><?php echo $sess_id; ?></th>
  <?php endforeach; ?>
  </tr>
  <?php foreach ($pilot_ids as $pilot_id): ?>
  <tr>
    <th><?php echo $pilot_id; ?></th>
    <?php foreach ($session_ids as $sess_id): ?>
    <?php if (isset($crosses[$sess_id.'.'.$pilot_id])): ?>
    <td>X</td>
    <?php else: ?>
    <td></td>
    <?php endif; ?>
    <?php endforeach; ?>
  </tr>
  <?php endforeach; ?>
</table>