philtune philtune - 6 months ago 13
PHP Question

Adding a table count to my listing of databases

I want to list all my custom databases:

SELECT `SCHEMA_NAME` AS 'Database',
`DEFAULT_CHARACTER_SET_NAME` AS 'Character Set',
`DEFAULT_COLLATION_NAME` AS 'Collation'
FROM `information_schema`.`SCHEMATA`
WHERE `SCHEMA_NAME` NOT IN ('information_schema','mysql','performance_schema')
ORDER BY `SCHEMA_NAME` ASC


But I want to include the number of tables in each database:

SELECT `TABLE_SCHEMA`, COUNT(*)
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` NOT IN ('information_schema','mysql','performance_schema')
GROUP BY `TABLE_SCHEMA`


...in a single table:

[
0 => [ 'Database'=>'customDb1', ..., 'Table Count'=>3 ],
1 => [ 'Database'=>'customDb2', ..., 'Table Count'=>8 ],
2 => [ 'Database'=>'customDb3', ..., 'Table Count'=>0 ]
]


I know that running the first query, looping through each database in PHP, and running the second query is much less efficient than using something like a table
JOIN
, but I'm just not grasping the
JOIN
syntax. The Lynda tutorial I'm taking just steams right through this rather (potentially) complex part of the language, and everything I try just throws errors or bad results.

I'm sure this is a common query for developers creating MySQL database interfaces. How do I add a table count to my listing of databases using only MySQL queries?

Answer

Try this:

SELECT s.`SCHEMA_NAME` AS 'Database',
       s.`DEFAULT_CHARACTER_SET_NAME` AS 'Character Set',
       s.`DEFAULT_COLLATION_NAME` AS 'Collation',
       COUNT(*) as table_cnt
FROM `information_schema`.`SCHEMATA` s
INNER JOIN `information_schema`.`TABLES` t
 ON(s.`SCHEMA_NAME` = t.`TABLE_SCHEMA`)
WHERE s.`SCHEMA_NAME` NOT IN ('information_schema','mysql','performance_schema')
GROUP BY s.`SCHEMA_NAME`
ORDER BY s.`SCHEMA_NAME` ASC
Comments