I am working on user online and offline status in php. My problem is that I need to update time in TIMESTAMP() column when user logged in. But here update query doesn't work. Can you please tell me that how to update it
$setLogged = mysqli_query($conn, "UPDATE user SET user_onlineStatus='" . time() . "'WHERE user_id='" . $user_id . "'");
Error is here:
"'WHERE user_id='"
should be:
"' WHERE user_id='"
Also, if you encounter errors like this, when your query doesn't work, always print query with var_dump() and run it directly in command line.
$sql = "UPDATE user SET user_onlineStatus='" . date('Y-m-d H:i:s') . "' WHERE user_id='" . $user_id . "'";
// var_dump($sql)
$setLogged = mysqli_query($conn, $sql);
$sql = "UPDATE user SET user_onlineStatus='" . date('Y-m-d H:i:s', time()) . "' WHERE user_id='" . $user_id . "'";
NOW() its so easy'" . time() . "' you're passing that as a string literal, it's a function.
UPDATE user SET user_onlineStatus = time() WHERE user_id='" . $user_id . "'
However, time() returns as a Unix timestamp 1463338870 format, rather than 2016-05-15 19:01:39 which is why it's failing you here, seeing your user_onlineStatus column is a TIMESTAMP type.
Ideally, you would be better off using the "now" method of today's time and date, your column type should either be DATE or DATETIME. Sidenote: As noted, a TIMESTAMP type is also valid (as per RiggsFolly's comment below).
so
"UPDATE user SET user_onlineStatus = now() WHERE user_id='$user_id' ";
If it isn't either, then I suspect it to be VARCHAR which is a bad idea.
Reference on DATE, DATETIME, and TIMESTAMP Types
If none of those worked for you, then your $user_id may be failing and is unknown whether or not it contains a value.
Make sure it does have a value, so check for errors.
Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code.
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
$setLogged = mysqli_query($conn,
"UPDATE user SET user_onlineStatus = NOW()
WHERE user_id='" . $user_id . "' ") or die(mysqli_error($conn));
A few unknowns:
mysqli_ to connect with, those different APIs do not intermix.Foonotes:
Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.
NOW() shoudl also work if the datatype is a TIMESTAMP
"UPDATE user SET user_onlineStatus=NOW()"time()function is working perfectly but problem is in the update query"UPDATE user SET user_onlineStatus=NOW() WHERE user_id='$user_id' "now()was that it makes the string concatenation unnecessary and that appears to be what you are struggling with.