A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearWhats the best way to implement time based sorting in php/mysql? - page 1
User InfoPosts
Whats the best way to implement time based sorting in php/mysql?#1
I have 1 table filled with articles. For the purpose of this post, lets just say it has 4 fields. story_id, story_title, story_numyes, story_numno

Each article can be voted YES or NO. I store every rating in another table, which contains 3 fields: vote_storyid, vote_date (as a timestamp), vote_code (1 = yes, 0 = no).

So when somebody votes yes on an article, it run an update query to story_numyes+1 as well as an insert query to log the story id, date and vote_code in the 2nd table.

I would like to sort articles based on how many YES or NO votes it has. For "Best of all time" rating is obviously simple.... ORDER BY story_numyes DESC.

But how would I go about doing best/worst articles today, this week, this month?

I get the timestamps to mark the cut-off dates for each period via the following:

$yesterday= strtotime("yesterday");
$last_week = strtotime("last week");
$last_month = strtotime("last month");


But Im not sure how to utilize these timestamps in a mysql query to achieve the desired results.

posted date: 2009-04-10 16:16:00


Re: Whats the best way to implement time based sorting in php/mysql?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-10 16:16:01


Re: Whats the best way to implement time based sorting in php/mysql?#3
In general:select story_id, sum(vote_code)from story_votegroup by story_id;For particular vote date ranges:select story_id, sum(vote_code)from story_votewhere vote_date >= (least date, inclusive( and vote_date < (last date, exclusive(group by story_id;OP comments:How would I use the ORDER BY clause? You(d add an order by sum(vote_code). Descending if you want stories with the most votes first:order by sum(vote_code) desc;On edit: I notice he wants all stories, not one, so I(m removing the having clause.

posted date: 2009-04-10 16:22:00


Re: Whats the best way to implement time based sorting in php/mysql?#4
SELECT a.*, SUM(vote_code) AS votesFROM articles a JOIN votes v ON (a.story_id = v.vote_storyid)WHERE v.vote_date >= $yesterdayGROUP BY a.story_idORDER BY 2 DESC;Likewise for $last_week and $last_month.If you want the results to be sorted, it(s better to do this in separate queries, instead of trying to do it in a single query. Because the sort order may be very different for each of the three periods.

posted date: 2009-04-10 16:27:00


Re: Whats the best way to implement time based sorting in php/mysql?#5
Im trying to stay away from subqueries to minimize CPU load. No way to do this in 1 go?

posted date: 2009-04-10 16:32:00


Re: Whats the best way to implement time based sorting in php/mysql?#6
And if you want "All time" then you'll need to extend the monthcount with CASE and remove the date restriction in the WHERE (which is what will be expensive).

posted date: 2009-04-10 16:33:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.