MySQL check if a table exists without throwing an exception

Question

What is the best way to check if a table exists in MySQL  preferably via PDO in PHP  without throwing an exception  I do not feel like parsing the results of  SHOW TABLES LIKE  et cetera  There must be some sort of boolean query

User · Answer

This is posted simply if anyone comes looking for this question. Even though its been answered a bit. Some of the replies make it more complex than it needed to be.

For mysql* I used :

if (mysqli_num_rows(
    mysqli_query(
                    $con,"SHOW TABLES LIKE '" . $table . "'")
                ) > 0
        or die ("No table set")
    ){

In PDO I used:

if ($con->query(
                   "SHOW TABLES LIKE '" . $table . "'"
               )->rowCount() > 0
        or die("No table set")
   ){

With this I just push the else condition into or. And for my needs I only simply need die. Though you can set or to other things. Some might prefer the if/ else if/else. Which is then to remove or and then supply if/else if/else.

User · Answer

Here is the my solution that I prefer when using stored procedures  Custom mysql function for check the table exists in current database   delimiter     CREATE FUNCTION TABLE EXISTS  table name VARCHAR 45   RETURNS BOOLEAN DETERMINISTIC READS SQL DATA BEGIN     DECLARE  exists  TINYINT 1  DEFAULT 0       SELECT COUNT    INTO  exists     FROM information schema tables      WHERE table schema    DATABASE       AND table name     table name       RETURN  exists   END    SELECT TABLE EXISTS  you table name   as  exists

User · Answer

If the reason for wanting to do this is is conditional table creation  then  CREATE TABLE IF NOT EXISTS   seems ideal for the job  Until I discovered this  I used the  DESCRIBE  method above  More info here  MySQL  quot CREATE TABLE IF NOT EXISTS quot  - gt  Error 1050

User · Answer

If you re using MySQL 5 0 and later  you could try   SELECT COUNT    FROM information schema tables  WHERE table schema     database name    AND table name     table name      Any results indicate the table exists   From  http   www electrictoolbox com check-if-mysql-table-exists

User · Answer

Why you make it so hard to understand   function table exist  table         pTableExist   mysql query  show tables like     table           if   rTableExist   mysql fetch array  pTableExist             return  Yes        else          return  No

User · Answer

As a  Show tables  might be slow on larger databases  I recommend using  DESCRIBE   and check if you get true false as a result   tableExists   mysqli query  DESCRIBE  myTable

User · Answer

Zend framework   public function verifyTablesExists  tablesName                 db    this- gt getDefaultAdapter             config db    db- gt getConfig              sql    SELECT COUNT    FROM information schema tables WHERE table schema      config db  dbname      AND table name      tablesName               result    db- gt fetchRow  sql           return  result

User · Answer

q    SHOW TABLES    res   mysql query  q   con   if   res  while    row   mysql fetch array  res  MYSQL ASSOC          foreach   row as  key   gt   value                 if    value   BTABLE       BTABLE IS A DEFINED NAME OF TABLE             echo  exist           else             echo  not exist

User · Answer

Using mysqli i ve created following function  Asuming you have an mysqli instance called  con   function table exist  table       global  con       table    con- gt real escape string  table        sql    show tables like     table           res    con- gt query  sql       return   res- gt num rows  gt  0       Hope it helps   Warning  as sugested by  jcaron this function could be vulnerable to sqlinjection attacs  so make sure your  table var is clean or even better use parameterised queries

User · Answer

I don t know the PDO syntax for it  but this seems pretty straight-forward    result   mysql query  SHOW TABLES LIKE  myTable      tableExists   mysql num rows  result   gt  0

[php] MySQL check if a table exists without throwing an exception

Examples related to php

Examples related to mysql

Examples related to pdo

Examples related to database