Select Random MySQL Rows, and show the result

marsupillami

Hi!

I have a mysql table called Gallery, where there are many rows.

I would like to select a random row, and show three fields os that table (correspondig to the randomised row), image1, id and title.

Could anybody help me out with this?

Thank you

eoghain

The only way to reliably do this is if your ID column is synchronious. That is there are no breaks between 1 and the last ID. If that is the case you can do this in 3 steps:

Select the max(ID) from the table
Use this ID to generate your random number (from 1 to ID)
Use new random number to select row from table

marsupillami

Yes, there are no breaks between them. How can I select the last ID and generate a random number?

Thank you

eoghain

SELECT max(id) 
  FROM table1;

That is the query to get the max id.

// Assuming that you've placed max id into varible $maxID
$randomID = rand(1, $maxID);

That uses the [man]rand/man function to get a random integer between 1 and the $maxID.

$sql = "SELECT id, image1, title
          FROM table1
         WHERE id = " . $maxID;

That is the query you will build to get your information.

mrhappiness

SELECT
  image1, id, title
FROM
  table1
ORDER BY
  RAND()
LIMIT 1

won't work?

eoghain

That would probably work just fine, as long as the database supported both the RAND() function and the LIMIT clause.

However why make the database select everything just to get 1 row, if your database is large this will be more expensive than running 2 queries and a small piece of php.

mrhappiness

because maybe i will delete an entry and what if my php-rand() will produce this very id?

according to mysql-manual RAND() is "a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version"

eoghain

Sure, as long as you are migrating between versions of MySQL, but what if you decide to move to PostgreSQL, or some other database?

And I know there are limitations on the solution that I provided but they can be overcome with a little change to the solution.

Instead of selecting the max id from the table you select all of the id's and put them in an array and then generate a random number based on the number of items in the array, and choose the number from that array position and then select the image from the database.

marsupillami

Thank you guys 😉 That worked just fine.