0

Possible Duplicate:
MySQL select 10 random rows from 600K rows fast

There are many varying examples across the web, but I am hoping someone has a solution that fits what I am looking for... Surely I cannot be the only one!

I am building a photography website and want to display 50+ image thumbnails on the landing page. The kicker is that I want it to display pictures randomly using PHP and without duplication. I also want each image to have its own, unique href and title params.

So, I have created a mySQL table that holds:

id image_path, image_href, image_title

I was figuring I would easily re-size the images on my own and put them in a folder in the root directory and then call it in the row image_path.

Does anyone have an idea how to do this the most efficient way? I have heard that rand() sucks when the database gets big. The database will grow to tens of thousands of images.

Let me know if you need/want clarification.

Thanks!!!

Edit: I may have come across the answer in my research, but I'm pretty new to PHP... :)

Community
  • 1
  • 1
doobada
  • 29
  • 7

1 Answers1

7

You could query the database, sort the results by RAND() and select the top 50:

SELECT id, image_path, image_href, image_title 
FROM table 
ORDER BY RAND()
LIMIT 50

This guarantees no duplicate results unless your table itself has duplicates.

A faster method is outlined in this question: MySQL select 10 random rows from 600K rows fast

Community
  • 1
  • 1
Blender
  • 289,723
  • 53
  • 439
  • 496
  • 1
    I really love typing an answer and discarding it after I see that there's a way more elegant way of doing things, thanks! – emartel Dec 13 '12 at 23:18
  • @blender I read that rand() gets real slow when the table is large... – doobada Dec 13 '12 at 23:18
  • @doobada: Read the answers to the question I linked to at the bottom of my answer. – Blender Dec 13 '12 at 23:19
  • @Blender I didn't see it first time around, thanks for the link. How would I write the code to place the images in separate divs on the page? I don't want it to just spit out the 50 images in one div... – doobada Dec 13 '12 at 23:24
  • @doobada: I don't use PHP anymore so I'm not sure what are some good sites to link to, but that info is covered in a basic PHP/MySQL tutorial. – Blender Dec 13 '12 at 23:27
  • @Blender Thanks for the help! If you stumble on a good tut, please send it on over. – doobada Dec 13 '12 at 23:29