Manipulating Databases¶

CI_DB_forge::create_database($db_name)¶

Permits you to create the database specified in the first parameter. Returns TRUE/FALSE based on success or failure:

if (ee()->dbforge->create_database('my_db'))
{
        echo 'Database created!';
}

Parameters:	$db_name (string) – The name of the database to create
Returns:	The result of the CREATE DATABASE query
Return type:	CI_DB_result

CI_DB_forge::drop_database($db_name)¶

Permits you to drop the database specified in the first parameter. Returns TRUE/FALSE based on success or failure:

if (ee()->dbforge->drop_database('my_db'))
{
        echo 'Database deleted!';
}

Parameters:	$db_name (string) – The name of the database to drop
Returns:	The result of the DROP DATABASE query
Return type:	CI_DB_result

Creating Tables¶

There are several things you may wish to do when creating tables. Add fields, add keys to the table, alter columns. CodeIgniter provides a mechanism for this.

CI_DB_forge::add_field($field)¶

Fields are created via an associative array. Within the array you must include a type key that relates to the datatype of the field. For example, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR) also require a constraint key.

$fields = array(
        'users' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
        ),
);
// will translate to "users VARCHAR(100)" when the field is added.

Additionally, the following keys can be used:

• unsigned: Set to TRUE to generate UNSIGNED in the field definition.
• default: Set to a value to generate a default value in the field definition.
• null: Set to TRUE to generate NULL in the field definition. Without this, the field will default to NOT NULL.
• auto_increment: Set to TRUE to generate an auto_increment flag on the field. Note that the field type must be a type that supports this, such as integer.

$fields = array(
        'blog_id' => array(
                'type' => 'INT',
                'constraint' => 5,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
        ),
        'blog_title' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
        ),
        'blog_author' => array(
                'type' =>'VARCHAR',
                'constraint' => '100',
                'default' => 'King of Town',
        ),
        'blog_description' => array(
                'type' => 'TEXT',
                'null' => TRUE,
        ),
);

After the fields have been defined, they can be added using ee()->dbforge->add_field($fields); followed by a call to the create_table() method.

Passing strings as fields

If you know exactly how you want a field to be created, you can pass the string into the field definitions with add_field():

ee()->dbforge->add_field("label varchar(100) NOT NULL DEFAULT 'default label'");

Note

Multiple calls to add_field() are cumulative.

Creating an id field

There is a special exception for creating id fields. A field with type id will automatically be assinged as an INT(9) auto_incrementing Primary Key:

ee()->dbforge->add_field('id');
// gives id INT(9) NOT NULL AUTO_INCREMENT

Parameters:

$field (array) – A multiddimensional associative array containing field names as the keys and an associative array of parameters for creating database fields: type: The type of field to create (e.g. INT, VARCHAR, TEXT) constraint: The length of the field unsigned: Set to TRUE to generate UNSIGNED in the field definition. default: Set to a value to generate a default value in the field definition. null: Set to TRUE to generate NULL in the field definition. Without this, the field will default to NOT NULL. auto_increment: Set to TRUE to generate an auto_increment flag on the field. Note that the field type must be a type that supports this, such as integer.

Return type:

void

CI_DB_forge::add_key($key[, $primary = FALSE])¶

Generally speaking, you’ll want your table to have Keys. This is accomplished with add_key(). An optional second parameter set to TRUE will make it a primary key. Note that add_key() must be followed by a call to create_table().

Multiple column non-primary keys must be sent as an array. Sample output below is for MySQL.

ee()->dbforge->add_key('blog_id', TRUE);
// gives PRIMARY KEY `blog_id` (`blog_id`)

ee()->dbforge->add_key('blog_id', TRUE);
ee()->dbforge->add_key('site_id', TRUE);
// gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`)

ee()->dbforge->add_key('blog_name');
// gives KEY `blog_name` (`blog_name`)

ee()->dbforge->add_key(array('blog_name', 'blog_label'));
// gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`)

