Finding What Tables Exist In A MySQL Database

taylor_venable · 12-18-2005, 10:25 PM

Hello; I have a LAMP server and I'm trying to write a PHP script to help keep track of some information on a database. I want the web interface to present to the user the option of what table to draw queries from. The database will be constant, but the table will be chosen by the user. For example, if we're using database "languages" and tables "c++", "lisp", and "ruby" exist in it, I want to be able to tell the user that they can choose from either "c++", "lisp", or "ruby". In other words, I need to discover from the database what tables exist inside it. Is there a way to do this with a PHP function or a MySQL command? Thanks.

paulsm4 · 12-18-2005, 10:36 PM

From the mysql interpreter, you'd do something like:

show databases;
use mydb;
show tables;
describe mytable;

I'm sure you can do the equivalent from PHP...

For example:

http://dev.mysql.com/doc/refman/5.0/en/show.html

Code:

  mysql_select_db("foobar");
  $query = "show tables";
  $result = mysql_query($query);
  $num_results = mysql_num_rows($result);
  print "There are $num_results tables.<br>";
  for ($i = 0; $i < $num_results; $i++)
  {
    $row = mysql_fetch_array($result);
    print "table " . $row[0] . " exists.<br>";
  }

PS:
Nice web site!

Pauli · 12-19-2005, 10:29 AM

Couldn't you just query it?

Select Table_Name from User_Tables;

EDIT: Sorry if this is wrong, I'm used to Oracle not MySQL.

taylor_venable · 12-19-2005, 10:53 AM

Ah, the SHOW command; somehow that one slipped past me.

Thanks, Paul!

Hko · 12-19-2005, 11:00 AM

Quote:

Originally Posted by Pauli

Couldn't you just query it?

Select Table_Name from User_Tables;

You cannot use "Select" to get table names. But on the other hand, "SHOW TABLES" is a (limited) query in MySQL. And you can use "FROM database_name" and "LIKE 'col%'" with "SHOW TABLES".

Pauli · 12-19-2005, 11:08 AM

Oh well. It works in oracle

Hko · 12-19-2005, 11:24 AM

I'll take your word for it

Unfortunately these kind of things are apperently not part of the SQL-standaard.

taylor_venable · 12-19-2005, 09:52 PM

It looks like "SELECT Table_Name FROM User_Tables" may get the metadata regarding what tables are present directly from the database. Since this is part of the idea behind Relational Databases (that they self-contain their metadata in the same manner that they would any other data), that would make perfect sense. I wonder if you couldn't do the same thing in MySQL, assuming you knew what hidden system-table stored metadata about the rest of the tables in that particular database.