CodeIgniter – Insert Query

CodeIgniter insert query will execute using following functions. They are

$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 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).")";
$this->db->query($sql);

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