LinuxQuestions.org - Finding What Tables Exist In A MySQL Database

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Finding What Tables Exist In A MySQL Database (https://www.linuxquestions.org/questions/programming-9/finding-what-tables-exist-in-a-mysql-database-394346/)

taylor_venable

12-18-2005 10:25 PM

Finding What Tables Exist In A MySQL Database

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 :p

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

Oracle

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.

All times are GMT -5. The time now is 09:00 AM.