Updating a Last Access Time Stamp

[CBaZ]

I am trying to enter a new time stamp into mysql database when a user logs in at my site.

I read on the update syntax but can't seem to get it to do it.. It reads the values just doesnt write the values into mysql.

 

$update_query = mysql_query UPDATE users SET (last_access, ip_address, browser, hostname) values ($date, $IP, $browser, $hostname) WHERE username = '$login'") or die("QUERY FAILED: " . mysql_error()); 
$update = ($update_query) or die ("QUERY FAILED: " . mysql_error()); 

[phprocker]

Try this instead of both lines:

 

mysql_query("UPDATE users SET last_access = $date
WHERE username = $login")or die("QUERY FAILED: " . mysql_error());

[Pikachu2000]

You're using INSERT syntax rather than UPDATE syntax . . .

 

INSERT INTO `table` SET `field1` = 'string1', `field2` = 'string2' WHERE `some_field` = 'some_value'

[CBaZ]

that updates it but for some reason the WHERE isn't doing its job it updates all other usernames to the same time?? why?

[Pikachu2000]

Post your code in its current form.

[CBaZ]

$date = date("YmdHis"); 
$session = md5($date); 
$password_cypt = md5($password); 
$row = mysql_fetch_array($result,MYSQL_ASSOC); 
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']); 
$IP = getenv("REMOTE_ADDR"); 
$browser = getenv("HTTP_USER_AGENT"); 
$_SESSION["logged"] = "1"; 
$_SESSION["id"] = $row["user_id"]; 
$_SESSION["username"] = $row["username"];  
$_SESSION["firstname"] = $row["firstname"]; 
$_SESSION["lastname"] = $row["lastname"]; 
$_SESSION["email"] = $row["email"]; 
$_SESSION["created"] = $row["created"]; 
$_SESSION["last_access"] = $row["last_access"]; 
$_SESSION["pw"] = $row["pw"]; 
$_SESSION["permission"] = $row["permission"]; 
$_SESSION["count"] = $row["count"]; 
$_SESSION["photo"] = $row["photo"];
$_SESSION["session_id"] = $row["session_id"];

//echo "sql: ".$query."<br>"; 
//echo "Login success: username: ".$_SESSION["username"];
//Find Last Access
      $dd = substr($_SESSION[last_access],6,2);
      $mm = substr($_SESSION[last_access],4,2);
      $yyy = substr($_SESSION[last_access],0,4);
      $HH = substr($_SESSION[last_access],8,2);
      $MM = substr($_SESSION[last_access],10,2);
      $SS = substr($_SESSION[last_access],12,2);

      $access2 = "$mm-$dd-$yyy $HH:$MM:$SS"; 
$username = $_SESSION["username"];
$access = "$access2";
$login_message = "<p><br> Login Successful.<br><br> [$username] , Last Visited On: [$access]<br>Loading Please Wait..<img src=ajax-loader.gif border=0></p>\n";
if ($_SESSION[permission] == "1") { 
$connect = mysql_connect("localhost", "username", "pw") or die("Could not connect to database: " . mysql_error());
mysql_select_db("db", $connect) or die("Could not select database");

mysql_query("UPDATE users SET last_access = $date WHERE username = '$username'")or die("QUERY FAILED: " . mysql_error());
print "$login_message"; 

print "<META HTTP-EQUIV=\"Refresh\" Content=\"10; URL=password/admin.php\">"; 
} 

[Psycho]

You are also making this more difficult than it needs to be. MySQL allows you to add an attribute called "ON UPDATE CURRENT_TIMESTAMP". Just set that attribute for the "last access field". Then when a user logs in update the "ip_address", "browser" and "hostname" fields and the "last access" timestamp will be updated automatically.

[CBaZ]

I tried to set the on update attribute got an error

 

Error

SQL query:

 

ALTER TABLE `users` CHANGE `last_access` `last_access` VARCHAR( 14 ) ON UPDATE CURRENT_TIMESTAMP CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '00000000000000'

 

MySQL said: 

 

#1294 - Invalid ON UPDATE clause for 'last_access' column