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

#1

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();
		}
}
#2

$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
#3

thanks man

#4

but I already format it

#5

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.

#7

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

#8

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
#9

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

#11

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.

#12

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

#13

He may not always want to update the column value.

2 Likes
#14

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