How to limit SHOW TABLES query

php mysql pagination information-schema
Jaxkr picture Jaxkr · Jul 24, 2012 · Viewed 15.4k times · Source

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.

Answer

Mike Mackintosh picture Mike Mackintosh · Jul 24, 2012

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;

Related questions

How can I select rows in MySQL starting at a given row number?

Say I have 50 rows in a MySQL table. I want to select the first ten (LIMIT 10), but then I want to be able to select the next 10 on a different page. So how do I start my selection, after row 10? …

Read More
Limit pagination page number

$objConnect = mysql_connect("localhost","root","") or die(mysql_error()); $objDB = mysql_select_db("Test"); $strSQL = "SELECT * FROM UserAddedRecord WHERE (Name LIKE '%".$getname."%' and State LIKE '%".$getstate."%' and Cell LIKE '%".$getcell."%' and Custgroup LIKE '%".$…

Read More
Laravel join queries AS

Any way of defining an AS for a query?? I have tried the following: $data = News::order_by('news.id', 'desc') ->join('categories', 'news.category_id', '=', 'categories.id') ->left_join('users', 'news.user_id', …

Read More