mauro269 mauro269 - 1 month ago 5
MySQL Question

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

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

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;