mysql result for pagination

Question

The query is:

SELECT * FROM `news` ORDER BY `id` LIMIT ($curr_page * 5), ( ($curr_page * 5) + 5 )

Where $curr_page is a php variable which is getting a value from $_GET['page']

I want to make a pagination (5 news on each page), but I don't know why the mysql is returning me extra values.

On the first page the result ok: $curr_page = 0

The query would be:

SELECT * FROM `news` ORDER BY `id` LIMIT 0, 5

But on the second page, the result from the query is adding extra news, 10 instead of 5.

The query on the second page:

SELECT * FROM `news` ORDER BY `id` LIMIT 5, 10

Whats wrong? Why the result has 10 values instead of 5?

Thank you!

Answer 1

The LIMIT statement's syntax is:

LIMIT [START_ROW], [COUNT]

So, your code should be:

SELECT * FROM `news` ORDER BY `id` LIMIT ($curr_page * 5), 5

Wanted to add, not knowing the structure of your table: usually "news" is sorted by date posted rather than id. If you have a publish date field in your table, it makes for a much more appropriate sort column than the id, which is meaningless to the user. Assuming the id is auto-increment, the order you enter articles may not always reflect the formal date of publication.

Documentation

• MySQL's SELECT syntax - http://dev.mysql.com/doc/refman/5.0/en/select.html

Answer 2

look this example, the first parameter is the row to begin and the second is the number of result to display

mysql> select * From t1 limit 0,5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

mysql> select * From t1 limit 5,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        6 | BETTE      | NICHOLSON | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL    | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK     | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)

mysql> select * From t1 limit 10,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       11 | ZERO       | CAGE      | 2006-02-15 04:34:33 |
|       12 | KARL       | BERRY     | 2006-02-15 04:34:33 |
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|       14 | VIVIEN     | BERGEN    | 2006-02-15 04:34:33 |
|       15 | CUBA       | OLIVIER   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)

in your case the second parameter is static and the first parameter ($curr_page * 5)