Get Random Id From Database

[kkkaiden]

How do I get a random ID from the database, without using RAND(1, 999999) LIMIT 1 because that makes a random number between 1 and 999999 and i only have 246 in my database but if i change it to RAND(1, 246) then if i ever add more to my database it wont show any more above 246? help please?

[Synook]

Try:

SELECT RAND(1, MAX(id)) ...

[kkkaiden]

Try:

SELECT RAND(1, MAX(id)) ...

I'm using this but it wont work, am I doing it wrong?<?phpinclude'connect.php';mysql_select_db("$dbname");$query = "SELECT number FROM $tablename ORDER BY RAND(1, MAX(number)) LIMIT 1";$result = mysql_query($query) or die(mysql_error());while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ echo "{$row['number']}";}?>

[Synook]

That query will end up saying "order by 1", "order by 5" - it doesn't make sense to the MySQL engine (as far as I can tell).You can use the ORDER BY RAND() idiom, however, to select a single random record:

$query = "SELECT number FROM $tablename ORDER BY RAND() LIMIT 1 ";

The code I gave you above literally gives you a random id, so the below query, if the ids went unbroken from 1 - 246, would give you a random number between 1 and 246:

SELECT RAND(1, MAX(id)) FROM table

[shadowayex]

I'm not familiar with what Synook has done, so it might work. If not, you could use something like this:

<?php//connection$numofrecords = mysql_num_rows(mysql_query("SELECT * FROM table"));$query = mysql_query("SELECT * FROM table WHERE id = " . RAND(1, $numofrecords) . ");

I think that would do. This (as Synook's would) assumes that there's a straight, unbroken id list from 1 to whatever the highest id is. There's many ways to do it, but that's what I would do.

[Synook]

I'm not familiar with what Synook has done, so it might work.

ORDER BY RAND() is an idiom, while it doesn't sound syntactically correct it just orders the records randomly. Note that this takes a lot of time, and if you have over a couple of thousand records then it is faster to use application logic to figure out a random record.

This (as Synook's would) assumes that there's a straight, unbroken id list from 1 to whatever the highest id is.

The ORDER BY RAND() doesn't assume this - I was just pointing out that that is what SELECT RAND(1, MAX(id)) FROM table would produce if there was every number present.

[shadowayex]

When I said it'd assume, I meant your second piece. As I said, you first piece made no sense to me . Now I understand it though. You're right, that'd be ###### in a large database. Then again, this entire process seems like it'd be ###### in a large database.

[kkkaiden]

I just can't seem to get it to work...

<?phpinclude'connect.php';mysql_select_db("$dbname");$query = "RAND(1, MAX(id)) FROM tablename";$result = mysql_query($query) or die(mysql_error());while($row = mysql_fetch_array($result, MYSQL_ASSOC)){	 print "{$row['id']}";}?>

[Synook]

Why don't you use the idiom?

<?phpinclude'connect.php';mysql_select_db("$dbname");$query = "SELECT id FROM table ORDER BY RAND() LIMIT 1";$result = mysql_query($query) or die(mysql_error());$row = mysql_fetch_array($result, MYSQL_ASSOC);print $row['id'];?>

[kkkaiden]

Why don't you use the idiom?

<?phpinclude'connect.php';mysql_select_db("$dbname");$query = "SELECT id FROM table ORDER BY RAND() LIMIT 1";$result = mysql_query($query) or die(mysql_error());$row = mysql_fetch_array($result, MYSQL_ASSOC);print $row['id'];?>

Thanks alot Synook, now it works, .