Create Database and Table in CodeIgniter (IF NOT EXISTS)

CodeIgniter create database if not exists: To create database & table in code igniter you have to use the Database Forge class. This library helps you to manage all sorts of database activities like create, drop, modify database/tables. You can use this class to migrate database if you prefer to do it manually.

Here we'll see how to create database and tables in codeigniter programmatically using the Forge Class.

Initializing the Forge Class:

First you have to initialize the Forge class and then access its methods using the handle $this->dbforge. The class depends on database driver so make sure it is up and running.

$this->load->dbforge();

Create Database in CodeIgniter:

To create database in codeigniter use the function create_database() like this,

$this->dbforge->create_database('db_name');

// SQL String: CREATE DATABASE db_name;

The function returns TRUE/FALSE based on success or failure. So you can verify if the database is created or not like this,

// check if database is created
if ($this->dbforge->create_database('Library'))
{
 echo 'Database created successfully...';
}

CodeIgniter Create Database If Not Exists:

To include IF NOT EXISTS clause to 'CREATE DATABASE' command, pass the second parameter as 'TRUE' to the method.

$this->dbforge->create_database('Library', TRUE)

// SQL String: CREATE DATABASE IF NOT EXISTS Library;

Create Table in CodeIgniter:

Just like creating databases you can create tables using the function create_table(). Together you have to use the method add_field() to add fields to the table. If you want to add multiple columns/fields to the table then pass it as an associative array to add_field() function. The call to this function should be immediately followed by create_table() method.

Also note that to create table in different database other than the default, first you have to run a query to switch over DB and then create table.

// switch over to Library DB
$this->db->query('use Library');

// define table fields
$fields = array(
  'memid' => array(
    'type' => 'INT',
    'constraint' => 9,
    'unsigned' => TRUE,
    'auto_increment' => TRUE
  ),
  'firstname' => array(
    'type' => 'VARCHAR',
    'constraint' => 30
  ),
  'lastname' => array(
    'type' => 'VARCHAR',
    'constraint' => 30
  ),
  'email' => array(
    'type' => 'VARCHAR',
    'constraint' => 60,
    'unique' => TRUE
  ),
  'password' => array(
    'type' => 'VARCHAR',
    'constraint' => 40
  )
 );

$this->dbforge->add_field($fields);

// define primary key
$this->dbforge->add_key('memid', TRUE);

// create table
$this->dbforge->create_table('Members');

The method add_key('memid', TRUE) adds primary key to the 'memid' field. By passing the second parameter as 'TRUE' will create primary key constraint.

Create Table If Not Exists:

Just like database, you can add IF NOT EXISTS clause while creating table. This will create a table if there is no other table with the same name.

$this->dbforge->create_table('Members', TRUE);

// SQL String: CREATE TABLE IF NOT EXISTS Members (...);

Set MySQL Engine:

If you like to set mysql engines during DB creation, then you have to pass the details in array as third argument to create_database().

$attr = array('ENGINE' => 'InnoDB');
$this->dbforge->create_database('db_name', FALSE, $attr);

That should create database & table in codeigniter. Apart from creating database you can also update, drop and access these database tables.

Tweet