lalthung lalthung - 4 months ago 11
PHP Question

syntax error while trying to update time_out in hours table

im trying to update the time_out time to the last id where users time_in was captured, the following code of mine is giving me this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


here is my logout.php

session_start();
if(!isset($_SESSION['MEMBER_ID']) || (trim($_SESSION['MEMBER_ID']) == '')) {
header("location: index.php");
exit();
}

date_default_timezone_set('America/New_York');
$dt = date("Y-m-d h:i:s");
$id = $_SESSION['MEMBER_ID'];
$sql = "UPDATE hours SET time_out = '".$dt."' WHERE member_id IN (SELECT MAX(hours_id) FROM hours WHERE member_id = '".$id."'";
mysql_query($sql) or die(mysql_error());

//Unset the variables stored in session
unset($_SESSION['MEMBER_ID']);
unset($_SESSION['LOGIN_NAME']);
unset($_SESSION['PASS']);


here is the schema

`hours_id` int(11) NOT NULL AUTO_INCREMENT,
`member_id` int(11) NOT NULL,
`member_name` varchar(32) NOT NULL,
`team` varchar(32) NOT NULL,
`time_in` datetime NOT NULL,
`time_out` datetime DEFAULT NULL,
`dated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
unset($_SESSION['TEAM']);


what could be wrong in this query someone.?

Answer

Have closed the round brackets in your query like this?

$sql = "UPDATE hours SET time_out = '".$dt."' WHERE member_id IN (SELECT MAX(hours_id) FROM hours WHERE member_id = '".$id."')";

Note: Stop using deprecated mysql_* versions. There are better alternatives likes mysqli or pdo

EDIT

With single query you can fix your issue like this

$sql = "UPDATE hours SET time_out = '".$dt."' WHERE member_id IN (SELECT MAX(hours_id) FROM (SELECT MAX(hours_id) FROM hours WHERE member_id = '".$id."') AS t)";

Let me know if it works for you