Read data from a MySQL DB un-randomly

gravity · #1 (**permalink**) 10-20-08, 08:59

Hi,

I am a new member here, looking for a solution to a problem I have. I
am building a PHP web app, and am looking for a means to get data
from
a mysql DB in a not random manner (sequentially or however else).
Think of it as a Quote of the Day feature, with all quotes in the DB
having equal chances of being shown. It could be a random pick, but
in
a way that guarantees fairness. I have thought of just getting the
data sequentially but I am not sure its the best way, and how best to
implement this.

Please Assist.

Grav

healdem · #2 (**permalink**) 10-20-08, 09:25

I'd have a look at the rand function

gravity · #3 (**permalink**) 10-20-08, 11:10

Quote:

Originally Posted by healdem

I'd have a look at the rand function

Healdem,

I want all the entries in the DB to have an equal chance of being shown, whether sequentially or any other way. So how would rand work?

gvee · #4 (**permalink**) 10-20-08, 11:38

Does this air-code work?

Code:

SELECT *
FROM   quotations
ORDER
    BY Rand()
LIMIT 1;

gravity · #5 (**permalink**) 10-20-08, 12:58

Quote:

Originally Posted by georgev

Does this air-code work?

Code:

SELECT *
FROM   quotations
ORDER
    BY Rand()
LIMIT 1;

Not really, its not fair (some entries get selected multiple times before others are) which is not really what I want..

healdem · #6 (**permalink**) 10-20-08, 13:18

so where are your multiple entries coming from. the rand function can be used to select random records. Ive used this technique to display random images.. Ive yet to see duplicate images, admittedly I can't guarantee thee are not duplicates but Ive yet to see them

is the reason you are seeing duplicate because of your table design is it your data, is it becuase the query needs refining

have you read the references and examples that can be found on the web

what is your table definition, where are you seeing multiple rows.

gravity · #7 (**permalink**) 10-21-08, 05:02

Quote:

Originally Posted by healdem

so where are your multiple entries coming from. the rand function can be used to select random records. Ive used this technique to display random images.. Ive yet to see duplicate images, admittedly I can't guarantee thee are not duplicates but Ive yet to see them

is the reason you are seeing duplicate because of your table design is it your data, is it becuase the query needs refining

have you read the references and examples that can be found on the web

what is your table definition, where are you seeing multiple rows.

You misunderstood me healdem, I am not getting multiple rows but if I run the query 3 times I may get the same result 3 times (since its random), which is not what I want. I want an equal distribution.

healdem · #8 (**permalink**) 10-21-08, 06:17

Ive not seen that either
as said before Ive used rand to select random images and I have never seen the same images repeating, occasionally you get a duplicate but thats part of the random nature. rand doesn't guarantee you will NEVER get duplicates... it doenst' guarantee that all items will be selected.. however the probability is that you will get all items selected over time

are you using the function as documented on the MySQL website and elsewhere
what is the SQL you are using
are you setting a seed value for the rand function

r937 · #9 (**permalink**) 10-21-08, 06:48

Quote:

Originally Posted by healdem

occasionally you get a duplicate but thats part of the random nature. rand doesn't guarantee you will NEVER get duplicates...

but that's what he wants -- he wants all of them, randomly, but never the same one twice

healdem · #10 (**permalink**) 10-21-08, 06:57

in which case he needs to maintain a list of items that have been selected (or not selected as the case may be) and then randomly select from that list.

and thats before we start getting involved on how random a computer random number is

gvee · #11 (**permalink**) 10-21-08, 07:23

What about this for a solution

Pseudo code

Code:

Parameters: @current_id

Variable: @new_id

SELECT @new_id = Min(id)
FROM   quotations
WHERE  id > @current_id

IF @new_id IS NULL THEN
  SELECT @new_id  = Min(id)
  FROM   quotations
END IF

RETURN @new_id

Ultimately it's just cycling through the records based on an id (easily could be a different field)...

Internet Services

Web Development

Digital Marketing

Consumer Tech