Don't update the "on update CURRENT_TIMESTAMP" only for a specific action

Question

On my website I've created an update_date column where I automatically store, for every single row, the date of when I update the rows of my table (every row is like an article page). I've done this using the on update CURRENT_TIMESTAMP.

The problem is that I've created also a pageview column where I store all the visits that users do to every single page.

What I would like to is to not automatically update the update_date when a new visit is accounted in the pageview column. Is there a possibility to obtain this writing some PHP code or just using a MySQL query?

Answer 1

If you set the timestamp field to its value, it won't be set to the current timestamp, so you can simply add update_date = update_date to your query:

UPDATE articles SET update_date = update_date, pageviews = pageviews + 1 WHERE id = :the_id;

But since you're actually updating your article, the update_date loose a little bit of its meaning. I suggest you to records visits in a distinct table, linked to the articles by a foreign key, so the article timestamp will be updated only when the article content is really updated, not just its page views count:

For example, it can be a table visits, where here the article id #1 has 13 views, and the article #3 has 7. The article #2, not present, has no view:

+--+----------+--------+
|id|article_id|pageview|
+--+----------+--------+
| 1|        1 |     13 |
+--+----------+--------+
| 2|        3 |      7 |
+--+----------+--------+

Then to retrieve the pageview count for article #3:

SELECT pageview FROM visits WHERE article_id = 3;

This dissociation also has the benefit of being a perfect place to store additional informations along the number of visits, by storing one row by visit with the querystring, the User Agent and if the visitor is logged its user_id for example:

complete_visits

+--+----------+-----------------------------+-------+-------+-------------------+
|id|article_id|querystring                  |UA     |user_id|visit_date         |
+--+----------+-----------------------------+-------+-------+-------------------+
| 1|        1 |     /article/1/?search=test |Firefox|null   |2012-12-12 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 2|        1 |               /article/1/   |Firefox|12     |2012-12-13 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 3|        1 |     /article/1/?search=a    |Firefox|null   |2012-12-14 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 4|        1 |     /article/1/?search=b    |Firefox|null   |2012-12-15 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 5|        1 |     /article/1/?search=c    |Firefox|null   |2012-12-16 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 6|        1 |     /article/1/?order=asc   |IE     |null   |2012-12-17 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 7|        3 |                 /article/3/ |Chrome |null   |2012-12-18 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 8|        3 |                 /article/3/ |Firefox|4      |2012-12-19 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+
| 9|        3 |     /article/3/?search=test |Firefox|null   |2012-12-20 12:12:12|
+--+----------+-----------------------------+-------+-------+-------------------+

To retrieve the pageview count for article #3:

SELECT COUNT(id) FROM complete_visits WHERE article_id = 3;

Answer 2

Either you change your table, so you don't autoupdate the update_date, but explicitly only set the update_date to now() when you update the contents, or, update contents set counter=counter+1,update_date=update_date where id=12345;