[RESOLVED] Need help to select random rows from database without rand()

guanche

Hello,

I need help to display random user pictures from users table. My database is about more than 100 000 rows and I don't want to use "order by rand".

My idea was to generate a random number and to use it like "limit $random, 3". But there are some rows where "photo" is "0", and sometimes $rand is bigger than number of rows.

This is a normal query:

$rezult = mysql_query("SELECT file_name FROM users WHERE photo='1' and gender='1' ORDER by id desc LIMIT 0,3")or die('Error');

How do I select random rows?

Thanks.

dagon

Fast Random Results - "ORDER BY RAND()" is bad

guanche

dagon, yes, I know. That's why I am looking for a solution WITHOUT RAND();

NogDog

guanche;10952012 wrote:
dagon, yes, I know. That's why I am looking for a solution WITHOUT RAND();

Which is what that thread Dagon linked to tries to provide.

dagon

i didn't post the link for my health, some reading is required.

zstarsales04

Zstar Electronic Co.Ltd, Sell fire cards for DS/NDSL/NDSi, also have Wii, DSiLL, NDSi, NDSL, PSP2000, PSP3000, PS2, PS3, PSP go, PSP, Xbox360 accessories, all kinds of phones are available
www.zstar.hk
WWW.TIGERSUPERMALL.COM

guanche

Thank you dagon. Of course, I did read the thread. However, it doesn't answer my question. It's just a discussion without any solutions.

I didn't find any good solution yet.

I am going to add one more column to the DB table - 'views'

and than:

$result = mysql_query("SELECT file_name FROM users WHERE photo='1' and gender='1' ORDER by views LIMIT 0,3")or die('Error');

and update views+1

Weedpacket

guanche wrote:
It's just a discussion without any solutions.

...Except for the one in the very first post.

Shrike

I seem to remember reading somewhere (might even be here) that your user base can supply the "randomness" to this problem. The idea was the server just cycles through the list one by one, as far as the end user is concerned they see a random picture because there is no preset order in which users visit your site.

laserlight

Shrike wrote:
I seem to remember reading somewhere (might even be here) that your user base can supply the "randomness" to this problem. The idea was the server just cycles through the list one by one, as far as the end user is concerned they see a random picture because there is no preset order in which users visit your site.

That was Roger Ramjet's idea in that same thread in question.

guanche

Thank you laserlight. I did see the Roger Ramjet's idea:

// get the highest 2 ids
$res1 = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 2");
// generate random no between 0 and second highest id
$top = mysql_result($res1,1);
$rand = rand(0,$top);
// now query for id greater then random no
$sql = "SELECT * FROM table WHERE id>$rand ORDER BY id DESC LIMIT 1"

I had the same idea but the problem is that I need to select rows with "where" statement:

.... where image='1'

If $rand is a number which selects the last rows from the table, and all rows or some rows have image='0', the script will not display any last images. There may be several hundreds of last rows in the table with image='0' 🙁

bradgrafelman

So what's wrong with adding that condition to the first SELECT statement?

guanche

bradgrafelman, thank you. I am just stupid.