Single HTML Table from multiple MySQL tables

Question

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!

If you var_dump'd your $rows versus the $rows I have, you'd probably see the differences. I'd try using $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); — erisco
– erisco, Commented Apr 14, 2010 at 19:17
Ah, yes. I see that something's wrong. When I use mysqli_fetch_assoc I get: array(4) { ["status_id"]=> string(1) "7" ["pilot_id"]=> string(2) "33" ["session_id"]=> string(2) "65" ["present"]=> string(1) "1" } Which is only the first row... Why is that? Anyways, $rows = mysqli_fetch_all($result, MYSQLI_ASSOC) does not worked, though... (from what I googled, I don't have Mysqlnd - which I cannot install on my shared server). Here's the error: Fatal error: Call to undefined function mysqli_fetch_all(). Thanks! — David Chouinard
– David Chouinard, Commented Apr 14, 2010 at 20:23
I just ended up doing this: $rows = array(); while ($rawRow = mysqli_fetch_assoc($result)) { $rows[] = $rawRow; } — David Chouinard
– David Chouinard, Commented Apr 14, 2010 at 21:25

erisco · Accepted Answer · 2010-04-14 02:09:58Z

3

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>

edited Apr 14, 2010 at 2:09

answered Apr 14, 2010 at 2:01

erisco

14.3k2 gold badges43 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Chouinard Over a year ago

Thanks for you help! However, I'm quite confused. I've posted an answer below (the comment field is too small for my explanations), any ideas?

ariefbayu · Accepted Answer · 2010-04-14 01:55:01Z

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

Collectives™ on Stack Overflow

Single HTML Table from multiple MySQL tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related