Correct mySQL query for Student attendance entry

Question

I'm trying to develop a student attendance form using Codeigniter & MySQL. My table structure for the two tables are as follows:

student_mst table
-----------------
s_id(pk)
s_name
s_acssn_id 
s_prog_id
s_admission_no 
s_photo_name 

stu_attendance table
--------------------
sa_id(pk)
sa_s_id(fk)
sa_s_acssn_id
sa_s_prog_id
sa_atten_dt
sa_atten_code(A=Absent,P=Present)
sa_teacher_id

The design of my interface is as shown in the image below:

My plan is, on any particular date, a teacher can mark a student as either Present or Absent from the Action buttons for which ajax code is there which inserts rows in the above-mentioned stu_attendance table. And also every new day when the attendance entry form is opened, the Datatable plugin should open just like as shown in the image.

In order to accomplish my plans, I'm populating a Datatable plugin using the following query:

SELECT a.s_id AS sid,a.s_prog_id AS progid,a.s_acssn_id AS ssnid,a.s_name AS sname,a.s_admission_no AS sadmsnno,a.s_photo_name AS sphotonm,
       b.sa_atten_code AS acode 
FROM student_mst a 
LEFT JOIN stu_attendance b 
ON a.s_id = b.sa_s_id

The above SQL query works well for the first time (i.e. when stu_attendance table is empty) and populates the Datatable plugin as shown in the above image. I'm able to mark students as Present/Absent.

However, my plan fails the next day, because as per the query written above, it also picks and shows records of the students from the previous day which is not desired.

I hope I have made my problems clear. Anyone, please guide me. Should I change my table structures or should I change my design interface or should change my SQL query. Please help maybe with some code/examples.

score 0 · Accepted Answer · answered Aug 19 '21 at 03:26

0

Add a date check to the join so it only shows the current day's attendance.

SELECT a.s_id AS sid,a.s_prog_id AS progid,a.s_acssn_id AS ssnid,a.s_name AS sname,a.s_admission_no AS sadmsnno,a.s_photo_name AS sphotonm,
       b.sa_atten_code AS acode 
FROM student_mst a 
LEFT JOIN stu_attendance b 
ON a.s_id = b.sa_s_id AND b.sa_atten_dt = CURRENT_DATE()

answered Aug 19 '21 at 03:26

Barmar

741,623
53
500
612

Thanks for the reply@Barmar. I did use the CURRENT_DATE() in my query, but that would return 0 records when the attendance table is empty and would not show my datatable plugin as shown in the image. – gomesh munda Aug 19 '21 at 03:33
`LEFT JOIN` should make it show all the students, even if they don't have an attendance record on that day. – Barmar Aug 19 '21 at 04:06
Thanks, @Barmar, previously I was writing like WHER b.sa_atten_dt = CURRENT_DATE() and it was returning 0 records.But following your suggestion to use AND b.sa_atten_dt = CURRENT_DATE() and its working perfectly. – gomesh munda Aug 19 '21 at 06:35
See https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 for why the condition has to be in the ON clause. – Barmar Aug 19 '21 at 14:01

score 0 · Answer 2 · answered Aug 19 '21 at 03:27

0

use a where condition at the end of your query. for example:

where date like '%19%August%';

answered Aug 19 '21 at 03:27

Muhammad Mujtaba Shafique

299
2
12

score 0 · Answer 3 · answered Aug 19 '21 at 06:34

Use following query, it should work

SELECT a.s_id AS sid,a.s_prog_id AS progid,a.s_acssn_id AS ssnid,a.s_name AS 
   sname,a.s_admission_no AS sadmsnno,a.s_photo_name AS sphotonm,
       b.sa_atten_code AS acode 
  FROM student_mst a 
  LEFT JOIN stu_attendance b 
  ON a.s_id = b.sa_s_id AND b.sa_atten_dt >= date_format(curdate(),'%Y-%m-%d %H:00:00')

Correct mySQL query for Student attendance entry

3 Answers3