Mysql help with view more button.

Question

I am working on a widget that is a lot like twitters widget where there is a list of postings and a view more button. I can get it to work with using ID variables but I would like to sort by popular posts.

Here is my mysq code:

$sql = "SELECT id, title, category, icon_normal, status, description, views_monthly FROM posts WHERE views_monthly<=".$lastPost." AND status='1' ORDER BY views_monthly DESC LIMIT 9"

So the problem that I am having is it shows the first 9 just fine. When it gets to the point where views_monthly = 0 then it just loads the same 9 post over again.

How do it get it to switch to using ID when it reaches Views_monthly = 0 and load fresh posts?

Answer 1

Instead of changing the WHERE clause, change the LIMIT offset:

SELECT id, title, category, icon_normal, status, description, views_monthly
FROM posts
WHERE status='1'
ORDER BY views_monthly DESC
LIMIT $offset, 9

The offset is the page number (0-based) multiplied by 9. The LIMIT clause is described in the documentation for SELECT.