CodeIgniter Queries

DATE POSTED: 03/04/2019

In this post we will explain basic CodeIgniter Queries.

What is CodeIgniter?

CodeIgniter is a powerful PHP framework with a very small footprint, built for developers who need a simple and elegant toolkit to create full-featured web applications.

Today we are going to discuss about the following query.

  1. Select Query
  2. Insert Query
  3. Update Query
  4. Delete Query
  5. Join Query

Select Query

CodeIgniter Select Query will run using following functions. They are

$this->db->query()

To execute a query, use the following function:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->query('QUERY HERE');
$query = $this->db->query("select * from user");
//SELECT `name` FROM ('user')
$this->db->query('QUERY HERE'); $query = $this->db->query("select * from user"); //SELECT `name` FROM ('user')
$this->db->query('QUERY HERE');
$query = $this->db->query("select * from user");
//SELECT `name` FROM ('user')

$this->db->query() With Query Bindings

Use Of Query Bindings Benefit of using binds is that the values are automatically escaped, producing safer queries

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$sql = "SELECT * FROM user WHERE name = ? AND type = ?";
$this->db->query($sql, array('code', 'php'));
//SELECT * FROM user WHERE name = 'code' AND type = 'php'
$sql = "SELECT * FROM user WHERE name = ? AND type = ?"; $this->db->query($sql, array('code', 'php')); //SELECT * FROM user WHERE name = 'code' AND type = 'php'
$sql = "SELECT * FROM user WHERE name = ? AND type = ?"; 
$this->db->query($sql, array('code', 'php'));
//SELECT * FROM user WHERE name = 'code' AND type = 'php'

$this->db->get()

Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$query = $this-> db-> get('users');
//SELECT * FROM user
$query = $this-> db-> get('users'); //SELECT * FROM user
$query = $this-> db-> get('users'); 
//SELECT * FROM user

$this->db->get() Select The Fields

You can use get the particular column or all the column using this function.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
Type 1:
$this->db->select('name');
$query = $this-> db-> get('user');
//SELECT name FROM user
Type 2:
$this->db->select('name')->from('tbl_user')->get();
//SELECT `name` FROM ('user')
Type 1: $this->db->select('name'); $query = $this-> db-> get('user'); //SELECT name FROM user Type 2: $this->db->select('name')->from('tbl_user')->get(); //SELECT `name` FROM ('user')
Type 1: 

$this->db->select('name'); 
$query = $this-> db-> get('user'); 
//SELECT name FROM user

Type 2:

$this->db->select('name')->from('tbl_user')->get();
//SELECT `name` FROM ('user')

$this->db->get() With Limit

The limit keyword is used to limit the number of rows returned in a  query result.  

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
Type 1:
$limit=10;
$query = $this-> db-> get('user',$limit);
//select * from tbl_user limit 10;
Type 2:
$this->db->select('id, name');
$this->db->from('tbl_user');
$this->db->limit(1);
$query = $this-> db-> get();
//select id,name from tbl_user limit 1;
Type 1: $limit=10; $query = $this-> db-> get('user',$limit); //select * from tbl_user limit 10; Type 2: $this->db->select('id, name'); $this->db->from('tbl_user'); $this->db->limit(1); $query = $this-> db-> get(); //select id,name from tbl_user limit 1;
Type 1:

$limit=10;
$query = $this-> db-> get('user',$limit); 
//select * from tbl_user limit 10;


Type 2:

$this->db->select('id, name');
$this->db->from('tbl_user');
$this->db->limit(1);
$query = $this-> db-> get();
//select id,name from tbl_user limit 1;

$this->db->get() With Offset,Limit

The OFF SET value is also most often used together with the LIMIT keyword. The OFF SET value allows us to specify which row to start from retrieving data

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$limit =10;
$offset =20;
$query = $this-> db-> get('user',$offset,$limit);
///select * from user limit 10, 20
$limit =10; $offset =20; $query = $this-> db-> get('user',$offset,$limit); ///select * from user limit 10, 20
$limit	=10;
$offset	=20;
$query = $this-> db-> get('user',$offset,$limit); 
///select * from user limit 10, 20

$this->db->get() With select, from

