CODEWITHSUNDEEP
phpmysqlpaginationinformation-schema
Jaxkr
Jaxkr

Reputation: 1284

How to limit SHOW TABLES query

I have the following query:

SHOW TABLES LIKE '$prefix%'

It works exactly how I want it to, though I need pagination of the results. I tried:

SHOW TABLES LIKE '$prefix%' ORDER BY Comment ASC LIMIT 0, 6

I need it to return all the tables with a certain prefix and order them by their comment. I want to have pagination via the LIMIT with 6 results per page.

I'm clearly doing something very wrong. How can this be accomplished?

EDIT: I did look at this. It didn't work for me.

Upvotes: 15

Views: 20320

Answers (2)

Mike Mackintosh
Mike Mackintosh

Reputation: 14237

The above cannot be done via MySQL Syntax directly. MySQL does not support the LIMIT clause on certain SHOW statements. This is one of them. MySQL Reference Doc.

The below will work if your MySQL user has access to the INFORMATION_SCHEMA database.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_TO SEARCH_HERE' AND TABLE_NAME LIKE "table_here%"  LIMIT 0,5;

Upvotes: 17

Jer In Chicago
Jer In Chicago

Reputation: 828

Just select via a standard query instead of using SHOW TABLES. For example

select table_name from information_schema.tables

Then you can use things like ASC and LIMIT, etc...

Upvotes: 3

Related Questions