循环遍历多维数组以在PHP中显示网格

I have 3 tables. Apls, Hulls and AplsHulls.

我有3张桌子。 Apls,Hulls和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.

Apls由id,name,date组成,包含id,production_name AplsHulls是一个连接表,由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.)

并非每个船体都与每个Apl相关联。具有状态(已发货,正在生产等)的连接表中的那些

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.

我需要在表/网格中显示一个报告,其中包含以下列标题:Apl Name,Apl_Date然后将船体生产名称作为剩余的列标题。 (如果船体7不在结果集中,它甚至不会得到一列。

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

对于数据,我需要列出apl名称,apl日期,然后循环其余列并填写连接表中记录的状态。如果apl和hull在连接表中没有关联,那么只需用“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.

请注意列标题忽略ProdB,因为该记录根本不在连接表中。此外,它为连接表中的列填充NA,但在连接表中可能没有关联。

It is very confusing, I know.

我知道这很混乱。

2 个解决方案

#1

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.

但是一旦你掌握了你所关心的船体清单,就可以让你的程序编写一些邪恶的SQL来为你构建结果。下面的代码假设您的DB库返回sql查询的行数组。

$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:

生成的sql是:

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.

可能有更好的方法来构建查询,但这应该有效。如果船体的数量非常大,它可能会发生故障。或者,如果任何涉及的表非常大。如果您的产品名称在sql中不合法,则需要将它们映射到其他内容。

#2

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>

#1