Types of Insert Queries in CodeIgniter

$this->db->query()

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

$this->db->insert_string()

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

$this->db->insert_batch()


$data = array(
            array(
                'name'	= >  'name1' , 
                'groupname'= >  'groupname1', 
                'age'	= >  'age1'
            ),
            array(
                '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->db->escape()

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->db->escape_str()

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.

$this->db->insert_id()

Get Affected Rows

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

$this->db->affected_rows();