phpmyadmin Question posted in
The official documentation can be found here.

How to count rows in a table (phpMyAdmin)

I am currently working on an attendance system using php and mysql and I want to count the number of Present/Absent/Late of that student, which is happened on the same table. Like for example the table looks like this.

student_name | attendance status |  date
             |                   |
student1     |  Present          |  2019-02-21
student2     |  Absent           |  2019-02-21
student3     |  Late             |  2019-02-21  
student1     |  Absent           |  2019-02-22
student2     |  Absent           |  2019-02-22
student3     |  Present          |  2019-02-22

I want output as below : Show how many presents/absents/late is a student in a month like

student 1    | 20 presents       |  4 absents   | 2 lates

I am using fpdf library but even a php code for that is a big help.

Table name : attendance_records

Solt’n

    $result = mysqli_query($conn, "
                                SELECT student_name,
  SUM(CASE WHEN attendance = 'Present' THEN 1 ELSE 0 END) AS presents,
  SUM(CASE WHEN attendance = 'Absent' THEN 1 ELSE 0 END) AS absents,
  SUM(CASE WHEN attendance = 'Late' THEN 1 ELSE 0 END) AS lates
FROM attendance_records
GROUP BY student_name
    ") or die("database error:". mysqli_error($conn));                                    
    foreach( $result as $row ) {
    $pdf->SetFont('Arial','I',9);
    $pdf->Ln();     
      foreach($row as $column) {                                                                                                                                                           
    $pdf->Cell(39,10,$column,1);
    }
}
Tags:

2

Answers


  1. 0

    Use in phpmyadmin ..

    SELECT * FROM table_name WHERE attendance_status = absent;

    Phpmyadmin will count it automatically..

    But you should code in php and mysql or mysqli ..

    Login or Signup to reply.
  2. 0

    You can combine SUM and CASE to achieve what you need

    Try using query

    SELECT student_name,
  SUM(CASE WHEN attendance = 'Present' THEN 1 ELSE 0 END) AS presents,
  SUM(CASE WHEN attendance = 'Absent' THEN 1 ELSE 0 END) AS absents,
  SUM(CASE WHEN attendance = 'Late' THEN 1 ELSE 0 END) AS lates
FROM attendance_records
GROUP BY student_name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search