Parameters:	$key (string) – The name of the field to create a key for $primary (boolean) – Set this to TRUE to make the key a primary key
Return type:	void:

CI_DB_forge::create_table($table[, $if_not_exists = FALSE])¶

After fields and keys have been declared, you can create a new table with:

ee()->dbforge->create_table('table_name');
// gives CREATE TABLE table_name

An optional second parameter set to TRUE adds an IF NOT EXISTS clause into the definition:

ee()->dbforge->create_table('table_name', TRUE);
// gives CREATE TABLE IF NOT EXISTS table_name

You could also pass optional table attributes, such as MySQL’s ENGINE:

$attributes = array('ENGINE' => 'InnoDB');
ee()->dbforge->create_table('table_name', FALSE, $attributes);
// produces: CREATE TABLE `table_name` (...) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Note

Unless you specify the CHARACTER SET and/or COLLATE attributes, create_table() will always add them with your configured char_set and dbcollat values, as long as they are not empty (MySQL only).

Parameters:	$table (string) – The name of the table to create $if_not_exists (boolean) – Set to TRUE to only create the table if it does not exist
Returns:	The result of the CREATE TABLE query
Return type:	CI_DB_result

Manipulating Tables¶

CI_DB_forge::drop_table($table_name)¶

Execute a DROP TABLE statement with an IF EXISTS clause:

// Produces: DROP TABLE IF EXISTS table_name
ee()->dbforge->drop_table('table_name');

Parameters:	$table (string) – The name of the table to drop
Returns:	The result of the DROP TABLE IF EXISTS query
Return type:	CI_DB_result

CI_DB_forge::rename_table($table_name, $new_table_name)¶

Executes a TABLE rename:

ee()->dbforge->rename_table('old_table_name', 'new_table_name');
// gives ALTER TABLE old_table_name RENAME TO new_table_name

Parameters:	$table_name (string) – The name of the table being renamed $new_table_name (string) – The new table name
Returns:	The result of the ALTER TABLE .. RENAME TO .. query
Return type:	CI_DB_result

CI_DB_forge::add_column($table, array $field[, $after_field = ''])¶

The add_column() method is used to modify an existing table. It accepts the same field array as above, and can be used for an unlimited number of additional fields:

$fields = array(
        'preferences' => array('type' => 'TEXT')
);
ee()->dbforge->add_column('table_name', $fields);
// Executes: ALTER TABLE table_name ADD preferences TEXT

You can also take advantage of MySQL’s AFTER and FIRST clauses to position the new column:

// Will place the new column after the `another_field` column:
$fields = array(
        'preferences' => array('type' => 'TEXT', 'after' => 'another_field')
);

// Will place the new column at the start of the table definition:
$fields = array(
        'preferences' => array('type' => 'TEXT', 'first' => TRUE)
);

Parameters:	$table (string) – The table to add the column to $field (array) – The column defintition (see add_field() for details) $after_field (string) – The field that should come before this new field, leave empty to be the last field
Returns:	The result of the ALTER TABLE query
Return type:	CI_DB_result

CI_DB_forge::drop_column($table, $column_name)¶

Used to remove a column from a table:

ee()->dbforge->drop_column('table_name', 'column_to_drop');

Parameters:	$table (string) – The table to drop the column from $column_name (string) – The name of the column to drop
Returns:	The result of the DROP query
Return type:	CI_DB_result

CI_DB_forge::modify_column($table, array $field)¶

The usage of this method is identical to add_column(), except it alters an existing column rather than adding a new one. In order to change the name you can add a name key into the field defining array:

$fields = array(
        'old_name' => array(
                'name' => 'new_name',
                'type' => 'TEXT',
        ),
);
ee()->dbforge->modify_column('table_name', $fields);
// gives ALTER TABLE table_name CHANGE old_name new_name TEXT

Parameters:	$table (string) – The table to add the column to $field (array) – The column defintition (see add_field() for details)
Returns:	The result of the ALTER TABLE query
Return type:	CI_DB_result