1

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 . "'");
  • You can simplify that by doing `"UPDATE user SET user_onlineStatus=NOW()"` – RiggsFolly May 15 '16 at 18:32
  • Thanks for the response. `time()` function is working perfectly but problem is in the update query –  May 15 '16 at 18:41
  • 1
    Well do as suggested `"UPDATE user SET user_onlineStatus=NOW() WHERE user_id='$user_id' "` – RiggsFolly May 15 '16 at 18:45
  • The only real reason I suggested using `now()` was that it makes the string concatenation unnecessary and that appears to be what you are struggling with. – RiggsFolly May 15 '16 at 18:51

2 Answers2

2

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);
Prokhor Sednev
  • 668
  • 7
  • 14
0

'" . 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:

  • If you're using sessions. If so, make sure you started the session.
  • The MySQL API to connect with. If you're using anything other than 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.

Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • @ImranHasan Right follow this advice if you wont follow mine – RiggsFolly May 15 '16 at 18:46
  • *Howdy Smokey* @RiggsFolly :-) – Funk Forty Niner May 15 '16 at 18:47
  • @RiggsFolly No not at all, thanks. I just thought I'd leave the quotes out of it ;-) we'll leave it at that. – Funk Forty Niner May 15 '16 at 18:48
  • 1
    `NOW()` shoudl also work if the datatype is a `TIMESTAMP` – RiggsFolly May 15 '16 at 18:49
  • @Fred-ii- I have recently changed format to `TIME`. Which function should I use now –  May 15 '16 at 19:01
  • @ImranHasan Use `DATETIME` however your `TIMESTAMP` type https://dev.mysql.com/doc/refman/5.5/en/datetime.html should have been ok. I've made a few edits to my answer therefore you would need to reload it. Details are in there as well as if your variable has a value or not. DATE only returns `YYYY-MM-DD` rather than DATETIME's `YYYY-MM-DD HH:MM:SS`. – Funk Forty Niner May 15 '16 at 19:03