Free collection of beautiful vector icons for your web pages.

How to perform basic DataBase operations with CodeIgniter?

Configuration of the Database

First, you have to configure the Database (DB) in the folder /application/config/database.php. In this file, you will be able to set everything about the database. The main lines you would have to configure are the first five in the following code:

<?php
$db['default'] = array(
1   'dsn'      => 'mysql:host=localhost; dbname=domainname; charset=utf8',//Describe the connection to the DB
2   'hostname' => 'localhost',             //The hostname of your server
3   'username' => 'name',                  //Users who can access to the DB
4   'password' => 'pass',                  //Password required for accessing to the DB
5   'database' => 'dbname',                //Name of the DB
6   'dbdriver' => 'pdo',                   //Driver of the DB
7   'dbprefix' => '',                      //Optionnal, prefix for a table name
8   'pconnect' => FALSE,                   //Whether to use a persistant connection
9   'db_debug' => (ENVIRONMENT !== 'production'),  //How to display errors (environment)
10  'cache_on' => FALSE,                   //Enable/Disable query caching
11  'cachedir' => '',                      //Location of the cache
12  'char_set' => 'utf8',                  //The character set used in communication with the DB
13  'dbcollat' => 'utf8_general_ci',       //The character collation used in communicating with the DB
14  'swap_pre' => '',                      //A default table prefix that can be swapped with the dbprefix
15  'encrypt'  => FALSE,                   //Whether or not to use an encrypted connection
16  'compress' => FALSE,                   //Whether or not to use client compression (MySQL only)
17  'stricton' => FALSE,                   //TRUE/FALSE - forces 'Strict Mode' connections
18  'failover' => array(),                 //An array with data in case the connection to the main db fails
19  'save_queries' => TRUE                 //TRUE/FALSE - Whether to "save" all executed queries.
); 
?>

Connection to the Database

There is two way to make a connection to a database. It can be done manually or automatically.

Automatically, you will load the database in every page of your website. For it, add the word database in the folder : application/config/autoload.php.
Manually, you will load the database in pages where the next line of code will be written
```
$this -> load -> database()
```

How to make queries ?

Every functions you could use to manipulate the database can be founded on this page. To make a summary, I will quote the main functions you could use:

How to find a name, ID, email...

// Select any row where the field title = $title and the field status = $status
$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->where($array);
$query = $this->db->get('mytable');
$result = $query->result_array();

How to insert data

// Insert a new row in the table
$data = array('name' => Paris, 'title' => Amour, 'Date' => 1998);
$this->db->insert('mytable',$data);

How to update data

// Update a given row in the table
$this->db->set('field','field+1');
$this->db->where('id',4);
$this->db->update('mytable');

How to order data

// Order titles in increasing order
$this->db->order_by('title','ASC');
// Order titles in descending order
$this->db->order_by('title','DESC');
// Order titles randomly
$this->db->order_by('title','RANDOM');

How to delete data

// Delete the data where id=$id from the table mytable
$this->db->delete('mytable', array('id'=>$id);
// Delete an entire table
$this->db->empty_table('mytable');

A lot of other functions are available : like(), count_all_results(), select_avg or limit().

How to perform basic DataBase operations with CodeIgniter?

Configuration of the Database

Connection to the Database

How to make queries ?

See also