Show all tables inside a MySQL database using PHP?

Question

I'm trying to show all the tables in my database. I've tried this:

$sql = "SHOW TABLES"; $result = $conn->query($sql); $tables = $result->fetch_assoc(); foreach($tables as $tmp) {     echo "$tmp <br>"; } 

but it only gives me one table name in a database I know has 2. What am I doing wrong?

Answer 1

How to get tables

1. SHOW TABLES

mysql> USE test; Database changed mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1             | | t2             | | t3             | +----------------+ 3 rows in set (0.00 sec) 

2. SHOW TABLES IN db_name

mysql> SHOW TABLES IN another_db; +----------------------+ | Tables_in_another_db | +----------------------+ | t3                   | | t4                   | | t5                   | +----------------------+ 3 rows in set (0.00 sec) 

3. Using information schema

mysql> SELECT TABLE_NAME        FROM information_schema.TABLES        WHERE TABLE_SCHEMA = 'another_db'; +------------+ | TABLE_NAME | +------------+ | t3         | | t4         | | t5         | +------------+ 3 rows in set (0.02 sec) 

to OP

you have fetched just 1 row. fix like this:

while ( $tables = $result->fetch_array()) {     echo $tmp[0]."<br>"; } 

and I think, information_schema would be better than SHOW TABLES

SELECT TABLE_NAME FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'your database name'  while ( $tables = $result->fetch_assoc()) {     echo $tables['TABLE_NAME']."<br>"; } 

Answer 2

Try this:

SHOW TABLES FROM nameOfDatabase;