Monthly Top 45 script

[koughman]

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

[fenway]

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.

[wickning1]

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]

[koughman]

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?

[fenway]

If so, you can always use STRTODATE() to convert into from a "date-like" string to a proper SQL date.

[koughman]

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

[Helljumper]

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.

[fenway]

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.

[koughman]

[!--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

[fenway]

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.

[koughman]

so without using mysql and using php is there a way i can get a script that shows the past 45 best entries?

[fenway]

No, without MySQL you can't do it at all.