MySQL Timestamp Update Problem.

Thora_Fan

I have never run across this problem before in 2 years of PHP/MySQL. Maybe it's been happening all along and I just didn't realize it.

I have a table that holds articles that are posted on a site. The table is designed as so:

+--------------------+---------------+------+-----+---------+----------------+
| Field              | Type          | Null | Key | Default | Extra          |
+--------------------+---------------+------+-----+---------+----------------+
| ID                 | int(11)       |      | PRI | NULL    | auto_increment |
| articleDate        | timestamp(14) | YES  |     | NULL    |                |
| articlePostedBy    | varchar(50)   |      |     |         |                |
| articleTitle       | text          |      |     |         |                |
| articleAuthor      | varchar(255)  |      |     |         |                |
| articleSource      | varchar(255)  |      |     |         |                |
| articleDescription | text          |      |     |         |                |
| articleBody        | text          |      |     |         |                |
+--------------------+---------------+------+-----+---------+----------------+

The problem I am having is that for some reason, the "articleDate" field updates every time I edit an article. In the past, it's never been this way. I tried changing the definition to allow NULL, but it didn't work. The SQL I am using to update the table is this:

$SQL = "UPDATE $objAdmin->dbTable
				SET articleTitle='$articleTitle',
				articlePostedBy='$_SESSION[userID]',
				articleAuthor='$articleAuthor',
				articleSource='$articleSource',
				articleDescription='$articleDescription',
				articleBody='$articleBody'  

				WHERE ID = '$ID'";

Everything updates just fine, but I don't want the articleDate field to update as well. Any ideas (if this made any sense)?

drawmack

My guess would be it's a corrupted index. Try repairing and optimizing the table and see if it still happens. If it does just work around it by retrieving the timestamp before the update and then updating and explicitly stating that the timestamp field equals the old timestamp. If you've just upgraded to a new version of mysql it could be a bug or a new feature so you may wish to check the documentation.

rachel2004

Originally posted by Thora_Fan
The problem I am having is that for some reason, the "articleDate" field updates every time I edit an article.

The timestamp column is doing what it is expected to be doing which is automatically updating itself whenever something has changed to the record. This occurs regardless of whether you you use NULL or speficially skip the column when doing INSERT or UPDATE. As a side note, if you have more than one "timestamp" type columns in a table, only the first "timestamp" column will automatically update itself.

nemonoman

TIMESTAMP type column automatically update to NOW() every time a record is inserted or updated PROVIDED that the column is not explicitly set with the SQL.

It's always been thus.

Thus:
UPDATE table SET id=id

Will set any TIMESTAMP column to NOW() (moment of update)

HOWEVER

UPDATE table SET id=id, MyTimestamp=MyTimestamp

Will preserve the current value of MyTimestamp column.

In your statement:

$SQL = "UPDATE $objAdmin->dbTable
SET articleDate=articleDate,
articleTitle='$articleTitle',
articlePostedBy='$_SESSION[userID]',
articleAuthor='$articleAuthor',
articleSource='$articleSource',
articleDescription='$articleDescription',
articleBody='$articleBody'

WHERE ID = '$ID'";

rachel2004

Originally posted by nemonoman
UPDATE table SET id=id, MyTimestamp=MyTimestamp

Will preserve the current value of MyTimestamp column.

I would say do this if you wish to salvage any records that have not been modified since their creation date, which I'm assuming "articleDate" captures. If you're depending on the "articleDate" to give you info about when an article was saved to the table, then you cannot reliably depend on the results since you know that records' timestamp column have been auto-updated.

Long term, however, you may wish to just use ALTER TABLE command and change the type from timestamp to any of the other date formats that MySQL provides. Use UNIX_TIMESTAMP() function in MySQL if you need to retreive the record in a, well, UNIX timestamp.

Or if you want the best of both words, still use ALTER TABLE, but only use it INSERT COLUMN, thus preserving your timestamp column and now adding another date type column that does not auto-update.

Thora_Fan

Thank you all for the resoponses. I am trying nemonoman's answer and it appears to be working for now. Thanks again.