Loop through multi dimension array to show grid in PHP

Question

I have 3 tables. Apls, Hulls and AplsHulls.

Apls consists of id, name, date Hulls consists of id, production_name AplsHulls is a join table and consists of id, apl_id, hull_id, status.

Not every Hull is associated with each Apl. The ones that are are in the join table with a status (shipped,in production, etc.)

I need to display a report in a table/grid that has the following columns headers: Apl Name, Apl_Date then the hull production names as the remaining column titles. (if hull 7 isn't in the result set, it doesn't even get a column.

For the data I need to list the apl name, apl date, then loop across the remaining columns and fill in the status for the records in the join table. If the apl and hull aren't associated in the join table, then just fill the cell with "NA".

I have tried this a bunch of different ways and I can currently get the dynamic list of column headers for the hulls, I never seem to be able to get the data to loop across correctly.

Sample Data:

Apls Table

Id: 1, Name: X1-0000, Date: 1/1/2009
Id: 2, Name: BG-5480, Date: 2/22/2009
Id: 3, Name: HG-0000, Date: 2/27/2009

Hulls Table

Id: 1, Production_name: ProdA
Id: 2, Production_name: ProdB
Id: 3, Production_name: ProdC
Id: 4, Production_name: ProdD

AplsHulls Table

Id: 1, Apl_id: 1, Hull_id: 1, Status:Delivered
Id: 2, Apl_id: 1, Hull_id: 3, Status:Ordered
Id: 3, Apl_id: 2, Hull_id: 4, Status:Delivered

I need the table to show like this:

  APL   |     Date     |    ProdA    |   ProdC   | ProdD
X1-0000 |  01/01/2009  |  Delivered  |  Ordered  | NA 
BG-5480 |  02/22/2009  |     NA      |     NA    | Delivered

Notice the column headers ignore ProdB since that record wasn't in the join table at all. Also it fills in NA for the columns that are in the join table but it may not have an association to in the join table.

It is very confusing, I know.

Answer 1

Assuming that you've pulled the table data into a set of arrays:

<?php
    $aplToHullMap = array();

    foreach( $AplsHulls as $row )
    {
        $aplID = $row[ 'apl_id' ];
        $hullID = $row[ 'hull_id' ];
        $status = $row[ 'status' ];

        if( isset( $aplToHullMap[ $aplID ] ) )
            $aplToHullMap[ $aplID ][ $hullID ] = $status;
        else
            $aplToHullMap[ $aplID ] = array( $hullID => $status );
    }
?>
<table>
  <tr>
    <th>Apl Name</th>
    <th>Apl Date</th>
<?php
    foreach( $Hulls as $row )
        echo( "<th>" . $row[ 'production_name' ] . "</th>\r\n" );
?>
  </tr>
<?php
    foreach( $Apls as $row )
    {
?>
  <tr>
    <td><?php echo( $row[ 'name' ] ); ?></td>
    <td><?php echo( $row[ 'date' ] ); ?></td>
    <?php
        $map = $aplToHullMap[ $row[ 'id' ] ];

        foreach( $Hulls as $hull )
        {
            if( isset( $map[ $hull[ 'id' ] ] ) )
                $status = $map[ $hull[ 'id' ] ];
            else
                $status = 'NA';

            echo( "<td>" . $status . "</td>\r\n" );
        }
    ?>
  </tr>
<?php
    }
?>
</table>

Answer 2

You can get the list of hulls you care about with a query like this:

select h.id, pname from hulls h join aplshulls on (h.id=hull_id)

You can (and probably should) stop reading this answer now and just use that get the columns you care about and then figure out how to put the data you have into the table.

But once you have that list of hulls you care about you can have your program write some evil sql to build the result for you. The code below assumes your DB library returns an array of rows for your sql query.

$hulls = query("select h.id, pname from hulls h join aplshulls on (h.id=hull_id)");
/* This should give a result like:
 * $hulls = array(array('id'=>1,'pname'=>'proda'),
 *                array('id'=>3,'pname'=>'prodc'),
 *                array('id'=>4,'pname'=>'prodd'));
 */

$sql = "select name, mdate";
foreach ($hulls as $row) {
    $sql .= ", ifnull({$row['pname']},'NA') {$row['pname']}";
}
$sql .= " from apls ";

foreach ($hulls as $row) {
    $sql .= " left join (select apl_id, status as {$row['pname']} from hulls h \join aplshulls ah on (h.id=hull_id) where pname='{$row['pname']}') as {$row['pn\ame']} on ({$row['pname']}.apl_id=apls.id)";
}
$sql .= " where apls.id in (select distinct apl_id from aplshulls)";

$result = query($sql);
foreach ($result as $row) {
  print "<tr>";
  foreach ($row as $value) {
    print "<td>$value</td>";
  }
  print "</tr>\n";
}

Replace the calls to query with your database query methods.

The resulting sql is:

select name, date,
       ifnull(proda,'NA') proda, ifnull(prodc,'NA') prodc, ifnull(prodd,'NA') prodd
from apls
     left join (select apl_id, status as proda
                from hulls h join aplshulls ah on (h.id=hull_id)
                where pname='proda') as proda on (proda.apl_id=apls.id)
     left join (select apl_id, status as prodc
                from hulls h join aplshulls ah on (h.id=hull_id)
                where pname='prodc') as prodc on (prodc.apl_id=apls.id)
     left join (select apl_id, status as prodd
                from hulls h join aplshulls ah on (h.id=hull_id)
                where pname='prodd') as prodd on (prodd.apl_id=apls.id)
where
  apls.id in (select distinct apl_id from aplshulls);

There is probably a better way to build the query but this should work. It probably breaks down if the number of hulls is very large. Or if any of the involved tables is very large. If your product names aren't legal in the sql you will need to map them to something else.