How to apply pagination to the result of SHOW TABLES query in PHP

Question

I would like to apply pagination to the results of SHOW TABLES FROM DATABASE_NAME.

Tried using the LIMIT keyword but an error is thrown.

By pagination I mean that the result(tables) returned by the query is to be displayed on multiple pages.

You can do it in your code, for the first page show from the first result to the number of results you want to show. For the second one, show from result #`( number of results per page * 1 )`, until `( number of results per page * 1 ) + results per page`, and so on, just replace the `1` with the number of the current page in a zero based count (first page is 0, the second 1, etc). — Yaniro, Mar 20 '12 at 07:44

score 32 · Answer 1 · answered Mar 03 '14 at 09:20

32

mysql> pager less;

mysql> show tables;

answered Mar 03 '14 at 09:20

ivke

That is awesome – Alexander Zhukov Feb 09 '18 at 11:17
exactly what I was looking for. Thanks a ton. – McDee Apr 03 '18 at 11:04
Note that `less` must work on your system's command line already. For people that don't have `less` installed on their system, `more` might work as well. Might even work on Windows, but `less` isn't installed by default in some linux distro, especially when troubleshooting in a docker container (or any type of container). – beauk Jan 05 '20 at 20:07

score 22 · Accepted Answer · answered Mar 20 '12 at 07:44

22

you can use the different query instead (an achieve the names of the tables) like:

    SELECT TABLE_NAME FROM information_schema.TABLES 
WHERE `TABLE_SCHEMA` = 'my_db_name' LIMIT 10

answered Mar 20 '12 at 07:44

Haim Evgi

One more thing .. Could you please modify the query to return one more column of "table no." ?? – Ashish Saxena Mar 20 '12 at 09:56
SET @rank= 0; SELECT @rank:=@rank+1 AS rank,TABLE_NAME FROM information_schema.TABLES WHERE `TABLE_SCHEMA` = 'my_db_name' LIMIT 10 – Haim Evgi Mar 20 '12 at 10:06
is this is what u want? row number – Haim Evgi Mar 20 '12 at 10:07
yup i want the row no. :D but the above query is returning rank as null for each table :( – Ashish Saxena Mar 20 '12 at 10:34
you need to run 2 queries, run the set rank before – Haim Evgi Mar 20 '12 at 11:16
not working .. still showing null rank .. executed both the queries one by one .. executing the queries on information_schema – Ashish Saxena Mar 20 '12 at 12:19
use it like this with join : http://stackoverflow.com/questions/3126972/mysql-row-number – Haim Evgi Mar 20 '12 at 13:10
1

You are a star! Thank you – kholofelo Maloma Aug 04 '16 at 07:37

score 0 · Answer 3 · answered May 29 '13 at 07:29

You can try following

1)

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'database_name' 
AND TABLE_NAME LIKE "a%" 
LIMIT 0,20;

2)

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'database_name'  
LIMIT 0,50;

3 Answers3