Determine if a table exists in a database using CodeIgniter

Question

I'm starting to write a simple Model class using CodeIgniter 2.1.0. All that I want it to do for now is create and delete the MySQL table it represents. Here's the code:

class Users_model extends CI_Model
{

    public function createTable()
    {
        if ($this->db->table_exists('users') == FALSE) {
            $query = "CREATE TABLE users(
                    id SERIAL PRIMARY KEY,
                    email  VARCHAR(52) NOT NULL check(email <> ''),
                    UNIQUE (email)
                    )ENGINE = InnoDB;";
            $this->db->query($query);
        }
    }

    public function deleteTable()
    {
        if ($this->db->table_exists('users')) {
            $query = "DROP TABLE users;";
            $this->db->query($query);
        } else {
            echo "users not found <br />";
            $tables = $this->db->list_tables();
            foreach ($tables as $table) {
                echo $table."<br />";
            }
        }
    }
}

And this is the code I'm executing in the main class:

$this->load->model('users_model');
$this->users_model->deleteTable();
$this->users_model->createTable(); 

The first run is fine and dandy. The tables are created just fine. But then, when I run it again, I get the output:

users not found
users

Upon further inspection I realize table_exists() never returns TRUE. var_dump($this->db->table_exists('users')); returns bool(false).

So, am I doing something wrong? This is as simple as it gets, copy pasted from the documentation. Google doesn't return anything related, and being a C programmer my mindset is always "If there's a bug, it's yours. (Richard Stallman is never wrong)"... But since Mr. Stallman has little to do with this, there's a possibility.

TL;DR

Why does table_exists('users') in the above code never returns TRUE even if list_tables() does return 'users';

Answer 1

Try using var_dump() instead of echo. Boolean values don't work well with echo.

var_dump( $this->db->table_exists('users') );

The source code of table_exists looks like this:

function table_exists($table_name)
{
    return ( ! in_array($this->_protect_identifiers($table_name, TRUE, FALSE, FALSE), $this->list_tables())) ? FALSE : TRUE;
}

Update from comments

If you have a look at _protect_identifiers it does add the database name to the table name. But there should be a . between db.table. Maybe your db config is messed up?

Answer 2

As a solution to your problem in codeigniter you can try this function

function validateTable($tableName)
    {
        $result = $this->db->list_tables();

        foreach( $result as $row ) {
            if( $row == $tableName )    return true;
        }
        return false;
    }