You can specify the table name in from(), you call the get() function without a parameter.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('id, name');
$this->db->from('user');
$query = $this->db->get();
//SELECT `id`, `name` FROM (`user`) LIMIT 10, 20;
$this->db->select('id, name'); $this->db->from('user'); $query = $this->db->get(); //SELECT `id`, `name` FROM (`user`) LIMIT 10, 20;
$this->db->select('id, name');
$this->db->from('user');
$query = $this->db->get();
//SELECT `id`, `name` FROM (`user`) LIMIT 10, 20;

$this->db->get_where()

Identical to the above function except that it permits you to add a “where” clause in the second parameter, instead of using the db->where() function:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$options=array('usertype'=>'admin');
$query = $this->db->get_where('user',$options);
//SELECT * FROM `an_introduction` WHERE usertype = 'admin'
$options=array('usertype'=>'admin'); $query = $this->db->get_where('user',$options); //SELECT * FROM `an_introduction` WHERE usertype = 'admin'
$options=array('usertype'=>'admin');
$query =  $this->db->get_where('user',$options);
//SELECT * FROM `an_introduction` WHERE usertype = 'admin'


$this->db->get() With Where

This function restricts our select query result set and condition is the filter to be applied on the results.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('username');
$this->db->from('user');
$this->db->where('userid',1);
$this->db->where("usertype","admin");
$query=$this->db->get();
//SELECT `username` FROM (`user`) WHERE `userid` = 11 AND `usertype` = 'admin'
$this->db->select('username'); $this->db->from('user'); $this->db->where('userid',1); $this->db->where("usertype","admin"); $query=$this->db->get(); //SELECT `username` FROM (`user`) WHERE `userid` = 11 AND `usertype` = 'admin'
$this->db->select('username');
$this->db->from('user');
$this->db->where('userid',1);
$this->db->where("usertype","admin");
$query=$this->db->get();
//SELECT `username` FROM (`user`) WHERE `userid` = 11 AND `usertype` = 'admin'

$this->db->get() With Or_Where,

Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with OR if appropriate

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->from('user');
$this->db->where('username !=', 'xxx');
$this->db->or_where('userid >', 11);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE username != 'xxx' OR userid > 11
$this->db->from('user'); $this->db->where('username !=', 'xxx'); $this->db->or_where('userid >', 11); $query=$this->db->get(); //SELECT * FROM (`user`) WHERE username != 'xxx' OR userid > 11
$this->db->from('user');
$this->db->where('username !=', 'xxx');
$this->db->or_where('userid >', 11); 
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE username != 'xxx' OR userid > 11

$this->db->get()  with Where_In

Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with AND if appropriate

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$names = array('name1', 'name2', 'name3');
$this->db->from('user');
$this->db->where('status !=', 'active');
$this->db->or_where_in('username', $names);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')
$names = array('name1', 'name2', 'name3'); $this->db->from('user'); $this->db->where('status !=', 'active'); $this->db->or_where_in('username', $names); $query=$this->db->get(); //SELECT * FROM (`user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')
$names = array('name1', 'name2', 'name3');
$this->db->from('user');
$this->db->where('status !=', 'active');
$this->db->or_where_in('username', $names);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')

$this->db->get()  with or_where_in

Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with OR if appropriate

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$names = array('name1', 'name2', 'name3');
$this->db->from('user');
$this->db->where('status !=', 'active');
$this->db->or_where_in('username', $names);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')
$names = array('name1', 'name2', 'name3'); $this->db->from('user'); $this->db->where('status !=', 'active'); $this->db->or_where_in('username', $names); $query=$this->db->get(); //SELECT * FROM (`user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')
$names = array('name1', 'name2', 'name3');
$this->db->from('user');
$this->db->where('status !=', 'active');
$this->db->or_where_in('username', $names);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')

$this->db->get()  with where_not_in

Generates a WHERE field NOT IN (‘item’, ‘item’) SQL query joined with AND if appropriate

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$names = array('name1', 'name2', 'name3');
$this->db->from('user');
$this->db->where('type', $type);
$this->db->where_not_in('username', $names);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE `type` = 1 AND username NOT IN ('name1', 'name2', 'name3')
$names = array('name1', 'name2', 'name3'); $this->db->from('user'); $this->db->where('type', $type); $this->db->where_not_in('username', $names); $query=$this->db->get(); //SELECT * FROM (`user`) WHERE `type` = 1 AND username NOT IN ('name1', 'name2', 'name3')
$names = array('name1', 'name2', 'name3');
$this->db->from('user');
$this->db->where('type', $type);
$this->db->where_not_in('username', $names);
$query=$this->db->get();
//SELECT * FROM (`user`) WHERE `type` = 1 AND username NOT IN ('name1', 'name2', 'name3')

