Random Number from Array of Primary Keys

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Random Number from Array of Primary Keys

Post 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);
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

Code: Select all

SELECT `PrimaryKey` FROM `Table` ORDER BY RAND() LIMIT 1
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post by richo »

i don't understand how i could implement this in my WHERE statement.

WHERE primary key = random primary key.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

you don't need a where statement.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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);
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post 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?
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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.
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post by richo »

:oops: oops!

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

:D cheers :D
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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).
richo
Forum Commoner
Posts: 58
Joined: Sun Aug 06, 2006 11:56 am

Post by richo »

cool, cheers for the pointers. i've learnt allot from doing this random thing.
Post Reply