Jump to content


Photo

Monthly Top 45 script


  • Please log in to reply
11 replies to this topic

#1 koughman

koughman
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 February 2006 - 10:40 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 February 2006 - 08:56 AM

Try the following (UNTESTED):

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

Make sure you have appropriate indexes. Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 08 February 2006 - 01:45 PM

If you're storing individual votes then you need a GROUP BY:

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


#4 koughman

koughman
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 08 February 2006 - 09:55 PM

in this query
SELECT * FROM yourTable WHERE DATE_SUB(yourDate, INTERVAL 1 MONTH)<=NOW() ORDER BY score DESC LIMIT 45
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?

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 February 2006 - 10:30 PM

If so, you can always use STRTODATE() to convert into from a "date-like" string to a proper SQL date.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 koughman

koughman
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 09 February 2006 - 01:34 AM

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


#7 Helljumper

Helljumper
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 February 2006 - 01:52 AM

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.

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 February 2006 - 03:32 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 koughman

koughman
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 10 February 2006 - 01:20 AM

[!--quoteo(post=343993:date=Feb 8 2006, 11:32 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 8 2006, 11:32 PM) View Post[/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

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 February 2006 - 09:03 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 koughman

koughman
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 10 February 2006 - 10:31 PM

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

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 February 2006 - 10:44 PM

No, without MySQL you can't do it at all.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users