14

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.

Community
  • 1
  • 1
Jaxkr
  • 1,236
  • 2
  • 13
  • 33
  • Try http://stackoverflow.com/questions/9782948/how-to-apply-pagination-to-the-result-of-show-tables-query-in-php/16807952#16807952 – Mukesh May 29 '13 at 07:35

2 Answers2

16

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;
Mike Mackintosh
  • 13,917
  • 6
  • 60
  • 87
  • But how do I then filter if by prefix. The LIKE clause isn't working. – Jaxkr Jul 24 '12 at 17:12
  • 2
    `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'table%' LIMIT 0,5` works – Mike Mackintosh Jul 24 '12 at 17:13
  • Thanks you. But that's returning results from databases that start with that prefix. Not tables that do. – Jaxkr Jul 24 '12 at 17:15
  • 1
    I apologize. Try this: `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_HERE' AND TABLE_NAME LIKE "table_here%" LIMIT 0,5;` – Mike Mackintosh Jul 24 '12 at 17:19
3

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...

Jer In Chicago
  • 828
  • 5
  • 7