Foreach on columns and count other column inside

Yii 2.0 General Discussions
1

Hola !

Little question,

in Yii2 i have a table with thoses colums : ‘teacher’ - ‘testdate’

and i would count how many same ‘testdate’ i get for each 'teacher return something like this :

teacher1 - 03-06-2019 - 3

teacher2 - 03-06-2019 - 1

teacher2 - 04-06-2019 - 4

teacher4 - 03-01-2020 - 1

etc, etc,

Do you know how i should combine a foreach or what ’ Because i’m trying since 2 days now, with a foreach and some if/else, but i keep failiing… like having the last record not count or so…

my actual code :
public function actionRapports()
{
//rapports evaluateurs
$assures = Assures::find()->orderBy([‘ass_formateur’=>SORT_ASC,‘ass_date_test_mesure’=>SORT_ASC])->all();
//$count = Assures::find()->groupBy([‘ass_formateur’])->orderBy([‘ass_formateur’=>SORT_ASC,‘ass_date_test_mesure’=>SORT_ASC])->count();
/*
$eval = Assures::find()->groupBy([‘ass_formateur’])->all();
echo $count;
echo ‘

’;

//var_dump($assures);

foreach ($assures as $assure) {

# code…

echo $assure->ass_prenom.’
’;

}

    //die();
    // ->groupBy('ass_formateur')
  */  
    $spreadsheet = new Spreadsheet();
    $spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
    //$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
    $spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(0);
    $spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(1);
    $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
    $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);

    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    $spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

    $spreadsheet->getActiveSheet()->getStyle('A2:C2')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2');

    $sheet = $spreadsheet->getActiveSheet();        
    
    $sheet->setCellValue('A2', 'Evaluateur')->setCellValue('B2', 'Date mesure')->setCellValue('C2', 'Nombre de tests');

    $row=3;

    $i = 0; // loop
    $oldevaluateur = '';
    $oldmesure = '';
    $nbtest = 1;
    $nbprof = 0;
    
    foreach ($assures as $assure) {
        if(!empty($assure->ass_formateur)){
            $datemesure = $assure->ass_date_test_mesure;
            $evaluateur = $assure->ass_formateur;

            if($evaluateur == $oldevaluateur) {
                if($datemesure == $oldmesure) {
                    $nbtest++;
                }
                else{
                    $sheet->setCellValue('A'.$row,utf8_encode($oldevaluateur)); //evaluateur
                    $sheet->setCellValue('B'.$row,date('d-m-Y',strtotime($oldmesure)));
                    $sheet->setCellValue('C'.$row,$nbtest);  
                    $oldevaluateur = $evaluateur;
                    $oldmesure = $datemesure;
                    $nbtest = 1;
                    $row++;
                }
            }
            else {
                //info de l'évaluateur précédent si pas vide

                if(!empty($oldevaluateur)){
                    $sheet->setCellValue('A'.$row,utf8_encode($oldevaluateur)); //evaluateur
                    $sheet->setCellValue('B'.$row,date('d-m-Y',strtotime($oldmesure)));
                    $sheet->setCellValue('C'.$row,$nbtest);  
                    $oldevaluateur = $evaluateur;
                    $oldmesure = $datemesure;
                    $nbtest = 1;
                    $row++;   
                }
                
                $oldevaluateur = $evaluateur;
                $oldmesure = $datemesure;
                $nbprof++;
            }
        }
        else {

            $sheet->setCellValue('A'.$row,utf8_encode($oldevaluateur)); //evaluateur
            $sheet->setCellValue('B'.$row,date('d-m-Y',strtotime($oldmesure)));
            $sheet->setCellValue('C'.$row,$nbtest);  
                    
                
        }
    }
    
    $titre = 'Rapports Evaluateurs SDE '.$nbprof;
    $sheet->setCellValue('A1', $titre);

    $filename = "SDE_inlingualeman_rapport_evaluateurs".".xls";
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
   header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    $writer = new Xlsx($spreadsheet);
    //$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
   // Yii::$app->getSession()->setFlash('success', Yii::t('app', 'Rapport exporté.'));
//return $this->redirect('site/index');
}



Kind regards,
2

That should be done via SQL COUNT(*) and GROUP BY.

3

Hey, i tried, no success, if i do a gropby, i got only as result as i have teachers,
so even if i have 10 results with 5 differents teachers, i’ll get only 5 results with a groupby,

and i need all resutls because i need to export it with phpspreadsheet,
so i would do a foreach loop to do the

teacher1 - 03-06-2019 - 3
teacher2 - 03-06-2019 - 1
teacher2 - 04-06-2019 - 4
teacher4 - 03-01-2020 - 1

4

But in the example you provide you have exactly five results:

teacher1 - 03-06-2019 - 3
teacher2 - 03-06-2019 - 1
teacher2 - 04-06-2019 - 4
teacher4 - 03-01-2020 - 1

That’s definitely GROUP BY.

5

So let’s try to explain again :
Database :

ID - member - Date - teacher
1 - john - 2019-01-12 - marc
2 - paul - 2019-01-12 - marc
3 - francine - 2019-03-10 - marc
4 - jean - 2019-01-21 - sheila
5 - martine 2019-01-31 - sheila
6 - david - 2019-03-25 - Lilia
7 - julien - 2019-03-25 - lilia
8- daniel - 2019-03-25 - lilia
9 - sofia - 2019-04-01 - brad

And results i would :
Teacher - date - nb test
Marc - 2019-01-12 - 2
Marc - 2019-03-10 - 1
Sheila - 2019-01-21 - 1
Sheila - 2019-01-31 - 1
Lilia - 2019-03-25 - 3
Brad - 2019-04-01 - 1

6

That’s also GROUP BY but using multiple fields: teacher and date.

1 Like
7

Ok thank you very much, i must doing something wrong with my groupBy() i’ll try to understand it better and come back with my solution :slight_smile:

9

FOUND !!!

(in model : public $cnt)

in controler
$assures = Assures::find()
->select([‘ass_formateur’,‘ass_date_test_mesure’,‘COUNT(*) AS cnt’])
->groupBy([‘ass_formateur’,‘ass_date_test_mesure’])
->orderBy([‘ass_formateur’=>SORT_ASC,‘ass_date_test_mesure’=>SORT_ASC])
->all();

THEN :
foreach ($assures as $assure) {
echo $assure->ass_formateur.’ ‘.$assure->ass_date_test_mesure.’ ‘.$assure->cnt.’
’;
}