查询为:
SELECT * FROM `news` ORDER BY `id` LIMIT ($curr_page * 5), ( ($curr_page * 5) + 5 )
其中$curr_page
是从$_GET['page']
获取值的php变量
我想进行分页(每页5条新闻),但我不知道为什么mysql会给我返回额外的值。
在第一页上,结果ok:$curr_page=0
查询将是:
SELECT * FROM `news` ORDER BY `id` LIMIT 0, 5
但在第二页,查询的结果是添加了额外的新闻,10条而不是5条。
第二页的查询:
SELECT * FROM `news` ORDER BY `id` LIMIT 5, 10
怎么了?为什么结果有10个值而不是5个?
谢谢!
LIMIT[START_ROW],[COUNT]
所以,你的代码应该是:
SELECT * FROM `news` ORDER BY `id` LIMIT ($curr_page * 5), 5
想添加,但不知道表的结构:通常"新闻"是按发布日期而不是id排序的。如果表中有发布日期字段,它会产生比id更合适的排序列,这对用户来说毫无意义。假设id是自动递增的,那么您输入文章的顺序可能并不总是反映正式发布日期。
文档
- MySQL的SELECT语法-http://dev.mysql.com/doc/refman/5.0/en/select.html
看看这个例子,第一个参数是要开始的行,第二个参数是显示的结果数
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)
在您的情况下,第二个参数是静态的,第一个参数($curr_page * 5)