display randomly but not repeat

[muralikalpana]

Hello,

i have some problem with this issue. i have to do display results randomly using mysql. but results don't repeat until one cycle completed.

i mean there are 6 results is there....

example first displyed randomly like 2 and 6, next 3 , 4 next 1,5 (one cycle completed)
again displaying randomly like 2 and 4, next 3,5 next 1,6 (cycle completed)

but not repeat result until cycle complete.

Any body help me.

thanks.

[pritaeas]

You can add an extra integer column to your table. Select two (using LIMIT) random items where you order the query by MIN(extra column) first, and RAND() second. After using the selected two, increment the extra field.

[muralikalpana]

sorry i did not get you. if you don't mind please explain. what is the use of extra column.

you mean like this: SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;

[pritaeas]

Define an extra column (integer).

SELECT * FROM table ORDER BY MIN(extra), RAND() LIMIT 2

Now you get two results. For each of them, increment the extra column by 1. Next time, using the same query, you will get two different results. Suppose you have six items in the table, after the third query, all values in the extra column are set to 1, so all options are equally available again.

[muralikalpana]

i understood but i have 1000s of results. then is there any problem will occur on pageloads. i mean slow loading.

[pritaeas]

I doubt it, but you will notice if it does.