Check if a table exists in the database

Asked

Viewed 12,113 times

22

How to check if a table exists in the database before trying to use it?

<?php
// define a tabela
$table = 'banana';

// verificar se tabela "banana" existe aqui
// $tableExists = ??

// executa consulta se existir
if ($tableExists) {
  $query = 'SELECT * FROM '.$table.' WHERE 1';
  // executa a consulta, etc...
} else {
  die ('A tabela '.$table.' não foi encontrada na base de dados!');
}
?>
php mysql sql-table

3 answers

21


Based in this OS response, the simplest way is:

$table = 'banana';
$tableExists = $db->query("SHOW TABLES LIKE '$table'")->rowCount() > 0;

Alternatively, if someone does not want to use PDO:

$table = 'banana';
$result = mysqli_query("SHOW TABLES LIKE '$table'");
$tableExists = $result && $result->num_rows > 0;

  • The person who gave the downvote could explain why? Do you have any performance problems or anything like?

  • I did not downvote the 'problem' of the code is that the mysql_* functions have already been deprecated. http://br1.php.net/manual/en/function.mysql-connect.php and has a question here: http://answall.com/questions/579/porque-nao-devemos-usar-funcoes-do-tipo-mysql

  • Really, @lost is right...today the best is to use PDO...

  • @utluiz Não dei downvote, but in the link you provided, in the comments has the version PDO, update your example and if it was from downvote, you’re already safe :)

  • Thanks for the personal comment, it is better to leave the example more suitable even. I was more concerned with the content of query than with how to use in PHP and let it pass.

  • 1

    @utluiz you could update your answer using mysqli? Thank you.

Show 1 more comment

8

For information_schema mysql is possible to access all tables in a database:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'data_base'
AND table_name = 'nome_da_tabela'

After query, use the bank driver function/method that resumes the amount of bank records:

Count() (generically)

rowCount()(PDO)

mysqli_num_rows()(mysqli)

$db = new PDO('mysql:host=localhost dbname=test','usuario', 'senha');
$sql = 'SELECT table_name FROM information_schema.tables WHERE table_schema = :database
AND table_name = :tabela';

$stmt = $db->prepare($sql);
$stmt->bindValue(':database', 'test';
$stmt->bindValue(':tabele', 'bananas';
$stmt->execute();
$tabelas = $stmt->rowCount();

if($tabelas >= 1 ){
  echo 'tabela valida';
  //efetuar select
}else{
  echo 'tabela invalida';
  //efetuar um create table?
}

about the information_schema

7

Create a function so you can search dynamically: 

public function verificaTabela($tabela)
{
    $tabelas_consulta = mysql_query('SHOW TABLES');

    while ($tabelas_linha = mysql_fetch_row($tabelas_consulta))
    {
        $tabelas[] = $tabelas_linha[0];
    }

    if (!in_array($tabela, $tabelas)) 
    {
        return false;
    }
    else
    {
        return true;
    }
}

$tabela_existe = verificaTabela('minha_tabela');

Browser other questions tagged php mysql sql-table

You are not signed in. Login or sign up in order to post.