Don't update the attendance column if the time is after a certain time

[Pedroski55]

We have to do online classes again here in Nanjing, because of the virus. I have to keep a record of attendance.

The code below increments the attendance when a student logs in. Works fine.

The has_been_inc column is also incremented if it is not already 1. This prevents students from logging in multiple times and upping the attendance. This all works fine.

Before the next class I reset the has_been_inc column to zero.

What I want to do is also NOT increment the attendance if a student comes late. Not quite sure how to realise that.

// MY STUFF HOPE THIS WORKS！！    
					try{
			 		// attendance will not increase with multiple logins. Before next week, reset 'has_been_inc' to zero from phpmyadmin
			 		$mystmt = $pdo->prepare('UPDATE allstudents20EAP SET attendance = attendance + 1, has_been_inc = has_been_inc + 1, logon_time = LOCALTIME() WHERE email = :email AND has_been_inc != 1');	
  	 				$mystmt->execute(['email' => $email]);
  	 					}
				catch (PDOException $e){
    				$_SESSION['exception'] = $e->getMessage();
    				//if(!$e == ''){
    					//echo 'PDOException' . $e;
    				//exit();}
 		 		}    
   			 // END MY STUFF  

I think I need another AND LOCALTIME() !> How to format the time here??

I tried this just in phpMyAdmin, with brackets, with apostrophes, but I get an error:

UPDATE allstudents20EAP SET attendance = attendance + 1, has_been_inc = has_been_inc + 1, logon_time = LOCALTIME() WHERE email = '[email protected]' AND LOCALTIME() !> （2021-08-21 11:00:00）AND has_been_inc != 1

How should I format the time in this LOCALTIME() !> command??

Maybe the time must be in seconds??

[Strider64]

WHERE CURDATE() > todaysClass"; (My logic isn’t right, but I think you get the point I’m trying to make)

Wouldn’t it just be easier just to not allow everyone not do multiple check-ins for attendance?

[Pedroski55]

Thanks for your reply!

Not sure how to prevent multiple check-ins.

I have a kind of php timer on the webpages. If anyone tries to login too early, or too late, they just get a javascript clock and a message that says, “You are too early, or too late.” There is also a link to the webpage, which will take them to login if the time is right.

Anyway, I found a workaround for my problem, though it is PHP, not SQL: 2 “if statements” determine the $mystmt:

// MY STUFF HOPE THIS WORKS！！
try{
$timenow = new DateTime(‘NOW’);
$beginclass = new DateTime(“August 21, 2021 10:00:00”);
if($timenow > $beginclass) {
// attendance will not increase with multiple logins. Before next week, reset ‘has_been_inc’ to zero from phpmyadmin
$mystmt = $pdo->prepare(‘UPDATE allstudents20EAP SET has_been_inc = has_been_inc + 1, logon_time = LOCALTIME() WHERE email = :email AND has_been_inc != 1’);
}
elseif($timenow < $beginclass) {
$mystmt = $pdo->prepare(‘UPDATE allstudents20EAP SET attendance = attendance + 1, has_been_inc = has_been_inc + 1, logon_time = LOCALTIME() WHERE email = :email AND has_been_inc != 1’);
}
$mystmt->execute([‘email’ => $email]);
}

I can’t see how to do this just using SQL.