Menus

Thursday, 10 December 2015

Array of database result to mannually display in table


Generate report of table format.

Code placed on controller action
Objective of this report for generating month wise admission count each year.

               $sql = $sql = "SELECT YEAR( date_of_join ) as eyear , MONTH( date_of_join ) as emonth , COUNT(  `admission_number` ) as totalstudents FROM  `students` WHERE YEAR(  `date_of_join` ) BETWEEN 2013 AND 2015 GROUP BY YEAR( date_of_join ) , MONTH( date_of_join ) ORDER BY YEAR( date_of_join ) , MONTH( date_of_join ) ASC ";                        
               
              $models = Students::findBySql($sql)->all();


Above this query generating result as 


eyearemonthtotalstudents
20131195
20132147
20133171
20134206
20135303
20136286
20137186
20138181
20139231
201310278
201311398
201312296
20141179
2014269
20143117
20144134
20145235
20146422
20147212
20148271
20149378
201410451
201411339
201412289
20151188
20152220
20153198
2015484
20155165
20156402
$model array represent the above result.

I want to expect result is 


COMPARISON REPORT
LOGICLAB DISTANCE EDUCATION
Year/MonthJanFebMarAprMayJunJulAugSepOctNovDecTotal
20131951471712063032861861812312783982962878
2014179691171342354222122713784513392893096
20151882201988416540226517421011700
Total56243648642470311106636266117307375867674



Answer 

One Solution


<?php

use yii\helpers\Html;
use yii\helpers\ArrayHelper;

$this->title = 'Comparison Report';
$this->params['breadcrumbs'][] = ['label' => 'Comparison Report Criteria', 'url' => ['studentreport/comparisonrpt']];
$this->params['breadcrumbs'][] = $this->title;

//set heading. 
$caption = 'COMPARISON REPORT';
$caption .=  '<BR>'.Yii::$app->params['center_name'];
$caption .=  ' '.Yii::$app->params['center_caption'];

?>
          
<!-- Main content -->
<section class="invoice">
            
     <table  class="table" >           
                <tr style= "background-color: #bfcfd2" ><td colspan="14"><b><?=$caption?></b></td></tr>                    
                <tr style= "background-color: #F2F2F2"><td>Year/Month</td>
                        <td>Jan</td>
                        <td>Feb</td>
                        <td>Mar</td>
                        <td>Apr</td>
                        <td>May</td>
                        <td>Jun</td>
                        <td>Jul</td>
                        <td>Aug</td>
                        <td>Sep</td>
                        <td>Oct</td>
                        <td>Nov</td>
                        <td>Dec</td>
                        <td>Total</td> </tr>
                        
 <?php                
                //total colums print with show university
                $dataList=ArrayHelper::map($model, 'eyear', 'eyaer');
                foreach ($dataList as $key => $value) {
                    echo '<tr><td>'.$value.'</td>';
                    for($i=1;$i<=12;$i++){
                        echo '<td id='.$value.$i.'>0</td>';
                    }
                    echo '<td id='.$value.'>0</td></tr>';  //for print row toatal.
                }
                    
                 //bootam row.
                echo '<tr style= "background-color: #4CAF50"><td>Total</td>';
                for($i=1;$i<=12;$i++){
                    echo '<td id='.$i.'>0</td>';
                }   
                echo '<td id="gtotal">0</td></tr>';
                                
                //1.isplay University counts, 2.row total, 3.column total, 4. Grand total. 
                foreach ($model as $key => $value) {                    
                    echo "<script> document.getElementById('".$value['eyear'].$value['emonth']."').innerHTML = ".$value['totalstudents']." </script>";                    
                    echo "<script> "                    
                    . "document.getElementById('".$value['eyar']."').innerHTML = parseInt(document.getElementById('".$value['eyear']."').innerHTML) + ". $value['totalstudents'].";"
                    . "document.getElementById('".$value['emonth']."').innerHTML = parseInt(document.getElementById('".$value['emonth']."').innerHTML) + ". $value['totalstudents'].";"
                    . "document.getElementById('gtotal').innerHTML = parseInt(document.getElementById('gtotal').innerHTML) + ". $value['totalstudents']        
                    . "</script>";                                        
                }
                ?>
                </table>            
        </section>









