Check if table was newly created or not in database

Question

I have a command to create table in the database. I want to know whether a new table is created or not. Below given is my command.

 $command = $connection->createCommand(
     "
        CREATE TABLE IF NOT EXISTS `".$tbName."` LIKE `question`;
        INSERT INTO `".$tbName."` SELECT * FROM `question`;

     "
    )->execute();
    echo $command;
   if($command!==0){
    echo "Success";}
   else echo "Table already created";

But this always prints "Table already created" even if it is not there in the database.

why u can't check tbl existence with another qry, just like select 1 from tblname; it ll give result with true or false... — user1844933, Feb 08 '14 at 08:14
That will always give me true na?I want to check that if it is newly created or not. I want to write a one time action there. — Dency G B, Feb 08 '14 at 08:17

score 1 · Accepted Answer · edited May 23 '17 at 11:56

CDbCommand::execute() returns the number of rows affected by the sql statement. Since creating a table doesn't create any rows the result will always be 0. The same goes for CDbCommand::createTable(). Therefore as user1844933 mentioned, you should use another query to check whether the table exists:

$result = $connection->createCommand("SHOW TABLES LIKE :table_name")->execute(array('table_name'=>$table_name));
$tableExists = $result > 0;

Code from https://stackoverflow.com/a/1525801/428543

score 0 · Answer 2 · answered Feb 08 '14 at 08:16

0

I think you're supposed to use CdbCommand::createTable for this. See: http://www.yiiframework.com/doc/api/1.1/CDbCommand#createTable-detail

answered Feb 08 '14 at 08:16

Ruben

5,043
2
25
49

score 0 · Answer 3 · answered Feb 08 '14 at 08:22

There are severals way to check if table exist, so :

if(mysql_num_rows(mysql_query("SHOW TABLES LIKE 'table_name'"))==1) {
  echo "Table exists";
}

Another way is to make select from table:

$value = mysql_query('select 1 from `table_name`')

if($value !== FALSE)
{
   echo "Table exists";
}

Check if table was newly created or not in database

3 Answers3