Types of Insert Queries in CodeIgniter


Program 1:

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

Program 2:

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

Insert Data With Query Bindings

Query Bindings values are automatically escaped and Those are producing safer queries

$sql = "INSERT INTO tenant (name, image, url) VALUES (?, ?, ?)";
$this->db->query($sql, array($name, $image, $url));


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 Query automatically adds single quotes and it can escape only string data

$sql = "INSERT IGNORE INTO my_table(lat, lng, date, type) VALUES (?,?,?,?);"; 
$this->db->query($sql, array($data['lat'], $data['lng'], $data['date'], $data['type']));


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.).