在具有不同条件的相同数据库上多选

Question

i have pagination and i should call database for two times!
it's meant i should call :

SELECT COUNT(users_id) FROM users

for get all of database rowCount
and in another database fetch i have :

SELECT * FROM users ORDER BY users_id ASC LIMIT 1, 2

for create pagination
is there anyway to create one database call and get the same results ?
i try this:

SELECT COUNT(users_id),* FROM users ORDER BY users_id ASC LIMIT 1, 2

but this, call just 2 (count users_id) and broke pagination :(

Thanks and sorry for my poor english.

Answer 1

You can get this with 2 Queries:

1) first get your result with LIMIT and OPTION SQL_CALC_FOUND_ROWS

2) get the num rows without LIMIT

SELECT SQL_CALC_FOUND_ROWS t.* FROM yourTable 1 LIMIT 2;
SELECT FOUND_ROWS(); 

sample

mysql> SELECT SQL_CALC_FOUND_ROWS t.* FROM yourTable t LIMIT 2;
+-----+------------+
| id  | toDate     |
+-----+------------+
| 111 | 2017-03-02 |
| 111 | 2017-03-20 |
+-----+------------+
2 rows in set (0,00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            4 |
+--------------+
1 row in set (0,00 sec)

mysql>