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.
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.
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.
Thanks.
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)
select ..... where .... order ... LIMIT 30,10
EDI:
the found_rows will return from the beginning of the table to your limit,
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