php - 想要显示两个表中附图中提到的记录

Question

我想以相同的格式显示两个表中的记录，如所附图像链接所示。请帮助如何通过 PHP 或 MYSQL 来显示这一点。

Want to show the record as mentioned in attached image from two tables

<?php 
$con = @mysql_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password);
mysql_select_db( $mysql_db_database);

$res = mysql_query("SELECT  assembly, COUNT( CASE WHEN (`elect`.election_year =  '2013')  THEN  `tbl_observer_stats`.male_observers_trained ELSE NULL END ) AS '2013',
COUNT( CASE WHEN (`elect`.election_year =  '2014')  THEN  `tbl_observer_stats`.male_observers_trained   ELSE NULL END ) AS '2014',
COUNT( CASE WHEN (`elect`.election_year =  '2015')  THEN  `tbl_observer_stats`.male_observers_trained   ELSE NULL END ) AS '2015',
COUNT( CASE WHEN (`elect`.election_year =  '2016')  THEN  `tbl_observer_stats`.male_observers_trained   ELSE NULL END ) AS '2016',
COUNT( CASE WHEN (`elect`.election_year =  '2017')  THEN  `tbl_observer_stats`.male_observers_trained   ELSE NULL END ) AS '2017',
COUNT( CASE WHEN (`elect`.election_year =  '2018')  THEN  `tbl_observer_stats`.male_observers_trained   ELSE NULL END ) AS '2018'
FROM elect LEFT JOIN tbl_observer_stats ON (elect.id=tbl_observer_stats.election_id)  GROUP BY assembly") or die(mysql_error());
?>

<table width="812" class="listing resultList resultList--tbl_election" border="1">
        <tr class="table-headings">
        <th class="row-actions-header" width="157" rowspan="3"><div align="left"><strong>Assembly</strong></div></th>
        <th height="43" colspan="14" class="row-actions-header"><div align="center"><strong>Number of Observers Trained</strong></div></th>
              </tr>
              <tr class="table-headings">
                <th colspan="2" class="row-actions-header"><div align="center"><strong>2013</strong></div></th>
                <th width="74" colspan="2" class="row-actions-header"><div align="center"><strong>2014</strong></div></th>
                <th width="74" colspan="2" class="row-actions-header"><div align="center"><strong>2015</strong></div></th>
                <th width="74" colspan="2" class="row-actions-header"><div align="center"><strong>2016</strong></div></th>
                <th width="74" colspan="2" class="row-actions-header"><div align="center"><strong>2017</strong></div></th>
                <th width="114" colspan="2" class="row-actions-header"><div align="center"><strong>2018</strong></div></th>
                <th width="141" colspan="2" class="row-actions-header"><div align="center"><strong>Total</strong></div></th>
              </tr>
              <tr class="table-headings">
                <th width="30" height="43" class="row-actions-header">Male Observer</th>
                <th width="16" class="row-actions-header">Female Observer</th>
                <th class="row-actions-header">Male Observer</th>
                <th class="row-actions-header">Female Observer</th>
                <th class="row-actions-header">Male Observer</th>
                <th class="row-actions-header">Female Observer</th>
                <th class="row-actions-header">Male Observer</th>
                <th class="row-actions-header">Female Observer</th>
                <th class="row-actions-header">Male Observer</th>
                <th class="row-actions-header">Female Observer</th>
                <th class="row-actions-header">Male Observer</th>
                <th class="row-actions-header">Female Observer</th>
                <th class="row-actions-header">Male Observer</th>
                <th class="row-actions-header">Female Observer</th>
       </tr> 

      <?php  while($result = mysql_fetch_assoc($res)) {?>  

      <tr class="table-headings">
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td>
      <td class="field-content resultListCell">&nbsp;</td> 
      <td class="field-content resultListCell">&nbsp;</td> 
      <td class="field-content resultListCell">&nbsp;</td>
      </tr>
      <?php  }?>

        </table>

Answer 1

这应该可以解决问题:

<?php 
$con = @mysql_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password);
mysql_select_db( $mysql_db_database);


$res = mysql_query("select
a.assembly,
a.election_year,
COUNT(b.male_observers_trained) as male,
COUNT(b.female_observers_trained) as female
FROM elect a
LEFT JOIN tbl_observer_stats b ON (a.id=b.election_id)
GROUP BY a.assembly, a.election_year") or die(mysql_error());

$ar = array();
$years = array();
while($result = mysql_fetch_assoc($res)){
  /* fetch into multi array */
  $ar[$result['assembly']][$result['election_year']] = array('male'=>$result['male'], 'female'=>$result['female']);
  /* make array of distinct years */
  $years[$result['election_year']] = $result['election_year'];
}
mysql_free_result($res);
sort($years);

?>

<table width="812" class="listing resultList resultList--tbl_election" border="1">
        <tr class="table-headings">
        <th class="row-actions-header" width="157" rowspan="3"><div align="left"><strong>Assembly</strong></div></th>
        <th height="43" colspan="<?php echo count($years)*2 ?>" class="row-actions-header"><div align="center"><strong>Number of Observers Trained</strong></div></th>
              </tr>
              <tr class="table-headings">
              <?php
              foreach($years as $year){
                echo '<th colspan="2" class="row-actions-header"><div align="center"><strong>' . $year . '</strong></div></th>';
              }
              ?>

                <th width="141" colspan="2" class="row-actions-header"><div align="center"><strong>Total</strong></div></th>
              </tr>
              <tr class="table-headings">
              <?php
              foreach($years as $year){
                echo ' <th width="30" height="43" class="row-actions-header">Male Observer</th>
                <th width="16" class="row-actions-header">Female Observer</th>';
              }
                ?>

       <th width="30" height="43" class="row-actions-header">Male Observer</th>
       <th width="16" class="row-actions-header">Female Observer</th>
       </tr> 

      <?php

      $colSum = array();

      foreach($ar as $assembly => $data){
        $total_male = 0;
        $total_female = 0;
        echo '<tr class="table-headings"><td class="field-content resultListCell">' . $assembly . '</td>';
        foreach($years as $year){
          echo '
          <td class="field-content resultListCell">' . $data[$year]['male'] . '</td>
          <td class="field-content resultListCell">' . $data[$year]['female'] . '</td>';
        $total_male += $data[$year]['male'];
        $total_female += $data[$year]['female'];

        $colSum[$year]['male'][] = $data[$year]['male'];
        $colSum[$year]['female'][] = $data[$year]['female'];

        }
        echo '
      <td class="field-content resultListCell">' . $total_male . '</td> 
      <td class="field-content resultListCell">' . $total_female . '</td>
      </tr>';

       }

       echo '<tr class="table-headings"><td class="field-content resultListCell">TOTAL</td>';

       $totalSum = array();
        foreach($years as $year){
          echo '
          <td class="field-content resultListCell">' . array_sum($colSum[$year]['male']) . '</td>
          <td class="field-content resultListCell">' . array_sum($colSum[$year]['female']) . '</td>';
          $totalSum['male'][] = array_sum($colSum[$year]['male']);
          $totalSum['female'][] = array_sum($colSum[$year]['female']);

        }
        echo '
      <td class="field-content resultListCell">' . array_sum($totalSum['male']) . '</td> 
      <td class="field-content resultListCell">' . array_sum($totalSum['female']) . '</td>
      </tr>';
       ?>

        </table>