Using PHP mySQL to create 'most read' stories

[ShugNiggurath]

Hello,

I'm not an expert in mySQL, hence being here!

I've a database 'news' that has several sections 'sec', I store records as 'published' and have a counter 'read_count' which is increased by 1 each time it is viewed.

I want to be able to list the top 6 stories as most read, but want also to limit the time backwards on stories to 5 days - there are a minimum of 6 stories published each day.

SQL:
SELECT * FROM news WHERE sec='$sec' AND unix_timestamp(published) >= (unix_timestamp(NOW()) - 600000) ORDER BY read_count DESC LIMIT 0,30";

As I understand it, the unix_timestamp command is asking the database to look back 600000 seconds from NOW and then order the results by the read_count limited to 30 rows in highest - lowest order.

The results are not reflecting what they should be. Can anyone help show me where I'm going wrong?

Regards,
Hugh

 

[ShugNiggurath]

It's OK, I got it working. I was trying to use DATE_SUB earlier and I stupidly broke the query by using bad syntax, so I was trying to use straightforward arithmetic to test the string.

The syntax I am now using is as follows;

SELECT * FROM news

WHERE sec='$sec'

AND unix_timestamp(published) >= unix_timestamp(DATE_SUB(NOW(),INTERVAL 5 DAY))

ORDER BY read_count DESC LIMIT 0,6";

which returns me the top six read stories of the past 5 days.

I'm still relatively new to this stuff, so if there is anything wrong, weak or better performed another way in the code above, I'd appreciate tips...

Cheers,

Hugh