I am trying to add pagination to my results which query a FULL-TEXT indexed table. Here is the query:
我正在尝试向查询全文索引表的结果添加分页。在这里查询:
Normal Query
正常查询
SELECT *,MATCH(title) AGAINST ("+samsung +galaxy +s3" IN BOOLEAN MODE)
as score FROM `deals` WHERE `image`!='' AND category=15032 ORDER BY score DESC;
It returns 183 rows.
它返回183行。
Trying to do pagination
想做的分页
SELECT SQL_CALC_FOUND_ROWS *,MATCH(title) AGAINST
("+samsung +galaxy +s3" IN BOOLEAN MODE) as score FROM `deals`
WHERE `image`!='' AND category=15032 ORDER BY score DESC LIMIT 8;
Return 8 rows as intended as i want to show 8 items per page.
返回8行,就像我想要显示每页8项一样。
and then
然后
SELECT FOUND_ROWS();
It returns 20
items as opposed to 183
as was coming in the original query. I am not sure as to what may be causing this. Can you please help me with this.
它返回20个条目,而不是原始查询中的183个。我不确定是什么引起的。你能帮我一下吗?
Thanks.
谢谢。
2 个解决方案
#1
0
You are doing it wrong, to do the pagination you need two limits, the begin and the end, look this example and add the limit correctly to your code
您做错了,要进行分页,您需要两个限制:开始和结束,请查看这个示例并将限制正确地添加到代码中
mysql> select * from t1 order by actor_id;
+----------+-------------+--------------+---------------------+
| 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 |
| 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 |
| 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 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
| 17 | HELEN | VOIGHT | 2006-02-15 04:34:33 |
| 18 | DAN | TORN | 2006-02-15 04:34:33 |
| 19 | BOB | FAWCETT | 2006-02-15 04:34:33 |
| 20 | LUCILLE | TRACY | 2006-02-15 04:34:33 |
....
+----------+-------------+--------------+---------------------+
201 rows in set (0.00 sec)
now you apply the limits, begin and end
现在你应用极限,开始和结束
mysql> select * from t1 order by actor_id 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 order by actor_id limit 6,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 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 |
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from t1 order by actor_id limit 11,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 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 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)
so the Idea is you use LIMIT $begin,$number_results
, and when you click next it will take the number_result + total_result_per_page and put it in the $begin. Lets say that you are in page 3, and you show 10 results per page.($begin=10+10+10=30)
所以你的想法是使用LIMIT $begin,$number_results,当你点击next时它会取number_result + total_result_per_page并把它放到$begin中。假设你在第3页,每页显示10个结果($begin=10+10+10=30)
select ..... where .... order ... LIMIT 30,10
选择.....在....订单……限制30日10
EDI:
艾迪:
the found_rows will return from the beginning of the table to your limit,
found_rows将从表的开头返回到您的极限,
mysql> select * from t2 order by actor_id limit 10;
+----------+------------+--------------+---------------------+
| 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 |
| 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 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
mysql> select * from t2 order by actor_id limit 2,10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 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 |
| 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 |
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
| 12 | KARL | BERRY | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 12 |
+--------------+
1 row in set (0.00 sec)
It's 12 because 10 result displayed + the id 1 and 2 + 10 displayed results
它是12,因为显示了10个结果+ id 1和2 + 10个结果
#2
0
Try to use HAVING
instead of WHERE
尽量使用“有”而不是“在哪里”
SELECT SQL_CALC_FOUND_ROWS
*,
MATCH(title) AGAINST ("+samsung +galaxy +s3" IN BOOLEAN MODE) as score
FROM `deals`
HAVING`image`!=''
AND category=15032
ORDER BY score DESC
LIMIT 8;
#1
0
You are doing it wrong, to do the pagination you need two limits, the begin and the end, look this example and add the limit correctly to your code
您做错了,要进行分页,您需要两个限制:开始和结束,请查看这个示例并将限制正确地添加到代码中
mysql> select * from t1 order by actor_id;
+----------+-------------+--------------+---------------------+
| 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 |
| 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 |
| 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 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
| 17 | HELEN | VOIGHT | 2006-02-15 04:34:33 |
| 18 | DAN | TORN | 2006-02-15 04:34:33 |
| 19 | BOB | FAWCETT | 2006-02-15 04:34:33 |
| 20 | LUCILLE | TRACY | 2006-02-15 04:34:33 |
....
+----------+-------------+--------------+---------------------+
201 rows in set (0.00 sec)
now you apply the limits, begin and end
现在你应用极限,开始和结束
mysql> select * from t1 order by actor_id 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 order by actor_id limit 6,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 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 |
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from t1 order by actor_id limit 11,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 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 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)
so the Idea is you use LIMIT $begin,$number_results
, and when you click next it will take the number_result + total_result_per_page and put it in the $begin. Lets say that you are in page 3, and you show 10 results per page.($begin=10+10+10=30)
所以你的想法是使用LIMIT $begin,$number_results,当你点击next时它会取number_result + total_result_per_page并把它放到$begin中。假设你在第3页,每页显示10个结果($begin=10+10+10=30)
select ..... where .... order ... LIMIT 30,10
选择.....在....订单……限制30日10
EDI:
艾迪:
the found_rows will return from the beginning of the table to your limit,
found_rows将从表的开头返回到您的极限,
mysql> select * from t2 order by actor_id limit 10;
+----------+------------+--------------+---------------------+
| 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 |
| 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 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
mysql> select * from t2 order by actor_id limit 2,10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 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 |
| 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 |
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
| 12 | KARL | BERRY | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 12 |
+--------------+
1 row in set (0.00 sec)
It's 12 because 10 result displayed + the id 1 and 2 + 10 displayed results
它是12,因为显示了10个结果+ id 1和2 + 10个结果
#2
0
Try to use HAVING
instead of WHERE
尽量使用“有”而不是“在哪里”
SELECT SQL_CALC_FOUND_ROWS
*,
MATCH(title) AGAINST ("+samsung +galaxy +s3" IN BOOLEAN MODE) as score
FROM `deals`
HAVING`image`!=''
AND category=15032
ORDER BY score DESC
LIMIT 8;