How to count rows in a table (phpMyAdmin)

161
February 21, 2019, at 2:10 PM

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);
    }
}
Answer 1

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
Answer 2

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

Rent Charter Buses Company
READ ALSO
Exception during connection to JDBC MySQL

Exception during connection to JDBC MySQL

Good Afternoon: I'm a chemistry teacher from SpainI am not an experimented programmer, but I decided to create a small program to help my students with my subject

157
Updating a table column based on a join that is made from a union clause

Updating a table column based on a join that is made from a union clause

I need to update a table historicdata and get some ID's into a new columnThe id's exist in two separate tables, pallets, and pallets_archive

129
converting a sql query to subquery

converting a sql query to subquery

I am trying to determine the customers spending behavior by writing a query to select the top 10 highest spenders

154
Laravel Eloquent Relationship — How to Combine two Relationships on id and date?

Laravel Eloquent Relationship — How to Combine two Relationships on id and date?

I am trying to pull spend and revenue by day for each of my websites

157