Selecting random records and holding for pagination - HTML Forums

erme · 07-21-2009, 11:31 AM

Hi,

I'm building a business directory. When a user searches for a business I want the results to display randomly, as it would be unfair to order by date or alphabetically.

This is simple enough. Except I need to be able to save the random selection for pagination. This way there is no repeat of records and the user can view all the records available.

Is this possible? And if so, can anyone help with some strategies?

Thanks.

Vege · 07-21-2009, 11:38 AM

Quote:

Originally Posted by erme

Hi,

I'm building a business directory. When a user searches for a business I want the results to display randomly, as it would be unfair to order by date or alphabetically.

This is simple enough. Except I need to be able to save the random selection for pagination. This way there is no repeat of records and the user can view all the records available.

Is this possible? And if so, can anyone help with some strategies?

Thanks.

What database are you using?
What SSL?
If using MySQL i would say there is nothing easy about random

erme · 07-21-2009, 12:09 PM

Sorry, MySQL and PHP. Would it be a lot of work to get it right?

Mandarin · 07-21-2009, 01:23 PM

I assume you're doing something like this right now:

Code:

SELECT * FROM tbl_name ORDER BY RAND() LIMIT 0,10;

According to the MySQL documentation, the RAND() function can take an integer "as the seed value, which produces a repeatable sequence of column values." I tested this functionality to see if you could generate a random integer in PHP, store the unique value in a session variable for each user, and use that to reseed each query as you populate each results page. It seems to work fine. Sample queries for the first three results pages might look something like this:

Code:

SELECT * FROM tbl_name ORDER BY RAND(1621423) LIMIT 0,10;
SELECT * FROM tbl_name ORDER BY RAND(1621423) LIMIT 10,10;
SELECT * FROM tbl_name ORDER BY RAND(1621423) LIMIT 20,10;

scoutt · 07-22-2009, 07:56 AM

I don't think you can get around not showing the same business on other pages. if by random it will not remember what was on the last page. but it is the best you can get I believe

paul_norman_81 · 07-22-2009, 12:07 PM

Pretty easy I would have thought, and just as Mandarin has said... for each user you generate a random seed once and store it in a session variable ($_SESSION['seed'] = time(); perhaps, remembering to start your session on each page of course). You then pass this different seed to your mysql query. The results are then 'random' for each user, but you can paginate through them.

Vege · 07-23-2009, 02:05 AM

As far as i know order by rand() even with limit will fetch all data from a table to a hash table, then apply the limit.

So keep that in mind for tables with lots of data.