Menu
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.
286
The limit keyword is used to limit the number of rows returned in a query result. “SELECT {fieldname(s) | *} FROM tableName(s)” is the SELECT statement containing the fields that we would like to return in our query. “[WHERE condition]” is optional but when supplied, can be used to specify a filter on the result set.
The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value. TIP: SELECT LIMIT is not supported in all SQL databases. For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results.
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;
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...
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
