I'm trying to send timestamp in database using PHP prepared statement? but somthing is going wrong

Here my PHP Script please let me know if I do something wrong.
The output of this script is showing

“You’re successfully Checked Out”

but in the database, the data isn’t sent onto it.


elseif (isset($_POST['Check-out'])) {
		$time = date("Y-m-d h:i:s");
		$date = date("Y-m-d h:i:s"); 
		$emp_id = $_SESSION['user-id'];
		$sql = "UPDATE `sys_attendance` SET `clock_out` = ? WHERE `emp_id` ='$emp_id' AND `c_date` ='$date'";
		$stmt = mysqli_stmt_init($conn);
		if (!mysqli_stmt_prepare($stmt,$sql))
		 {
			header("Location: ../admin/home.php?error=sqlerror", true);
			exit();
		}
		else{
			mysqli_stmt_bind_param($stmt,"s",$time);
			mysqli_stmt_execute($stmt);
			mysqli_stmt_store_result($stmt);
			
			$_SESSION['msg-type'] = 'success';
			$_SESSION['msg-descrp'] = "You\'re Successfully Checked Out";
			header("Location: ../admin/home.php?result=You-Are-Checked-Out_Now&time=".$time."&date=".$date."&emp_id=".$emp_id, true);
			exit();
		}
}

$time is an object. You would need to ->format() the object to get a string out of it.

BUT.

Dont do that.

if clock_out is a timestamp, and c_date is a date, in the database, send your query as:

"UPDATE `sys_attendance` SET `clock_out` = CURRENT_TIMESTAMP() WHERE `emp_id` = ? AND `c_date` = CURDATE()";

Parameterize your employee id, and use MySQL functions to handle the date and time.

2 Likes

thanks man

but I already format it

er yes. sorry, my brain read that as a datetime invocation instead of the date function.

Have you checked to see what mySQL is telling you the error is on your statement? You blindly execute and assume it worked.

yes I assumed that it work but it won’t and I dont get any kind of error on SQL

You’ve got a basic flaw in your logic. This statement will never be true

		$date = date("Y-m-d h:i:s"); 
		$sql = "UPDATE `sys_attendance` SET `clock_out` = ? WHERE `emp_id` ='$emp_id' AND `c_date` ='$date'";

$date is a full date time including time, so it shouldn’t find a match. Is c_date hold just a date or is it a timestamp?

If it’s a date, then @m_hutley’s approach is still best, but you can change your query to something similar. Just make it completely parameterized, not haphazard like it is now…

elseif (isset($_POST['Check-out'])) {
		$time = date("Y-m-d h:i:s");
		$date = date("Y-m-d ");    // note the different format here....
		$emp_id = $_SESSION['user-id'];
		$sql = "UPDATE `sys_attendance` SET `clock_out` = ? WHERE `emp_id` = ? AND `c_date` = ?"; // note the change here....
		$stmt = mysqli_stmt_init($conn);
		if (!mysqli_stmt_prepare($stmt,$sql))
		 {
			header("Location: ../admin/home.php?error=sqlerror", true);
			exit();
		}
		else{
			mysqli_stmt_bind_param($stmt,"s",$time);
			mysqli_stmt_bind_param($stmt,"s",$emp_id); // added to match changed query above
			mysqli_stmt_bind_param($stmt,"s",$date);  // added to match changed query above
			mysqli_stmt_execute($stmt);
			mysqli_stmt_store_result($stmt);
			
			$_SESSION['msg-type'] = 'success';
			$_SESSION['msg-descrp'] = "You\'re Successfully Checked Out";
			header("Location: ../admin/home.php?result=You-Are-Checked-Out_Now&time=".$time."&date=".$date."&emp_id=".$emp_id, true);
			exit();
		}
}

If c_date is a timestamp, it’s going to be more difficult because you’re going to have to convert the timestamp in the database to a date. The other issue here is you’re only allowing one check-in and out per day. That’s going to hamper you later.

1 Like

WHERE DATE(FROM_UNIXTIME(c_date)) = CURDATE()

That is a good way to kill database performance on larger tables.

In these cases BETWEEN is a better choices, WHERE c_date BETWEEN ‘2019-11-01 00:00:00’ AND ‘2019-11-01 23:59:59’

Edit:
If the column type in MySQL is TIMESTAMP, it is not a unix timestamp. It is a normal DATETIME structure, only set to UTC timezone, and it can use the database date functionality.

I’m surprised no one has mentioned the Mysql feature that automatically updates a column with a timestamp?

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

He may not always want to update the column value.

2 Likes

Thanks to all of you Finally I’ve completed my project! :innocent: :+1: