CODEWITHSUNDEEP
mysqlselect
Ali
Ali

Reputation: 22337

rotate mysql select

How can I rotate select by a given number.

I want to rotate select items in each visit of a visitor.

On first visit, the newest item is displayed.

On second visit, the second newest come first and the newest goes to the last page, last item.

On third visit, the third come first and the second goes to last page, last item just after newest.

I capture the visit count with a cookie on the first page.

But how can I select circularly?

Upvotes: 2

Views: 755

Answers (1)

glglgl
glglgl

Reputation: 91109

  1. Either you load the complete resultset into an array and output appropriately, or
  2. you issue two queries:
    • one for n till the end (LIMIT n+1, 100000000 will do in most cases) and
    • one for 1 till n (LIMIT n).

e.g. this way:

(SELECT foo FROM bar ORDER BY whatever LIMIT $n+1, 1000000) UNION ALL
(SELECT foo FROM bar ORDER BY whatever LIMIT $n)

with $n being the visit count excluding the current visit.

EDIT: As @ypercube pointed out, not only the UNION ALL is necessary, but the order of the result COULD be mixed up anyway. So it is better to issue two queries - which should be no problem, as the additional complexity in code is just marginal if you use a loop.

Upvotes: 2

Related Questions