$this->db with or_where_not_in

Generates a WHERE field NOT IN (‘item’, ‘item’) SQL query joined with OR if appropriate

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$names = array('Frank', 'Todd', 'James');
$this->db->from('user');
$this->db->where('type', $type);
$this->db->or_where_not_in('username', $names);
$query=$this->db->get();
//SELECT * FROM `an_introduction` WHERE `type` = 1 OR `type` NOT IN('Frank', 'Todd', 'James')
$names = array('Frank', 'Todd', 'James'); $this->db->from('user'); $this->db->where('type', $type); $this->db->or_where_not_in('username', $names); $query=$this->db->get(); //SELECT * FROM `an_introduction` WHERE `type` = 1 OR `type` NOT IN('Frank', 'Todd', 'James')
$names = array('Frank', 'Todd', 'James');
$this->db->from('user');
$this->db->where('type', $type);
$this->db->or_where_not_in('username', $names);
$query=$this->db->get();

//SELECT * FROM `an_introduction` WHERE `type` = 1 OR `type` NOT IN('Frank', 'Todd', 'James')

$this->db->get() With Like

This function enables you to generate LIKE clauses, useful for doing searches.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('username');
$this->db->from('user');
$this->db->like("username","code");
$query=$this->db->get();
//SELECT `username` FROM (`user`) WHERE `username` LIKE '%code%'
$this->db->select('username'); $this->db->from('user'); $this->db->like("username","code"); $query=$this->db->get(); //SELECT `username` FROM (`user`) WHERE `username` LIKE '%code%'
$this->db->select('username');
$this->db->from('user');
$this->db->like("username","code");
$query=$this->db->get();
//SELECT `username` FROM (`user`) WHERE `username` LIKE '%code%'

$this->db->get() With or Like

This function is identical to the one above, except that multiple instances are joined by OR:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('username,userid');
$this->db->from('user');
$this->db->like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`user`) WHERE `username` LIKE '%code%' OR `usertype` LIKE '%admin%'
$this->db->select('username,userid'); $this->db->from('user'); $this->db->like('username','code'); $this->db->or_like('usertype','admin'); $query=$this->db->get(); //SELECT `username`, `userid` FROM (`user`) WHERE `username` LIKE '%code%' OR `usertype` LIKE '%admin%'
$this->db->select('username,userid');
$this->db->from('user');
$this->db->like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`user`) WHERE `username` LIKE '%code%' OR `usertype` LIKE '%admin%'

$this->db->get() With not_like();

This function is identical to like(), except that it generates NOT LIKE statements:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('username,userid');
$this->db->from('user');
$this->db->not_like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`user`) WHERE `username` NOT LIKE '%code%' OR `usertype` LIKE '%admin%'
$this->db->select('username,userid'); $this->db->from('user'); $this->db->not_like('username','code'); $this->db->or_like('usertype','admin'); $query=$this->db->get(); //SELECT `username`, `userid` FROM (`user`) WHERE `username` NOT LIKE '%code%' OR `usertype` LIKE '%admin%'
$this->db->select('username,userid');
$this->db->from('user');
$this->db->not_like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`user`) WHERE `username` NOT LIKE '%code%' OR `usertype` LIKE '%admin%'

$this->db->get() with or_not_like;

This function is identical to not_like(), except that multiple instances are joined by OR

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('username,userid');
$this->db->from('user');
$this->db->where('status','active');
$this->db->or_not_like('username','code');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'
$this->db->select('username,userid'); $this->db->from('user'); $this->db->where('status','active'); $this->db->or_not_like('username','code'); $query=$this->db->get(); //SELECT `username`, `userid` FROM (`user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'
$this->db->select('username,userid');
$this->db->from('user');
$this->db->where('status','active');
$this->db->or_not_like('username','code');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'

$this->db->get() With group_by

Permits you to write the GROUP BY portion of your query:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('*');
$this->db->from('user');
$this->db->group_by("states");
//SELECT * FROM (`user`) group by states
$this->db->select('*'); $this->db->from('user'); $this->db->group_by("states"); //SELECT * FROM (`user`) group by states
$this->db->select('*');
$this->db->from('user');
$this->db->group_by("states"); 
//SELECT * FROM (`user`) group by states

