CODEWITHSUNDEEP
phpmysql
wtz
wtz

Reputation: 383

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

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

Upvotes: 0

Views: 224

Answers (1)

pulsar
pulsar

Reputation: 987

<?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>';
?>

Upvotes: 1

Related Questions