Posted by at

5 comments:

  1. Unknown7 April 2016 at 07:37

    Thank you!
    Great tutorial on arrays in Yii2.
    I have a query in mysql that want to display the result in the same way.

    Here is the table:
    CREATE TABLE Sales (empID INT, yr SMALLINT, sales DECIMAL(10,2));
    INSERT sales VALUES
    (1, 2005, 12000),(1, 2006, 18000),(1, 2007, 25000),
    (2, 2005, 15000),(2, 2006, 6000),(3, 2006, 20000),(3, 2007, 24000);

    and this query in mysql:
    1. In an inner query, write one aggregating expression per reporting column,
    2. In an outer query, build the horizontal sums:

    SELECT
    IFNULL(empId,'Totals') AS EmpId, -- outer query labels rollup row
    sums.2005, sums.2006, sums.2007, -- and calculates horizontal sums
    sums.2005 + sums.2006 + sums.2007 AS Sums
    FROM ( -- inner query groups by employee
    SELECT -- with an expression for each column
    EmpID,
    SUM(IF(Yr=2005,sales,0)) As '2005',
    SUM(IF(Yr=2006,sales,0)) As '2006',
    SUM(IF(Yr=2007,sales,0)) As '2007'
    FROM Sales
    GROUP BY EmpID WITH ROLLUP
    ) AS sums;

    And want to show the following table:
    +--------+----------+----------+----------+-----------+
    | EmpId | 2005 | 2006 | 2007 | Sums |
    +--------+----------+----------+----------+-----------+
    | 1 | 12000.00 | 18000.00 | 25000.00 | 55000.00 |
    | 2 | 15000.00 | 6000.00 | 0.00 | 21000.00 |
    | 3 | 0.00 | 20000.00 | 24000.00 | 44000.00 |
    | Totals | 27000.00 | 44000.00 | 49000.00 | 120000.00 |
    +--------+----------+----------+----------+-----------+

    ReplyDelete
    Replies
    1. Yii ides12 April 2016 at 04:29

      //One solutions mysql array result to table format



      //salesController index function

      public function actionIndex()
      {

      $sql= "SELECT
      IFNULL(empId,'Totals') AS EmpId, -- outer query labels rollup row
      sums.2005, sums.2006, sums.2007, -- and calculates horizontal sums
      sums.2005 + sums.2006 + sums.2007 AS Sums
      FROM ( -- inner query groups by employee
      SELECT -- with an expression for each column
      EmpID,
      SUM(IF(Yr=2005,sales,0)) As '2005',
      SUM(IF(Yr=2006,sales,0)) As '2006',
      SUM(IF(Yr=2007,sales,0)) As '2007'
      FROM sales
      GROUP BY EmpID WITH ROLLUP
      ) AS sums; ";


      $command = Yii::$app->db->createCommand($sql);
      $sum = $command->queryAll();


      $dataProvider = new ArrayDataProvider([
      'allModels' => $sum,
      ]);


      return $this->render('index', [
      'dataProvider' => $dataProvider,
      ]);
      }

      Delete
    2. Yii ides12 April 2016 at 04:33

      //Index page of display in grid view


      echo GridView::widget([
      'dataProvider' => $dataProvider,
      ]);

      Delete
  2. Yii ides12 April 2016 at 04:01

    This comment has been removed by the author.

    ReplyDelete
  3. Unknown15 April 2016 at 02:58

    Thank you!
    Works perfectly!
    Please keep up with this great tutorials about yii2.

    ReplyDelete

Subscribe to: Post Comments (Atom)