Muzafar Muzafar - 1 month ago 5
PHP Question

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

I want to show the records from two tables in same format as shown in the attached image link. please help how to show this either through PHP or 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

This should do the trick:

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