Jump to content


Photo

LIMIT and RAND()


  • Please log in to reply
8 replies to this topic

#1 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 06 September 2006 - 12:10 AM

Is it possible to use a limit and rand() combination? If yes, how would I use it? First, I'd like to pull x number of results (LIMIT x), which should bring x results, I then would like to display one of the x results randomly.

#2 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 06 September 2006 - 12:18 AM

I use the above like this....


$result = mysql_query("SELECT id,item2,item3,item4,item5,item6 FROM table ORDER BY RAND() LIMIT 0,5") 
or die(mysql_error()); 


Hope that helps :)

#3 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 06 September 2006 - 12:21 AM

Oops, forgot something. I also need to order it by something, so my code would look like this:


$result = mysql_query("SELECT * FROM table ORDER BY x LIMIT 0,5") 
or die(mysql_error()); 

I would then want to apply a rand() display, which will display 1 random from the 5 results.

#4 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 06 September 2006 - 12:25 AM

Well you're ORDERing the results by RAND().....so you can't also order it by something else as well I don't imagine

#5 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 06 September 2006 - 12:33 AM

Hmmm, well, I need to order it by "x" because I want to pull the top 5 results based on "x", then display only 1 randomly from the 5. Is there another way to do this?

#6 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 06 September 2006 - 12:42 AM

Perhaps you could put the results of the query into an array, then have the array randomoze it?

I'm not 100% sure about this, but I think it would work.

#7 sgiandhu

sgiandhu
  • Validating
  • PipPip
  • Member
  • 13 posts
  • LocationVancouver, B.C. Canada

Posted 06 September 2006 - 05:27 PM

I used the following recently on a site, which allowed me to choose the most recent items and display them randomly:

SELECT tblName.item FROM tblName WHERE MONTH(itemDate) = MONTH(CURDATE()) AND YEAR(itemDate) = YEAR(CURDATE()) ORDER BY RAND()  LIMIT 0, 5

I used WHERE instead of ORDER to select the items I wanted.

HTH.

Joss

#8 fenway

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

Posted 06 September 2006 - 06:14 PM

I wonder if LIMIT can take an expression... if so, you can use it to pull a random of the 5.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 07 September 2006 - 01:26 AM

If you're using MYSQL 4.1 or higher you can try something similar to the following as well.
SELECT
*
FROM
(
    SELECT
    *
    FROM
    tablename
    ORDER BY x
    LIMIT 5
) AS t 
ORDER BY RAND() 
LIMIT 1





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users