Generate columns and rows from mysql table. Array manipulation to display row as column and fill the "cells"

Question

Please, consider the following.

I have a table setup with relationships as follows. A hierarchical structure where the root has a 1:1 relationship with subRoot table, which contains information related to the Root. I can display the table as shown, using a query and a simple while loop.

Simplified model:

Father  1:n  Child  1:n  Root  1:1   subRoot
---          ---         ---         ---
1            S1          P1          yes
1            S1          P2          no
1            S2          P1          no
1            S2          P2          no
1            S3          P1          yes
1            S3          P2          yes
↓            ↓           ↓           ↓

What I am trying to accomplish, is to display the following and I'm having quite a bit of trouble...

Father    Root    S1  S2  S3  →
---       ---     --  --  --
1         P1      yes no  yes
1         P2      no  no  yes
↓

EDIT: The code for the table that I can generate (the first one). This is adapted code as the one i'm working way too long to post here. (sorry if there are any mistakes...)

<?
$q="SELECT * FROM
Child ,
Root,
subRoot
WHERE
Child.Father_ID = 1 AND Child.ID = Root.ID AND Root.ID2 = subRoot.ID2";
$r=mysql_query($q); 
$num=mysql_num_rows($r); 
?>


<table class="jl_tbl" id="hor-minimalist-b"> 
<tr> 
<th width="20px">Child</th> 
<th width="150px">Root</th> 
<th width="3%">subRoot</th> 
</tr>
<?
$i=0; 
while ($i < $num) { 
  $Child=mysql_result($r,$i,"Sx");   
  $Root=mysql_result($r,$i,"Px"); 
  $subRoot=mysql_result($r,$i,"YN");
?> 

<tr> 
<td><? echo $Child; ?></td>
<td><? echo $Root; ?></td>
<td><? echo $subRoot; ?></td>
</tr>
<? 
$i++; 
} 
?>

EDIT for @verbumSapienti .

enter image description here

obStandardWarning - don't use the mysql_* functions as they are deprecated — hd1
– hd1, Commented Apr 9, 2013 at 15:37

pulsar · Accepted Answer · 2013-04-10 13:06:24Z

1

<?php
    $i=0;
    while ($i < $num)
    {
        $Child=mysql_result($r,$i,"Sx");
        $Root=mysql_result($r,$i,"Px");
        $subRoot=mysql_result($r,$i,"YN");
        $root[$Root] = array($Child => $subRoot);
        $i++;
    }
    echo '<table>';
    echo '<tr>';
    echo '<th>Father</th><th>Root</th>';
    foreach($root as $Root => $array)
    {
        foreach($array as $Child => $subRoot)
        {
            echo "<th>$Child</th>";
        }
    }
    echo '</tr>';
    foreach($root as $Root => $values)
    {
        echo '<tr>'; 
        echo '<td>fatherSource</td>';
        echo "<td>$Root</td>";
        foreach($values as $subRoot)
        {
            echo "<td>$subRoot</td>";
        }
        echo '</tr>';
    }
    echo '</table>';
?>

edited Apr 10, 2013 at 13:06

answered Apr 9, 2013 at 16:56

pulsar

9951 gold badge9 silver badges22 bronze badges

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

2 Comments

wtz Over a year ago

This is looking good! But there is an incomplete line foreach($... :/

wtz Over a year ago

Hmm.. It kinda doesn't work exactly the way it should.. Here it takes the number of how many non repeat Child values there are and populates the foreach($array as $Child => $subRoot) { echo "<th>$Child</th>"; } part. Plus for the YN part it only shows the first column and takes the YN values for the S3 P1 P2 (See the start of the thread for the screenshot on how it looks.)

Collectives™ on Stack Overflow

Generate columns and rows from mysql table. Array manipulation to display row as column and fill the "cells"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related