$this->db->get() With having

Permits you to write the HAVING portion of your query.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('*');
$this->db->from('user');
$this->db->having("states=1");
//SELECT * FROM (`tbl_user`) HAVING states=1
$this->db->select('*'); $this->db->from('user'); $this->db->having("states=1"); //SELECT * FROM (`tbl_user`) HAVING states=1
$this->db->select('*');
$this->db->from('user');
$this->db->having("states=1"); 
//SELECT * FROM (`tbl_user`) HAVING states=1

$this->db->get() With Order BY

Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are asc or desc, or random.


Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('username');
$this->db->from('user');
$this->db->order_by('username');
$query=$this->db->get();
//SELECT `username` FROM (`user`) ORDER BY `username`
$this->db->select('username'); $this->db->from('user'); $this->db->order_by('username'); $query=$this->db->get(); //SELECT `username` FROM (`user`) ORDER BY `username`
$this->db->select('username');
$this->db->from('user');
$this->db->order_by('username');
$query=$this->db->get();
//SELECT `username` FROM (`user`) ORDER BY `username`

$this->db->get() With count_all_results

Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc.


Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
echo $this->db->count_all('my_table');
echo $this->db->count_all('my_table');
echo $this->db->count_all('my_table');

Insert Query

CodeIgniter Insert Query will run using following functions. They are

$this->db->insert();

Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$data = array(
'title' => 'My title' ,
'name' => 'My Name' ,
'date' => 'My date'
);
$this->db->insert('user', $data);
INSERT INTO user (title, name, date) VALUES ('My title', 'My name', 'My date')
$data = array( 'title' => 'My title' , 'name' => 'My Name' , 'date' => 'My date' ); $this->db->insert('user', $data); INSERT INTO user (title, name, date) VALUES ('My title', 'My name', 'My date')
$data = array(
   'title' => 'My title' ,
   'name' => 'My Name' ,
   'date' => 'My date'
);

$this->db->insert('user', $data); 
INSERT INTO user (title, name, date) VALUES ('My title', 'My name', 'My date')


