Results 1 to 7 of 7

Thread: Random select

  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: Random select

    Hi all,

    I've got a table with people information, and a second table with photos regarding those people. The people could have one or more photos.
    My problem is to get a random photo for each person.

    Does anyone have an idea...

    Many thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you need a random photo for one person, or a group of people (one photo each)?
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Posts
    95
    a need a random photo for everyone (1 person 1 photo)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the MySQL RAND function

    you will need to take appropriatye note of the performance implications of using rand to select images

    it may work on a subselect
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does RAND not just return a single value per dataset, rather than per row?
    (I'd love to look it up in the link you posted, but dev.mysql doesn't seem to load from this location!)
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'll try to dig out some code I've used before.. it grabs random images (well URL's to images) from a MySQL DB. IIRC the code grabbed 30 images from a pool of 5000+. it was used on a website to display the range of products sold, although we only used 15.. the over was to allow for images that were not found when the script actually ran (to avoid having to go fetch another random selection of images).
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ....just checked
    its a standard SQL select statement with an order by clause of
    Code:
    order by RAND() LIMIT 32
    however there is a note in the MySQL manual that Rand can be a resource hog... we use it to grab 32 images once, not grab a random image for every person.. I suspect this will have a significant performance impact (assuming this is a live requirement and not a piece of homework).
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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