Selecting a random id from a database

[Mark T.]

I am trying to build a script, which selects a random ID from my MySQL database. The ID's are all integers, ranging from 1 to whatever ID is the last is the database.

I have been trying to get a script running that selects a random number out of this heap of integers. This is what I have so far:

<?php
$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);
                          
$sql = "SELECT MAX(id) FROM Jokes";
$result = mysql_query($sql);
                          

?>

As you can see, I have a query requesting the maximum ID in the table. I now want to select a random number which has to be between 1 and the maximum id I requested. How would I integrate this in this script?

Thanks in advance .

[seanf]

If you just want to get a random record and have MySQL 3.23 or higher you can use ORDER BY rand()

Take a look at this example and freddy's code if you don't have 3.23:

http://sitepointforums.com/showthrea...highlight=RAND

Sean

[Mark T.]

Okay, I have MySQL 3.23.22, which looks sufficient to me . But I want to select one of the random numbers, and I presume ORDER By RAND() (thanks for introducing me to that function btw ), will generate a random list of all the ID's...

[seanf]

No, use it like this to get one random id:

$sql = mysql_query("SELECT ID FROM table ORDER BY rand() LIMIT 1") or die(mysql_error()); 

Sean

[Mark T.]

Thanks, I had the code set up like that, I only missed the LIMIT 1. I owe you one !

[seanf]

Your welcome And if you ever see me in a pub

Sean

[Mark T.]

Yep, if you help me out one more time, I will certainly do that .

I now have this code:

<?php
$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);

$sql = "SELECT ID FROM Jokes ORDER By RAND(id) LIMIT 1";
$result = mysql_query($sql);

[b]$sql = "SELECT Joke FROM Jokes WHERE ID = '$result'";[/b]

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo ($row["Joke"]);
?>

Can you spot the flaw? I think it is in the bold line of code, but I am not sure what is wrong.

[freddydoesphp]

$result holds nothing more than a ref to the query result and can't be used in this fashion. Also there is not need to run two queries. Also since you are only getting one record you don't need the while loop around mysql_fetch_array()

<?php
$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);

$sql = "SELECT * FROM Jokes ORDER By RAND() LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo ($row["Joke"]);
?>

[seanf]

I would change your code to this Mark:

$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);

$sql = mysql_query("SELECT Joke FROM Jokes ORDER BY rand() LIMIT 1") or die(mysql_error());
$result = mysql_fetch_array($sql);

extract($result);

echo $Joke; 

Sean

[Mark T.]

I just keep learning new tricks every day. Does extract() "unpack" the result to a string? The rest of the code seems quite familiar, only a few little changes.

[seanf]

It saves you having to do this:

$sean = $result["sean"]; 

Sean

[freddydoesphp]

http://www.php.net/extract


It takes an array and creates variables out of each element using the key as the variable name and the value as the value of the element.

[Mark T.]

Two PHP-Wizards coming to my aid for such a simple script. I feel honored ! Thanks freddy and sean, everything is working just the way I want it right now .