Select random records from mySQL database

hi,

how i can select some random records from my database?

 

Simple

SELECT * FROM table ORDER BY RAND();

PHP:

 

MySql's RAND() function might be too slow if you have huge number of records/rows.
If you are fetching only 1 random record, probably you could seed a random number thru php and then fetch the specific row based on its ID.

 

your suggestion is good in theory, but greatly flawed, a random id is of course random, so how do you make sure the random number generated exists as an id?

 

Give it a rough between RAND(1,1000) ?

 

still no dice, what you can probably do is
-query for the total number of records, or
-query for the highest id

which is still no foolproof since, what if one record gets deleted, eg: id = 10
if you rand a number between the lowest possible eg 1,100, and you get 10
then your script will break..

 

$id = "SELECT * FROM table ORDER BY id DESC LIMIT 1";

to get the id of the last row then

$rand = rand(0, $id);

then

SELECT * FROM table WHERE id = $id

 

hmm, i already mentioned this on my previous post, you run a risk of generating a value for an id that doesnt exist

 

If the query doesnt return any result for a particular id, you could try generating a new number.
However if you have only a few hundreds of records/rows you could depend on the MySql RAND() function itself.

 

I think MySql RAND() function is good, how many records is considered huge? hundreds of millions?

 

Maybe just something as simple as this would do the trick,

$info = $row['something']; // a column from the selected table 
if (empty($info)){ // do we have any data from the something column ?
    $info = header( "Location: $ThisURL" ) ; // no data, then refresh the page
    }

PHP:

Of course this would only come into play if the rand() id number generated didn't exists, meaning its just a failsafe function that would refresh the page on the website visitor until sometime could be displayed. I doubt very much this would interfere with the visitors experience, its just a random event.

 

How can I update a random row in a table, and update one field after the row has been randomly selected?

 

It is simple:


===============================================


$result = mysql_query('SELECT some_id FROM some_table');
if (!$result)
{
echo 'Could not run query: ' . mysql_error();
exit;
}
$ids = array();
$i = 0;
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$ids[$row[0]] = $i;
$i++;
}
$rand_row = array_rand($ids, 1);
$some_value = 'New content';
$result = mysql_query('UPDATE some_table SET some_field=' . $some_value . ' WHERE some_id=' . $rand_row);
echo 'Done';



===========================================

replace some_table with your table_name
replace some_id with your unique id for the row -> usually primary key
replace some_field with your row field to be updated
replace "New content" with your new value

Enjoy