CodeIgniter Queries

Posted on: April 4, 2019 April 4, 2019
Categories: Codeigniter, PHP, Uncategorized
Tags: CodeIgniter framework, php

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.

Select Query
Insert Query
Update Query
Delete Query
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:

$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

$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:

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

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.

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

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

$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:

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

$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

$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

$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

$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

$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

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

$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:

$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:

$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

$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:

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

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

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

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:

$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

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

$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:

$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:

$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:

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

$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:

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

$this->db->empty_table('tbl_user'); 
// DELETE FROM tbl_user

$this->db->truncate();

Generates a truncate SQL string and runs the query

$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:

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

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

Post Views: 851

admin

767

CodeIgniter Queries

DATE POSTED: 03/04/2019

What is CodeIgniter?

Select Query

$this->db->query()

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

$this->db->get()

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

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

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

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

$this->db->get_where()

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

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

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

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

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

$this->db with or_where_not_in

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

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

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

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

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

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

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

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

Insert Query

$this->db->insert();

Insert With Query Bindings

$this->db->insert_string()

$this->db->insert_batch();

Update Query

$this->db->update();

$this->db->update_batch();

Delete Query

$this->db->delete();

$this->db->empty_table();

$this->db->truncate();

Join Query

$this->db->join()

Share this:

admin