Selecting random images using php and mysql

Question

I have a table containing images path it is like this

id i_id img         date

1   0   im.png  2015-05-12
2   0   im1.png 2015-05-12
3   0   im2.png 2015-05-12

And i am trying to echo out images using an src tag in php but what i need to do is that i need to echo out images in random manner and the table will grow also how to do this?. is it a php part or mysql part?.

@JayBlanchard is it like this SELECT *img_table ORDER BY RAND(id); — geekish fellow, May 12 '15 at 16:38
Google "show random image php mysql" many results. This http://stackoverflow.com/a/5660466/ being a good bet. Wouldn't you say so *Sam?* - @JayBlanchard It's `mysql_` but the idea/concept is there. They just need to use the other APIs ;-) — Funk Forty Niner, May 12 '15 at 16:45

score 0 · Accepted Answer · edited May 23 '17 at 12:14

There are two options. You can either do an ORDER BY RAND(), or you can do a select all and randomize the selected rows in PHP.

Option 1:

SELECT * from IMAGES ORDER BY RAND();

Option 2:

//select images (example uses mysql_ library; it's deprecated so DON'T USE IT!)
$query = "SELECT * FROM IMAGES";
$result = mysql_query($connection, $query)
$rows = mysql_fetch_all($result);
//randomize the array of images
shuffle($rows);
//output the images here

Option 1 can get very slow with large datasets. In my experience, Option 2 is faster, but it would be worth doing your own tests to see what works best for you. Usually the difference will be only fractions of a second. Check the answers here for more information: MySQL: Alternatives to ORDER BY RAND() or here: How can i optimize MySQL's ORDER BY RAND() function?

A third option is to generate random ids and select the albums that match those ids. This is only suitable if you don't have gaps in your ids. I have never tried this.

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 12 '15 at 16:46
@JayBlanchard It's just example code to illustrate the technique. Obviously it is better to use prepared statements anywhere where plain queries are being used, and to never use the mysql_ functions. I will edit my answer to make that clear. — Charles R, May 12 '15 at 16:50

Selecting random images using php and mysql

1 Answers1