fetch a random row from a mysql table

[Anon]

'lo...

i've a little question, as the subject indicated, i'm looking for a possibility to fecht a random row from a mysql table. is there any way to do this easily? i know there's a function mysql_fetch_row() but after reading the manual, I don't think this is what I need, as you can't really select any row from the table, but only after querying the database, and that's not what I want.

is there anybody who has a solution at hand? thanks in advance

sweeper

[Anon]

Sweeper, I am working on something to pull a random row from a mysql database. In my case, I am pulling faqs.

//get a random number from the total number of faqs in the db.

$db = mysql_connect("", "", "");

mysql_select_db("", $db);

$result = mysql_query("SELECT * FROM FAQS", $db);

$num = mysql_num_rows($result);
//gives me the total number of rows in the table

//random number between 1 and the total number of rows in the database
//if there are 20 faq's, will pull number between 1 and 20

$random_number = rand(1,$num);

$faq_result = mysql_query("SELECT * FROM FAQS WHERE id = $random_number", $db);
//pull a row from the db based on the random number

$row = mysql_fetch_array($faq_result);

There may be a better way to do this and I am still working on it. The problem I have is what to do when a faq is deleted from the db, the numbering which may ber 1-20, would have a missing number if one is deleted.

Hope this gets you started

sweeper wrote:

'lo...

i've a little question, as the subject indicated, i'm looking for a possibility to fecht a random row from a mysql table. is there any way to do this easily? i know there's a function mysql_fetch_row() but after reading the manual, I don't think this is what I need, as you can't really select any row from the table, but only after querying the database, and that's not what I want.

is there anybody who has a solution at hand? thanks in advance

sweeper

[Anon]

thanks for your reply, sammy, but that does what i not wanted to, using ID's, because of the same problem you run into, when a row eg. id is deleted, what then.

but you gave me a nice point to think about. mysql_fetch_rows returns an array with as much elements as there rows are. so let's say, i do the following:

$query = "select * from $table";
$result = mysql_db_query("$database",$query,$connection);
$rows=mysql_fetch_rows($result);
$random_row=rand(1,$rows);
$wanted_row=$rows[$random_row]

$first_field=$wanted[0];
$second_field=$wanted[1];
and so on...

i'll have to try it out.. i'm absolutely not sure wether this will work or not...
problems I see: when creating a random number, what does $rows return? the number of fields or NaN or...?
will $wanted_row convert to an array containing only the informations of the random row?

maybe this is an approach, maybe not... 🙂

greetings, and thanks for the answer
-sweeper

[Anon]

wee.. i got it working (almost)...

only problem i have now, is that it doesn't return the values out of the procedure.. strange that is.

here's the complete proc to select a random row from a mysql database.

function get_random_banner($connection, $database, $table) {

$query = "select * from $table";
$result = mysql_db_query("$database",$query);

$rand_value=@mysql_num_rows($result);
$banner_no=rand(0,"$rand_value");

$banner=mysql_fetch_row($result);

$advertisor = $banner[0];	
$banner_url = $banner[1];
$click_url  = $banner[2];
$referrer   = $banner[3];	
return $advertisor;
return $banner_url;
return $click_url;
return $referrer;
}

i don't know what i'm doing wrong with the return thingie.. but it isn't working.. somehow... gotta investigate into it a bit more... or if someone knows any possible problems with return? or sees what i'm doing wrong, would be great.

[Anon]

simple answer:

select ... from ... where ... order by rand() limit 1;

if you don't add the limit you can get your rows in random order... (note this works great for things like serving ads, etc)...

-andy

[Anon]

this looks pretty like what I need, the only problem i have, it doesn't work 🙁

mysql> select idstring from web_subscription order by rand();
ERROR 1064: You have an error in your SQL syntax near 'rand()' at line 1

is this version dependant? maybe the mysql version (3-22.21-4) i'm using hasn't got that feature implemented yet?

[Anon]

I just checked on a box that has 3.22.14... it doesn't work there. I know for a fact that this does work on 3.23.22 (as i just used this yesterday for something i was working on). If you can upgrade, I recommend that you do... there are a number of other features that really deserve an upgrade.

If you're concerned about stability, we serve about 1,000,000 page views/day. with a number of queries per page... our db box just keeps on truckin'

=)

-andy

[Anon]

Andy,

With regard to your code, if I do:

$faq_result = mysql_query("SELECT * FROM FAQS WHERE id = rand(), LIMIT 1", $db);

