How to limit SHOW TABLES query
information-schema, mysql, pagination, php
Solution
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;
Problem
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.