Have any Question?

You can ask below or enter what you are looking for!

Types of Insert queries in Codeigniter

In Codeigniter We have 8 types of queries. They are query, query bindings, insert_string, insert_batch, affected_rows, and insert query

Basic Insert Query Syntax $this->db->query()

Examples for Insert 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);

Query bindings

Benefit of  Query bindings is that the values are  escaped automatically and this queries are safer

Example:

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


Standard Insert Query

Basic Query for Standard Insert with Example

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

insert_string Query

In This Query Values are Escaped automatically  and this queries are Totally safe.

Syntax For Insert_string Query

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

insert_batch Query

The INSERT statement in MySQL also supports the use of VALUES syntax to insert multiple rows as a bulk insert statement. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.


Syntax for insert_batch Query

$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')

Get Inserted ID Query

If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately.

$this->db->insert_id()

Affected Rows in codeigiter

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


Syntax for Affected Rows in Codeigniter

$this->db->affected_rows();

Leave a Reply

Your email address will not be published. Required fields are marked *