CodeIgniter – Insert Query

CodeIgniter insert query will execute using following functions. They are


Program 1:

$sql = "insert into tbl_user (name, age, groupname)
			values ('codeigniter, 35, 'Group 1')";
$this-> db-> query($sql);

Program 2:

$data = array( 
	'name'	= >  $_POST['name'] , 
	'groupname'= >  $_POST['groupname'], 
	'age'	= >  $_POST['age'] );
$this-> db->insert('tbl_user', $data);

Insert With Query Bindings

Use Of Query Bindings

Benefit of using binds is that the values are automatically escaped, producing safer queries

$sql = "insert into tbl_user (name, age, groupname)
			values (?, ?, ?)";
$this-> db-> query($sql,array('codeigniter, 35, 'Group 1'));

Standard Insert

$sql = "INSERT INTO tbl_user (name, groupname, age) 
        VALUES (".$this->db->escape($name).", ".$this->db->escape($groupname).".", ".$this->db->escape($age).")";


Note: Values are automatically escaped, producing safer queries.

$data = array( 
	'name'	= >  $_POST['name'] , 
	'groupname'= >  $_POST['groupname'], 
	'age'	= >  $_POST['age'] );
$this-> db->insert_string('tbl_user', $data);


$data = array(
    'name'	= >  'name1' , 
	'groupname'= >  'groupname1', 
	'age'	= >  'age1'
	'name'	= >  'name2' , 
	'groupname'= >  'groupname2', 
	'age'	= >  'age2'
$this->db->insert_batch('tbl_user', $data); 
//INSERT INTO mytable (name, groupname, age) VALUES ('name1', 'groupname1', 'age1'), ('name2', 'groupname2', 'age2')

Escaping Insert Queries


This function determines the data type so that it can escape only string data. It also automatically adds single quotes around the data so you don’t have to:

$sql = "INSERT INTO tbl_user (name) VALUES(".$this->db->escape($name).")";


This function escapes the data passed to it, regardless of type. Most of the time you’ll use the above function rather than this one. Use the function like this:

$sql = "INSERT INTO tbl_user (name) VALUES('".$this->db->escape_str($name)."')";

Get Inserted ID

The insert ID number when performing database inserts.


Get Affected Rows

Displays the number of affected rows, when doing “write” type queries (insert, update, etc.).