CODEWITHSUNDEEP
phpmysqldatabase
Mensur
Mensur

Reputation: 475

Fast random row in MySql

I need to generate a random row from my MySql database and i have found example here: http://akinas.com/pages/en/blog/mysql_random_row/

And i want to use solution 3 which looks like this: 

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `users` LIMIT $offset, 1 " );

My code looks like this now: 

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( "SELECT * FROM `users` WHERE profile_image='2' LIMIT $offset, 1 " );
$random_date = mysql_fetch_array($result);

echo $random_date['user_name']; //display username of random user

But when i refresh the page: approximatly 7 of 10 times nonthing shows up. No username at all and also im trying to print out the id of the user but it's also empty. It seems that it's not getting anything at all from the database when refreshing and sometimes it get's data from the database. Any idea why this might happen?

Upvotes: 1

Views: 254

Answers (3)

Mensur
Mensur

Reputation: 475

The solution for my problem was this, BIG thanks to: @Jack

This query will find random row in a mysql table very fast.

$gender_search =  $logged_in_user['gender_search'];

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users` WHERE profile_image='2' AND gender='$gender_search'");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( "SELECT * FROM `users` WHERE profile_image='2' AND gender='$gender_search' LIMIT $offset, 1 " );
$random_date = mysql_fetch_array($result);

I have now converted this string to PDO if someone needs it. 

$statement = $dbConn->prepare("SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users` WHERE profile_image=? AND gender=?");
$statement->execute(array('2',$logged_in_user['gender_search']));
$offset_row = $statement->fetch(PDO::FETCH_OBJ);
$offset = $offset_row->offset;

$statement2 = $dbConn->prepare("SELECT * FROM `users` WHERE profile_image=? AND gender=? LIMIT ?, 1");
$statement2->execute(array('2', $logged_in_user['gender_search'], $offset));
$random_date = $statement2->fetch(PDO::FETCH_BOTH);

Upvotes: 0

exussum
exussum

Reputation: 18550

Its because in your first query you dont have a where, but in the second query you do.

if you have 50 rows and only 15 with profile_image = 2 That will be the reason why most appear as nothing.

Your query becomes LIMIT 30,1 when there are only 15 results for example

Make sure the same where is used in both queries.

Also avoid using mysql_* functions in new code

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173572

In this particular case, the problem is that you're working with two different queries; the first query is:

SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users`

Whereas the second one includes a condition:

SELECT * FROM `users` WHERE profile_image='2' LIMIT $offset, 1
                      ^^^^^^^^^^^^^^^^^^^^^^^

Both queries should operate over the same result set.

Upvotes: 2

Related Questions