I save user inputs (comments) in these tables.
NEWS_COMMENTS
- id
- comment
PRODUCT_COMMENTS
- id
- comment
- product_id
I wanted to add a history feature to my website. Like;
-Someone commented on XX news.
-Someone commented on YY product.
-Someone commented on ZZ product.
You noticed the issue. I don't save timestamps or dates, so I can't be sure which comment comes first. It can be either the news comment or product comment.
Does MySQL has a feature to select last 24 hour inputs? For example,
NEWS_COMMENTS has 3 new entries in last 24 hours. PRODUCT_COMMENTS has 5 new entries in last 24 hours.
The query should select these 8 entries ordering by their entry date/timestamp.
Can I do this without altering my current pages? Can you provide a query?
All you need to do is add a column with type timestamp and the option DEFAULT CURRENT_TIMESTAMP. You probably don't need any other modifications to your code - MySQL will do the magic to ensure that the timestamp is automatically filled in when you insert rows.
Then you can get the rows from the last 24 hours with a simple query:
SELECT col1, col2, ..., coln
FROM yourtable
WHERE yourtimestampcol > NOW() - interval 24 hour
SELECT * FROM table WHERE timeStamp > NOW() - INTERVAL 24 HOUR
By adding a column with the type timestamp and its open DEFAULT CURRENT_TIMESTAMP this should be what your looking for, obviously changing the table name and timestamp column.
"Does MySQL has a feature to select last 24 hour inputs?"
With your current database structure, no. Is it possible at all? Yes. You'll need to use timestamps. The fact that you don't already for new comments signifies you haven't thought through the entire process of what you're trying to achieve.
Essentially you'll need to add a cooment_timestamp field (or other name) as a timestamp type to your database and add an ON UPDATE clause : ON UPDATE CURRENT_TIMESTAMP.
Then, you can query (SELECT) the data you need using the timestamp information.
SELECT * FROM `COMMENT_TABLE` WHERE comment_timetamp > NOW() - INTERVAL 24 HOUR