How can I check if a MySQL table exists with PHP

Question

As simple in theory as it sounds I ve done a fair amount of research and am having trouble figuring this out   How can I check if a MySQL table exists and if it does do something   I guess a simple php if else statement could work for this   Is there a way to do this   This is what I have done with cwallenpoole s response   mysql connect  SERVER   USERNAME   PASSWORD    mysql select db  DATABASE      val   mysql query  select 1 from  TABLE      if  val     FALSE       print  Exists     else     print  Doesn t exist

User · Answer

lt  php   connection   mysqli connect  localhost   root      php sample login register      if   connection           echo  DB is Connected  lt br gt             sql    CREATE TABLE user              id INT 6  UNSIGNED AUTO INCREMENT PRIMARY KEY               name VARCHAR 255 NOT NULL              email VARCHAR 255 NOT NULL              password VARCHAR 255  NOT NULL                          if mysqli query  connection  sql                 echo  Created user table             else              echo  User table already exists                   else           echo  error   DB isnot connected           gt

User · Answer

The cleanest way to achieve this in PHP is to simply use DESCRIBE statement   if   mysql query   DESCRIBE  my table                my table exists     I m not sure why others are posting complicated queries for a such a straight forward problem   Update  Using PDO     assuming you have already setup  pdo  sh    pdo- gt prepare   DESCRIBE  my table     if    sh- gt execute              my table exists   else          my table does not exist

User · Answer

mysql query  SHOW TABLES FROM yourDB       gt  loop thru results and see if it exists    gt  in this way with only one query one can check easly more table    or mysql query  SHOW TABLES LIKE  tblname      Don t use mysql list tables    because it s deprecated

User · Answer

125 microsecond table exists check   000125 sec   125  s   mysql unbuffered query  SET profiling   1        for profiling only   mysql unbuffered query  SELECT 1 FROM  table  LIMIT 1     if  mysql errno      1146        NO EXISTING TABLE CODE GOES HERE    elseif mysql errno    gt  0      echo mysql error             results   mysql query  SHOW PROFILE        for profiling only   Execution time  measured using mysql profiling  when a table exists  or not  is about  000125 sec   125  s    The LIMIT 1 is important for speed   This minimizes the Sorting Result and Sending Data query State times  And table size is not a factor    I always use an unbuffered query when no results are required    PROFILE RESULTS  WHEN TABLE DOES NOT EXIST  QUERY STATE           SECONDS    --------------------  ------- starting              0 000025   checking permissions  0 000006   Opening tables        0 000065   query end             0 000005   closing tables        0 000003   freeing items         0 000013   logging slow query    0 000003   cleaning up           0 000003   TOTAL                 0 000123   lt  lt  lt  lt  lt  lt  lt  lt  lt  lt  lt  lt  123 microseconds   WHEN TABLE EXISTS    QUERY STATE           SECONDS    --------------------  ------- starting              0 000024 checking permissions  0 000005 Opening tables        0 000013 System lock           0 000007 init                  0 000006 optimizing            0 000003 statistics            0 000009 preparing             0 000008 executing             0 000003 Sending data          0 000019 end                   0 000004 query end             0 000004 closing tables        0 000006 freeing items         0 00001 logging slow query    0 000003 cleaning up           0 000003 TOTAL                 0 000127  lt  lt  lt  lt  lt  lt  lt  lt  lt  lt  lt  lt  127 microseconds

User · Answer

You can use many different queries to check if a table exists  Below is a comparison between several   mysql query  select 1 from  table name  group by 1    or   mysql query  select count    from  table name      mysql query  DESCRIBE  table name       70000   rows  24ms   1000000 rows  24ms   5000000 rows  24ms  mysql query  select 1 from  table name       70000   rows  19ms   1000000 rows  23ms   5000000 rows  29ms  mysql query  select 1 from  table name  group by 1    or   mysql query  select count    from  table name       70000   rows  18ms   1000000 rows  18ms   5000000 rows  18ms     These benchmarks are only averages

User · Answer

Or you could use   show tables where Tables in  insert db name   tablename

User · Answer

res   mysql query  SELECT table name FROM information schema tables WHERE table schema     databasename  AND table name     tablename        If no records are returned then it doesn t exist

User · Answer

Select 1 from table name will return false if the table does not exist   val   mysql query  select 1 from  table name  LIMIT 1     if  val     FALSE         DO SOMETHING  IT EXISTS    else         I can t find it        Admittedly  it is more Pythonic than of the PHP idiom  but on the other hand  you don t have to worry about dealing with a copious amount of extra data   Edit  So  this answer has been marked down at least twice as of the time I am writing this message  Assuming that I had made some gargantuan error  I went and I ran some benchmarks  and this is what I found that my solution is over 10  faster than the nearest alternative when the table does not exist  and it over 25  faster when the table does exist                            BEGINNING NON-EXISTING TABLE                               23 35501408577 for bad select 25 408507823944 for select from schema num rows -- calls mysql num rows on select    from information schema  25 336688995361 for select from schema fetch row -- calls mysql fetch row on select    from information schema result 50 669058799744 for SHOW TABLES FROM test                          BEGINNING EXISTING TABLE                               15 293519973755 for good select 20 784908056259 for select from schema num rows 21 038464069366 for select from schema fetch row 50 400309085846 for SHOW TABLES FROM test   I tried running this against DESC  but I had a timeout after 276 seconds  24 seconds for my answer  276 to fail to complete the description of a non existing table    For good measure  I am benchmarking against a schema with only four tables in it and this is an almost fresh MySQL install  this is the only database so far   To see the export  look here   AND FURTHERMORE  This particular solution is also more database independent as the same query will work in PgSQL and Oracle   FINALLY  mysql query   returns FALSE for errors that aren t  this table doesn t exist    If you need to guarantee that the table doesn t exist  use mysql errno   to get the error code and compare it to the relevant MySQL errors

User · Answer

It was already posted but here it is with PDO  same query       connection   new PDO    mysql host host db  dbname name db   user db  pass db     if   connection- gt query   DESCRIBE table name            echo  exist     else       echo  doesn t exist       Works like a charm for me      And here s another approach  i think it is slower      if   connection- gt query    SELECT   FROM INFORMATION SCHEMA TABLES WHERE table schema    db name  AND table name   tbl name    - gt fetch           echo  exist     else       echo  doesn t exist       You can also play with this query   SHOW TABLE STATUS FROM db name LIKE  tbl name    I think this was suggested to use in the mysql page

User · Answer

SHOW TABLES LIKE  TableName   If you have ANY results  the table exists   To use this approach in PDO    pdo           new  PDO            result         pdo- gt query  SHOW TABLES LIKE  tableName      tableExists    result     false  amp  amp   result- gt rowCount    gt  0    To use this approach with DEPRECATED mysql query   result        mysql query  SHOW TABLES LIKE  tableName      tableExists   mysql num rows  result   gt  0

User · Answer

Even faster than a bad query   SELECT count  1   as  ct   FROM INFORMATION SCHEMA TABLES where table schema   yourdatabasename  and table name  yourtablename     This way you can just retrieve one field and one value    016 seconds for my slower system

User · Answer

DO NOT USE MYSQL ANY MORE  If you must use mysqli but PDO is best    pdo   new PDO  dsn   username   pdo      proper PDO init string here if   pdo- gt query  SELECT table name FROM INFORMATION SCHEMA TABLES WHERE table schema    db name   - gt fetch       table exists

[php] How can I check if a MySQL table exists with PHP?

Examples related to php

Examples related to mysql

Examples related to if-statement