mySQL database query LIMIT help... How to create pages if number of rows returned exceeds 'x'?

Question

I'm attempting to write a script that returns rows from a mySQL database. Basically, the users on my site receive "experience points" and go up "ranks" based on the number of experience points they get.

I have a page called "recentexp.php" where all recent exp is shown in a table, with the most recent at the top.

How do I write a code that creates more pages the more results are returned?

If there are less than 10 rows, then have no page numbers, and only show page 1. However, if there are 12 rows returned, then have the 10 most recent (with the MOST recent at the top) on my page, and then have PHP create a page "2" where the 2 oldest rows are placed.

If I explained this badly, I apologize. I'm exhausted.. long day.

Thanks!

Answer 1

You can use LIMIT x, y to show the y results after x.

Let's say you have a variable $page that is passed through $_GET or through some other means. $page defaults to 1, or is a numerical value that denotes the page number.

You can then select the rows of that page via:

SELECT * FROM `table` WHERE `condition` = ? LIMIT ($page - 1) * 10, 10

You can replace 10 with how many results per page. As you can see, if the page number is 1, this query is:

SELECT * FROM `table` WHERE `condition` = ? LIMIT 0, 10

Which displays the first 10 results. If the page number is 2, this query is:

SELECT * FROM `table` WHERE `condition` = ? LIMIT 10, 10

Which displays the ten rows after the 10th row, i.e., rows 11-20 (which should be on page 2!)