Jump to content

Get Random Id From Database


kkkaiden
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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']}";}?>
Link to comment
Share on other sites

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

Edited by Synook
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. Edited by Synook
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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']}";}?>

Link to comment
Share on other sites

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'];?>

Link to comment
Share on other sites

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, :).
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...