Insert With Query Bindings

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$sql = "insert into user (name, age, groupname)
values (?, ?, ?)";
$this->db->query($sql,array('codeigniter, 35, 'Group 1'));
//nsert into user (name, age, groupname) VALUES ('codeigniter, 35, 'Group 1')
$sql = "insert into user (name, age, groupname) values (?, ?, ?)"; $this->db->query($sql,array('codeigniter, 35, 'Group 1')); //nsert into user (name, age, groupname) VALUES ('codeigniter, 35, 'Group 1')
$sql = "insert into user (name, age, groupname)
        values (?, ?, ?)";
$this->db->query($sql,array('codeigniter, 35, 'Group 1'));
//nsert into user (name, age, groupname) VALUES ('codeigniter, 35, 'Group 1')

$this->db->insert_string()

Note: Values are automatically escaped, producing safer queries.


Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$data = array(
'name' = > $_POST['name'] ,
'groupname'= > $_POST['groupname'],
'age' = > $_POST['age']
);
$this-> db->insert_string('user', $data);
$data = array( 'name' = > $_POST['name'] , 'groupname'= > $_POST['groupname'], 'age' = > $_POST['age'] ); $this-> db->insert_string('user', $data);
$data = array( 
        'name'	= >  $_POST['name'] , 
        'groupname'= >  $_POST['groupname'], 
        'age'	= >  $_POST['age'] 
    );
$this-> db->insert_string('user', $data);

$this->db->insert_batch();

Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:


Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$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')
$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')
$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')

Update Query

CodeIgniter Update Query will run using following functions. They are

$this->db->update();

Generates an update string and runs the query based on the data you supply. You can pass an array or an object to the function. Here is an example using an array:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$data = array(
'title' => $title,
'name' => $name,
'date' => $date
);
$this->db->where('id', $id);
$this->db->update('mytable', $data);
// UPDATE mytable
// SET title = '{$title}', name = '{$name}', date = '{$date}'
// WHERE id = $id
$data = array( 'title' => $title, 'name' => $name, 'date' => $date ); $this->db->where('id', $id); $this->db->update('mytable', $data); // UPDATE mytable // SET title = '{$title}', name = '{$name}', date = '{$date}' // WHERE id = $id
$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

$this->db->where('id', $id);
$this->db->update('mytable', $data); 

// UPDATE mytable 
// SET title = '{$title}', name = '{$name}', date = '{$date}'
// WHERE id = $id

$this->db->update_batch();

Generates an update string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$data = array(
array(
'title' => 'My title' ,
'name' => 'My Name 2' ,
'date' => 'My date 2'
),
array(
'title' => 'Another title' ,
'name' => 'Another Name 2' ,
'date' => 'Another date 2'
)
);
$this->db->update_batch('mytable', $data, 'title');
// Produces:
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')
$data = array( array( 'title' => 'My title' , 'name' => 'My Name 2' , 'date' => 'My date 2' ), array( 'title' => 'Another title' , 'name' => 'Another Name 2' , 'date' => 'Another date 2' ) ); $this->db->update_batch('mytable', $data, 'title'); // Produces: // UPDATE `mytable` SET `name` = CASE // WHEN `title` = 'My title' THEN 'My Name 2' // WHEN `title` = 'Another title' THEN 'Another Name 2' // ELSE `name` END, // `date` = CASE // WHEN `title` = 'My title' THEN 'My date 2' // WHEN `title` = 'Another title' THEN 'Another date 2' // ELSE `date` END // WHERE `title` IN ('My title','Another title')
$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title'); 

// Produces: 
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE 
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')

The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.

 Delete Query

CodeIgniter Delete Query will run using following functions. They are

$this->db->delete();

Generates a delete SQL string and runs the query.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->delete('tbl_user', array('id' => $id));
//DELETE FROM tbl_user WHERE id = $id
$this->db->delete('tbl_user', array('id' => $id)); //DELETE FROM tbl_user WHERE id = $id
$this->db->delete('tbl_user', array('id' => $id)); 
//DELETE FROM tbl_user WHERE id = $id

The first parameter is the table name, the second is the where clause. You can also use the where() or or_where() functions instead of passing the data to the second parameter of the function:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->where('id', $id);
$this->db->delete('mytable');
// Produces:
// DELETE FROM mytable
// WHERE id = $id
$this->db->where('id', $id); $this->db->delete('mytable'); // Produces: // DELETE FROM mytable // WHERE id = $id
$this->db->where('id', $id);
$this->db->delete('mytable'); 

// Produces:
// DELETE FROM mytable 
// WHERE id = $id

$this->db->empty_table();

Generates a delete SQL string and runs the query.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->empty_table('tbl_user');
// DELETE FROM tbl_user
$this->db->empty_table('tbl_user'); // DELETE FROM tbl_user
$this->db->empty_table('tbl_user'); 
// DELETE FROM tbl_user

$this->db->truncate();

Generates a truncate SQL string and runs the query

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->from('tbl_user');
$this->db->truncate();
(OR)
$this->db->truncate('tbl_user');
// TRUNCATE table tbl_user;
$this->db->from('tbl_user'); $this->db->truncate(); (OR) $this->db->truncate('tbl_user'); // TRUNCATE table tbl_user;
$this->db->from('tbl_user'); 
$this->db->truncate(); 
(OR)
$this->db->truncate('tbl_user'); 
// TRUNCATE table tbl_user;

Join Query

CodeIgniter Join Query will run using following functions. They are

$this->db->join()

Permits you to write the JOIN portion of your query:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');
$query = $this->db->get();
// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id
$this->db->select('*'); $this->db->from('blogs'); $this->db->join('comments', 'comments.id = blogs.id'); $query = $this->db->get(); // Produces: // SELECT * FROM blogs JOIN comments ON comments.id = blogs.id
$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');
$query = $this->db->get();

// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id

Multiple function calls can be made if you need several joins in one query.

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$this->db->join('comments', 'comments.id = blogs.id', 'left');
// Produces: LEFT JOIN comments ON comments.id = blogs.id
$this->db->join('comments', 'comments.id = blogs.id', 'left'); // Produces: LEFT JOIN comments ON comments.id = blogs.id
$this->db->join('comments', 'comments.id = blogs.id', 'left');
// Produces: LEFT JOIN comments ON comments.id = blogs.id

Thanks for using pheonix solutions.

You find this tutorial helpful? Share with your friends to keep it alive.