Jump to content

Archived

This topic is now archived and is closed to further replies.

koughman

Monthly Top 45 script

Recommended Posts

im looking for a mysql script to show the last 45 entries ordered by score in the past month, thanks

Share this post


Link to post
Share on other sites
Try the following (UNTESTED):

[code]SELECT * FROM yourTable WHERE DATE_SUB(yourDate, INTERVAL 1 MONTH)<=NOW() ORDER BY score DESC LIMIT 45[/code]

Make sure you have appropriate indexes. Hope that helps.

Share this post


Link to post
Share on other sites
If you're storing individual votes then you need a GROUP BY:

[code]SELECT entry, COUNT(*) as cnt FROM votetable WHERE voteDate >= NOW() - INTERVAL 1 MONTH GROUP BY entry ORDER BY cnt DESC LIMIT 45[/code]

Share this post


Link to post
Share on other sites
in this query[code]SELECT * FROM yourTable WHERE DATE_SUB(yourDate, INTERVAL 1 MONTH)<=NOW() ORDER BY score DESC LIMIT 45[/code] what do you mean by yourdate? like the date of the entry, in that case im using php date function and wouldnt that screw it up?

Share this post


Link to post
Share on other sites
If so, you can always use STRTODATE() to convert into from a "date-like" string to a proper SQL date.

Share this post


Link to post
Share on other sites
so then [code]SELECT * FROM yourTable WHERE DATE_SUB(STRTODATE(), INTERVAL 1 MONTH)<=NOW() ORDER BY score DESC LIMIT 45[/code]

Share this post


Link to post
Share on other sites
Nah, dude.

`yourTable` is the name of the SQL table that this data is stored in.
`yourDate` is the field of your table that the date is in.

Share this post


Link to post
Share on other sites
You should check the syntax of the STR_TO_DATE() function -- you need to pass in your PHP date string, and then define how it is composed: for example, STR_TO_DATE('04/31/2004', '%m/%d/%Y'). Simply substitute your PHP date for the date string and you're done.

Share this post


Link to post
Share on other sites
[!--quoteo(post=343993:date=Feb 8 2006, 11:32 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 8 2006, 11:32 PM) [snapback]343993[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You should check the syntax of the STR_TO_DATE() function -- you need to pass in your PHP date string, and then define how it is composed: for example, STR_TO_DATE('04/31/2004', '%m/%d/%Y'). Simply substitute your PHP date for the date string and you're done.
[/quote]
my date is like January 5th 2005 and im a n00b with php/mysql can one of you plz help with the exact script, thanks

Share this post


Link to post
Share on other sites
Yeah, the date suffix is going to be a problem -- if this is coming from PHP, you have your choice of how this is represented; pick something useful instead.

Share this post


Link to post
Share on other sites
so without using mysql and using php is there a way i can get a script that shows the past 45 best entries?

Share this post


Link to post
Share on other sites
No, without MySQL you can't do it at all.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.