Page 1 of 1
Random Number from Array of Primary Keys
Posted: Sun Sep 10, 2006 9:40 am
by richo
Hi, i want to select a random number from all primary keys in a mySQL table.
How would i do this?
At the moment i'm selecting a random number from 1 to the total number of rows. However, if i deleted a row in the table, it could still come up with a number that doesn't exist in the primary key column. Therefore, i need a way to either:
- detect that the primary key exists and if not, pick a random number again.
- or, create an array of all the primary keys and select a random number from them.
Any help, much appreciated!!
here is my existing code if it helps:
Code: Select all
/* Get total number of rows */
$count1 = mysql_query("SELECT * FROM slides");
$count = mysql_num_rows($count1);
/* Generate random number and get info based on pkey and random number */
$randSlide = mt_rand(1,$count);
$result2 = mysql_query("SELECT snapimg,title,pshortdesc FROM slides WHERE pkey = '$randSlide'");
$rowTotal = mysql_num_rows($result2);
Posted: Sun Sep 10, 2006 9:52 am
by jayshields
Code: Select all
SELECT `PrimaryKey` FROM `Table` ORDER BY RAND() LIMIT 1
Posted: Sun Sep 10, 2006 9:57 am
by richo
i don't understand how i could implement this in my WHERE statement.
WHERE primary key = random primary key.
Posted: Sun Sep 10, 2006 9:59 am
by Jenk
you don't need a where statement.
Posted: Sun Sep 10, 2006 10:00 am
by jayshields
Code: Select all
echo 'The random primary key is ' . mysql_result(mysql_query("SELECT `PrimaryKey` FROM `Table` ORDER BY RAND() LIMIT 1"), 0, 0);
Posted: Sun Sep 10, 2006 10:12 am
by richo
tried out the code, got this error:
Parse error: parse error, unexpected ',' in c:\Inetpub\wwwroot\index.php on line 12
(the line of the code)
Code: Select all
$ran2 = (mysql_query("SELECT pkey FROM slides ORDER BY RAND() LIMIT 1"), 0, 0);
okay, tried yours exactly and it works!
But how could i put this result into a variable?
Posted: Sun Sep 10, 2006 10:15 am
by richo
sorry i got it now:
Code: Select all
$rand = mysql_result(mysql_query("SELECT pkey FROM slides ORDER BY RAND() LIMIT 1"), 0, 0);
Many thanks! Much appreciated!
I have no idea how the code above works though, would you be able to explain it to me?
Posted: Sun Sep 10, 2006 10:19 am
by volka
SELECT ... ORDER BY fieldA
sql sorts the record according to the values in fieldA
rand() returns a random number. If used in ORDER BY Rand(), it is called once for each record; think of it as an additional field of the record. And then it's used for sorting; just like ORDER BY fieldA.
LIMIT 1 - only one record remains in the result set.
Posted: Sun Sep 10, 2006 10:25 am
by richo
Thanks, so you use the ORDER to build in the random and as it's only displaying 1, it brings back one random number bit of data from the list.
i'm now trying to place the random pkey into another select statement to bring back all the data for that row:
Code: Select all
$result = mysql_query("SELECT pkey,title,snapimg,pshortdesc FROM slides WHERE pkey = 'rand'");
but it doesn't seem to be working.
Posted: Sun Sep 10, 2006 10:41 am
by jayshields
The query looks at all the primary keys in the table, orders them randomly and then shows you the first one. You could leave out LIMIT 1, because you only view the first row when using mysql_result(), but you may aswell limit it to save execution time.
You should've said you wanted to just select a random row in the first place! Just change the query to this
Code: Select all
SELECT * FROM `table` ORDER BY RAND() LIMIT 1
and remove the last parameter from the mysql_result() call, so you return all the columns instead of just the first one.
Posted: Sun Sep 10, 2006 10:49 am
by richo

oops!
Cheers Jay, that's super helpful, looks like a really useful method!

cheers

Posted: Sun Sep 10, 2006 11:03 am
by jayshields
richo wrote:Thanks, so you use the ORDER to build in the random and as it's only displaying 1, it brings back one random number bit of data from the list.
i'm now trying to place the random pkey into another select statement to bring back all the data for that row:
Code: Select all
$result = mysql_query("SELECT pkey,title,snapimg,pshortdesc FROM slides WHERE pkey = 'rand'");
but it doesn't seem to be working.
And by the way, 2 reasons why that query didn't work. You have no $ before rand, so PHP doesn't know you are referring to the variable rather than just a string. The other reason is you put single quotes around it, you only need to use the quotes for fields that contain strings, and in this query, you are referencing a field which will/should contain integers (aka numbers).
Posted: Sun Sep 10, 2006 11:07 am
by richo
cool, cheers for the pointers. i've learnt allot from doing this random thing.