[SOLVED] TIMESTAMP and UPDATE functions

[xProteuSx]

I have been totally unsuccessful in figuring this out, and I think I have spent enough time, and pulled out enough of my hair, that I can ask for help without any guilt:

I have a MySQL DB table called users, and for each entry there are two columns:  users_datejoined and users_lastvisit.  Now, when a user registers, datejoined is set to the time of registration.  Everything works up to this point.  Now, when this user returns to the site and logs in, I would like the users_lastvisit column to be updated.  Upon successful login, the following code should be executed:

[code]
$sql = "UPDATE users SET users_lastvisit='TIMESTAMP' WHERE users_handle='$mcathandle' AND users_randomkey='$mcatrandomkey'";
$sql_result = mysql_query($sql,$dbh) or die ('The error is as follows: <br><br>' . mysql_error());
[/code]

*note that $dbh is the connection string, which does work (I have tested this)!

So far, the column users_lastvisit is always at '0000-00-00 00:00:00'.  I cannot seem to change it.

What am I doing wrong?  What should the code for the DB UPDATE be??

Thanks in advance.

[artacus]

just use 'users_lastvisit = NOW()'

[xProteuSx]

Thanks.  That does update the users_lastvisit field.  HOWEVER, it also changes the users_datejoined field to the same value.  How can this be?

Here is the code for my CREATE TABLE:

[code]
$createusertable = "
CREATE TABLE users (
users_id int(6) UNSIGNED NOT NULL auto_increment,
users_randomkey int(8) UNSIGNED default NULL,
users_handle varchar(20) BINARY default NULL,
users_password varchar(20) BINARY default NULL,
users_email varchar (40) BINARY default NULL,
users_adminlevel int(1) UNSIGNED default '0',
users_status int(1) UNSIGNED default '0',
users_datejoined timestamp NOT NULL,
users_visits int (6) UNSIGNED default '0',
users_lastvisit timestamp NOT NULL,
users_questionsanswered int(6) UNSIGNED default '0',
users_correctanswers int(6) UNSIGNED default '0',
users_percentcorrect float default '0',
users_totalscore int (6) UNSIGNED default '0',
users_pagesviewed int(8) UNSIGNED default '0',
users_visitbonus int(6) UNSIGNED default '0',
users_activity int(6) UNSIGNED default '0',
PRIMARY KEY(users_id),
UNIQUE(users_handle)
) TYPE=MYISAM;
";
[/code]

Any ideas?  I have been here before, and I was not able to figure it out.

[artacus]

No problem here. Timestamp is doing what it is supposed to do. Change users_datejoined from timestamp to datetime and use NOW() when you create it.

[xProteuSx]

I just went into PHPMyAdmin and realized that the users_datejoined has an attribute of "ON UPDATE CURRENT_TIMESTAMP" and it won't let me change that setting.

Here is the error I get:


Error

SQL query:

[code]ALTER TABLE `users` CHANGE `users_datejoined` `users_datejoined` TIMESTAMP UNSIGNED NOT NULL DEFAULT '0000-00-00 00:00:00'[/code]

MySQL said: Documentation
#1064 - 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 'UNSIGNED NOT NULL DEFAULT '0000-00-00 00:00:00'' at line 1

[xProteuSx]

It seems that I do not comprehend the full function of TIMESTAMP.  This being so, I have just changed the data type of the users_datejoined column from TIMESTAMP to DATETIME and all is in working condition.  Thanks for the help.

[artacus]

Isn't that what I told you to do?

[xProteuSx]

I tried to figure out why I could not get it to work with the TIMESTAMP data type, but yes, this was what you told me to do.  Thanks Artacus.

[artacus]

That's what I said, its doing what a timestamp is supposed to do. (Keep track of modified or insert times.) So you are using a field type that wants to store the current time whenever the row is updated. You can get around this, but its easier to just use a datetime field.
From the mysql manual:
[quote]
A TIMESTAMP column is useful for recording the date and time of an INSERT  or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”.
[/quote]
More... http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html