Pagination with Randomized DB Results

What is the best way to do this?

[nickCR]

So i've just spent about two hours searching the internet for a solution to my problem!

I am getting results from the database in an array. However the results are entirely randomized by MySQL using the "ORDER BY RAND()". This doesn't allow me to use the regular method of "LIMIT 0,10".

I thought the best solution would be to just pass the Array (only about 30 items right now with about 10 fields, this will grow to about 200 tops) into a $_SESSION array.

I would then use some loop to check the array key of each since the key corresponds to the results and goes [0][1][2][3] etc. So I wanted to use something like while:

while($arrayKey >= 0 AND $arrayKey < 10){
 echo "array contents here";
}

I haven't figured out how I would extract the key and use it in the array.

I already have the pagination in the script so it is looking for the page with GET.

Any suggestions on this?

Regards,
Nick

[rob7591]

to get the keys, you can do,

foreach($array as $key=>$val) {
//$key = whatever is between the [] . and $val = $array[$key]
}

your query would have to be something like:

if (!$_SESSION[$results]) {
$_SESSION[$results] = array();
$r = mysql_query("SELECT * FROM table ORDER BY RAND()");
while ($row = mysql_fetch_array($r)) {
$_SESSION[$results][] = $row;
}

then like:
$min = ($_GET['page'] - 1) * 10;
$max = $min + 9;
foreach($_SESSION[$results] as $key=>$val) {
if ($key >= $min && $key <= $max) {
// This is where you echo the db item
}
}

Not sure if that'll work.. I didn't test anything (and I've never made a pagination script, but that's what I could think of).

You could also maintain the random effect and probably increase speed a bit by scheduling a chronjob to reorder the database every day, and then just select it from the DB. (Maybe add a column called page_order, and have it increase consecutively, but in a random order every day) then you could just do:
SELECT * FROM table ORDER BY page_order

and you would still have a different order every day.

[nickCR]

Thanks a million for your response. I only needed the bottom half of the code since I already had the array but this is how I did it at the end:

// Extract Session

$campaigns = $_SESSION['campaigns'];

// Set Pagination Settings

$perPage = 10;
$min = ($page - 1) * $perPage;
$max = $min + 9;
$html = '';

// Render ads

$row = $min;
$position = 1;
while($row < $max and $row >= $min){
$html .= $someClass->generateWebAdHtml($campaigns[$row], $position);
$row ++;
$position ++;
}

echo $html;

[nickCR]

BTW the cron_job would be nice but I need the randomizing done everytime the ads are loaded. This is to give all advertisers a fair shake through the ranks.

[rob7591]

No Problem,

If you're going to change the $perPage in the future, you might want to have the line in your code:
$max = $min + ($perPage - 1);

Because that may throw everything off.

[mehh]

However the results are entirely randomized by MySQL using the "ORDER BY RAND()". This doesn't allow me to use the regular method of "LIMIT 0,10".

Are you sure? this works for me

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

[nickCR]

rob! that was messing things up a little when I was trying different $perPage. Your suggestion solves that thank you!

mehh: Yes the query still works and it will restrict the results to 10 but then when you go to LIMIT 10,20 it doesn't continue the results from 0,10 it randomizes them again. So basically you end up with another randomized list of 10. I need the list randomized only once so the user can shift through them.