CODEWITHSUNDEEP
phprandommysqli
Brian Patterson
Brian Patterson

Reputation: 1625

How to use MYSQLI store_result (and dataseek?) to paginate random results?

I need to pull a data set using PHP and order it randomly. Every time it is pulled, I need a different random order.

Once the data is pulled I'm using ajax/jquery to pull the results 12 at a time. So I have to keep working with the same result set, only pulling 12 records at a time based on the pagination.

Can anyone explain to me how to do this with mysqli/php, I think it would be using store_result and possibly data_seek. But I was hoping someone on here with some experience with this could point me in the right direction.

My current query ...

SELECT * FROM mytable WHERE status='active' limit 0,12

Upvotes: 0

Views: 265

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157910

So I have to keep working with the same result set

That's wrong assumption. There is no old result set by the time you're calling for the next page.

So, you'd better stick with mimipc's solution, it's pretty smart one.

Upvotes: 0

mimipc
mimipc

Reputation: 1374

I did it once using the MD5 function. You generate a random seed (salt) with php and just append it to all your IDs for example :

SELECT * FROM mytable WHERE status='active' ORDER BY MD5(CONCAT(id, :salt)) DESC LIMIT 0,12

(Replace :salt with 'aE41n' for example)

Then, all you have to do is storing the salt in a session for each user.

Don't know if it's the best solution considering performances, but I hope it will help.

Upvotes: 2

Related Questions