CodeIgniter – Working with Database

Hello Readers,

I would like to write about database connection in CodeIgniter and how to insert and retrieve values from database.

First step is to create a database and to add a table in it. Open browser and enter http://localhost/phpmyadmin (Assuming you use xampp/wamp). Click “Databases” in the menu. Create a database and a table using the following steps.

• Enter the name of the database in the textbox.(ex. sample_db)
• Press Create button
• Now database will be created and will be shown in the left sidebar
• Click on the database you have just created. (sample_db)
• Enter the name of the table (table1) and number of columns required (4)
• Enter the column names and their datatype. id(int),name(varchar),age(int),city(varchar)
• For the column id, check the field AI, located next to index filed
• Now press Save.

We have created databas and added table into it.

The next step is to configure the database settings into the CodeIgniter. To do that explore to xampp/exercise/application/config/database.php and change the following values.

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';  // add username of the database
$db['default']['password'] = '';      // password for the db.
$db['default']['database'] = 'sample_db';      // database name
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Now save the file and open application/controller/welcome.php, In index() function replace the following code

public function index()
{
	$this->load->model('common_model');
	$data = array('id'=>'','name'=>'Robert','age'=>15,'city'=>'Sydney');
	$id = $this->common_model->add_data($data);
	echo "Values Inserted Successfully. ID is".$id;
}
public function get(){
       $this->load->model('common_model');
       $value = $this->common_model->get_data();
       print_r($value);
}

In application/models/ create a new file ans save it as common_model.php and add the following code

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Common_model extends CI_Model {
	public function add_data($r_data){
                $this->load->database();
		$this->db->insert("table1",$r_data,);
		return $this->db->insert_id();
	}
        public function get_data(){
                $this->load->database();
		$get = $this->db->get("table1");
		return $get->result_array();
	}

}

?>

To view the output, open browser and enter http://localhost/exercise. Now values will be inserted. To view the inserted values, open http://localhost/exercise/index.php/get

If you have any clarifications, add a comment and I will reply.

Thanks for reading.