I still need a way to get a random number from those in the db, not just any random number. Can you expand on this to show me how to get a random number of those that are in the db. What if I have id's 1-20 in the db, then I delete id 13, I still want to pull a random id, but avoid 13.

I would use rand(1, 20) if the id's were 1-20. what next?

Andy Dickinson wrote:

simple answer:

select ... from ... where ... order by rand() limit 1;

if you don't add the limit you can get your rows in random order... (note this works great for things like serving ads, etc)...

-andy

[Anon]

Don't do where id = rand()...
do

$faq_result = mysql_query("SELECT * FROM FAQS ORDER BY RAND() LIMIT 1", $db);

That should get just one.

Sammy wrote:

Andy,

With regard to your code, if I do:

$faq_result = mysql_query("SELECT * FROM FAQS WHERE id = rand(), LIMIT 1", $db);

I still need a way to get a random number from those in the db, not just any random number. Can you expand on this to show me how to get a random number of those that are in the db. What if I have id's 1-20 in the db, then I delete id 13, I still want to pull a random id, but avoid 13.

I would use rand(1, 20) if the id's were 1-20. what next?

Andy Dickinson wrote:

simple answer:

select ... from ... where ... order by rand() limit 1;

if you don't add the limit you can get your rows in random order... (note this works great for things like serving ads, etc)...

-andy

[Anon]

if it was my box, i'd upgraded it already, but as this is my companies box I can't just upgrade... and well.. the server admin doesn't want to upgrade (never touch a running system was his answer, doh...)

well.. a new box should be set up here soon, i hope with a recent mysql version.. is there any other possibility to fetch a random row?

thx for your help

sweeper

[Anon]

For 3.22.x you can use:

select id, id*0+rand() as r from table_name order by r limit 1

8-)

[Anon]

I have written this after much thought and it works good!

$result = mysql_query("SELECT ID FROM table where ....");
$numrows = mysql_num_rows($result);

while ($ID = mysql_fetch_array($result)) {
$count[] =$ID["ID"];
}
$set = (rand (0,$numrows-1));
$random = $count[$set];

$sql = "select * from table where ID =$random";

Anyone know a better way - i am still learning myself.

Kevin

[Anon]

From the MySQL manual

In MySQL Version 3.23, you can, however, do:

SELECT * FROM table_name ORDER BY RAND()

This is useful to get a random sample of a set

SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000.

Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed.

-----That's from the manual.

You probably could use:

SELECT * FROM table_name ORDER BY RAND() LIMIT 1

-Andy

[Anon]

Well I don't know of any way to pick a random row without querying the database first but here's what I did..it's a little more optimized than the first solution you only need to access the database twice and when you do it returns limited data (for the first just all the faqIDs and the second just returns our randomly selected faqText. Here you go:

//first connect like normal:
$dbCon=mysql_connect("","","");
mysql_select_db("",$dbCon);

//now select all the faqID's:
$result=mysql_query("SELECT faqID FROM faqDB",$dbCon);

//get the number of entries:
$maxNum=mysql_num_rows($result);

select a random number between 0 and the number of entries -1. We do this because the mysql_result function indicies items starting at 0...you'll see why later :-)

$randNum=rand(0,$maxNum-1);

//here's the meat of the solution:
$text=mysql_query("SELECT faqText FROM faqDB WHERE faqID=".mysql_result($result,$randNum,"faqID"),$dbCon);

so instead of going back into the database with our new random number we select one of the entries in $result based on that random number. So if one of the entries in this FAQ DB is deleted it won't matter because we are selecting a random result faqID from select of all the faqIDs in the table.

//now just print the result
echo mysql_result($text,0,"blurbText");

hope that helps...

[Anon]

Regarding this statement:
SELECT * FROM table_name ORDER BY RAND() LIMIT 1

Will all records have to be retrieved from the table in this case, and then sorted by the random number first? Wouldn't it be inefficient?

[Anon]

I THINK I may have a solution.

It may be a round about way but it works...

mysql_connect($srv, $usr, $pwd) or die ("Problem connecting to DataBase");
$query = "select ... from ... ";
$result = mysql_db_query("...", $query);
$num_rows = mysql_num_rows($result);
srand ((double) microtime() * 1000000);
$query2 = "select ... from ... order by rand() LIMIT 1";
$result2 = mysql_db_query("...", $query2);
while ($r = mysql_fetch_array($result2))
{
echo "$r['field_name']";
}

Hope that helps!