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);
}
}
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
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 ..
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
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
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
I am trying to determine the customers spending behavior by writing a query to select the top 10 highest spenders
I am trying to pull spend and revenue by day for each of my websites