Check if a database table exists using php/pdo

Question

Hi,

I want to check if a table with a specific name exists in a database i've connected to using php and pdo. It has to work on all database backends ... MySQL, SQLite, ...

best regards, andre

Answer 1

Check out this post.

I misread, that will only work with MySQL, apologies

Answer 2

Do:

select 1 from your_table

and then catch the error. If you don't get any error, but resultset with one column containing "1", then the table exists.

Answer 3

As part of your project, create a schema view.

For Oracle it would be something like

SELECT TABLE_NAME FROM ALL_TABLES

For Mysql:

SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mydbname'

ETC..

And then run a query in your code against the view.

Answer 4

You could do a "select count(*) from table" query from php. If it returns an error or exception, the table doesn't exist. This could be the last possible resort and I am sure this works.

Or, you could check the schema table directly (probably requires additional permissions to be set by admin)

Answer 5

I do a few things in my web apps with CodeIgniter to check that the database exists (and is useful), any of these can work:

@$this->load->database();
$v = @$this->db->version()
$tables = @$this->db->list_tables();

Adding the @ will suppress errors if you have them enabled in your PHP setup, and checking the results of version() and list_tables() can be used to not only determine if your DB is around (but that it's sane too).