Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14

Thread: Random Row

  1. #1
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Row

    Hey guys,

    I just have a quick question about grabbing a random row from a mysql table.. how do you do it?
    I have tried "ORDER BY rand()" but it keeps picking the same row for some reason (maybe that reason can be explained).
    Any suggestions would be greatly appreciated.
    Thanks a lot,

    Matthew

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Try changing ORDER BY to LIMIT.

    Just a shot in the dark

  • #3
    New Coder
    Join Date
    Aug 2002
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    LIMIT is used to specify the number of rows returned, so isn't really appropriate here as far as i know. I would suggest something like:

    PHP Code:
    srand((double)microtime()*1000000);
    $randnumber rand(1,NUMBER); 
    Then select the row using the random number generated:

    SELECT * from TABLE WHERE RowID = '$randnumber'

    RowID should be some sort of auto incremented field using to identify rows. The 'NUMBER' above should be changed to the total number of rows in your table to avoid creating a random number which is more than your number of rows.

  • #4
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok,
    If I use php to generate a random number, and then grab that number from an autoincremented column on the table, wont that mess up if the number isnt there? It is grabbing information that will be added and deleted as time goes on. So autoincremented numbers will be lost here and there. Ex: The number of rows may be 30, but the highest autoincrement may be 50, because 20 rows have been deleted.
    I hope that makes sense. If so, how can I fix that part of it?
    Thanks a lot,

    Matthew

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do you run an old version of MySQL? Because

    SELECT * FROM table ORDER BY RAND() LIMIT 1;

    works very good for me... and should so since MySQL 3.23.2

  • #6
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am running version 3.23.52.
    ORDER BY RAND() keeps grabbing the same row for some reason.
    But, when I try to grab a random row from another table, it seems to work fine..
    Weird.. lol.

    Matthew
    Last edited by nurseryboy; 11-07-2002 at 03:11 AM.

  • #7
    Regular Coder
    Join Date
    May 2002
    Location
    London, England
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts
    how many rows do you have in the table? if its only one row.. what do you think will happen
    Jeewhizz - MySQL Moderator
    http://www.sitehq.co.uk
    PHP and MySQL Hosting

  • #8
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Haha, yeah, I thought of that.
    The fist thing I did was make sure I had at least two rows. That would be kinda dumb to be expecting something that wasnt there.. lol.
    Good suggestion though. Keeping me on my feet.

    Matthew

  • #9
    Regular Coder
    Join Date
    May 2002
    Location
    London, England
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts
    even if yo have two rows it could still bring up one row continually!
    Jeewhizz - MySQL Moderator
    http://www.sitehq.co.uk
    PHP and MySQL Hosting

  • #10
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Why would it do that? I would think eventually it would pick the other? Maybe?

    Matthew

  • #11
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Originally posted by nurseryboy
    Why would it do that? I would think eventually it would pick the other? Maybe?

    Matthew
    It would eventually but it would be much easier to have ten or more rows in there.

    So it's probably working just fine.

  • #12
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will add some more rows and try it out.
    Thanks for the suggestion. Hopefully thats it.

    Matthew

  • #13
    New Coder
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I added a few more and it seems to work. It sticks to one row more than the others, but overall they are random.
    Thanks a lot for the help, I think that was the problem.

    Matthew

  • #14
    Regular Coder
    Join Date
    May 2002
    Location
    London, England
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Glad we could help.... if you have two rows it could always go to the same row because its a 50/50 chance with 10 rows its a 10/90 chance... so more chance of getting another one!
    Jeewhizz - MySQL Moderator
    http://www.sitehq.co.uk
    PHP and MySQL Hosting


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •