Create-Read-Update-Delete (CRUD) in codeigniter

CodeIgniter (CI) is one of popular PHP framework. If you are already building PHP Application, CodeIgniter will help you to do it better and more easily.  For example, they can create web page (that to do basic Create, Read, Update, and Delete ) CRUD automatically.

In this post, we are going to create simple CRUD (Create-Read-Update-Delete) application using CodeIgniter framework. Its very hectic to have same structured  model every time for every controller, so it’s a good practice to have a generic method’s for all controller which is further very easy to remember and use.
so i came with some generic model which can be used by every middle or high level project and customised as per requirement.

you can refer to Code Igniter Active Record Class page.

model.php

function insert($table,$data) {
	$this->insert($table,$data);
	return $this->insert_id();
}

function update($table,$where=array(),$data) {
	$this->update($table,$data,$where);
	return $this->affected_rows();
}

function delete($table,$where=array()) {
	$this->delete($table,$where);
	return $this->affected_rows();

}

function query($query) {
	$q = $this->query($query);
	if(is_object($q)) {
		return $q->result_array();
	} else {
		return $q;
	}
}

The basic use can be as follows

From a controller we can call model’s function as below given.

//get all users in the users table
$users = $this->model->get('users');

//get the user with an ID=5 from the users table, return a single array
$user = $this->model->get('users',array('id'=>5),TRUE);

//update the users table with new data for user with ID=5
$data = array(
	'name'=>'arvind',
	'birthday'=>'05-05-1988'
);
$affected_rows = $this->model->update('users',array('id'=>5),$data);

//insert new user into the users table
$data = array(
	'first_name'=>'arvind',
	'last_name'=>'prajapati',
	'password'=>md5('arvindprajapati')
	'created'=>time()
);
$id = $this->model->insert('users',$data);

//delete user ID=5 from table users
$affected_rows = $this->model->delete('users',array('id'=>5));

//get all the information about user with ID=5 from users table
$data = $this->model->query('SELECT * FROM users WHERE id=5');

Beyond the simplicity of codeigniter, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adaptor. It also allows for safer queries, since the values are escaped automatically by the system.

Following are some quick review of Active Record Query function available in codeigniter.

Use of Query:

$this->db->query();

//To submit a query, use the following function:
$this->db->query('YOUR QUERY HERE');

Place your raw SQL in the query() method
$query = $this->db->query("select * from tbl_user");

//Pass SQL query in a variable
$sql = "select * from tbl_user";
$query = $this->db->query($sql);

Use Of Query Bindings

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

$sql = "SELECT * FROM tbl_user WHERE name = ? AND type = ?";
$this->db->query($sql, array('arvind', 'admin'));

Use the built – in get() method

$this->db->get();
$query = $this->db->get('users');
$query = $this->db->get('employees');

Select the fields with get() method

//Format 1
$this->db->select('name');
$query = $this->db->get('tbl_user');

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

Select the fields with limit in get() method

Sample Code:1
	$limit=10;
	$query = $this->db->get('tbl_user',$limit);

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

//normal sql query will be likewise
select id,name from tbl_user limit 1;


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


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

//SELECT `id`, `name` FROM (`tbl_user`) LIMIT 10, 20;

Fields selection with where, or_where, where_in, or_where_in, where_not_in method

	
<h4>Sample Code 1</h4>	
$this->db->get() 
$this->db->select('username');
$this->db->from('tbl_user');
$this->db->where('userid',11);
$this->db->where("usertype","admin");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `userid` = 11 AND `usertype` = 'admin'


<h4>Sample Code 2</h4>

$this->db->select('username');
$this->db->from('tbl_user');
$this->db->where('userid',11);
$this->db->where("usertype !=","admin");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `userid` = 11 AND `usertype` != 'admin'


<h4>Sample Code 3:</h4>
$this->db->select('username');
$this->db->from('tbl_user');
$this->db->where("userid > 11 AND usertype='user'");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `userid` > 11 AND usertype='user'


<h4>Sample Code 4:</h4>
$this->db->from('tbl_user');
$this->db->where('username !=', $name);
$this->db->or_where('userid >', $userid);
//SELECT * FROM (`tbl_user`) WHERE username != 'admin' OR userid > 5


<h4>Sample Code 5:</h4>
$names = array('name1', 'name2', 'name3');
$this->db->from('tbl_user');
$this->db->where('status !=', $status);
$this->db->or_where_in('username', $names);
//SELECT * FROM (`tbl_user`) WHERE status='active' OR username IN ('name1', 'name2', 'name3')


<h4>Sample Code 6:</h4>
$names = array('name1', 'name2', 'name3');
$this->db->from('tbl_user');
$this->db->where_not_in('username', $names);
//SELECT * FROM (`tbl_user`) WHERE username NOT IN ('name1', 'name2', 'name3')


<h4>Sample Code 7:</h4>
$names = array('name1', 'name2', 'name3');
$this->db->from('tbl_user');
$this->db->where('status !=', $status);
$this->db->or_where_not_in('username', $names);
//SELECT * FROM (`tbl_user`) WHERE status='active' OR username NOT IN ('name1', 'name2', 'name3')

Use of get_where() method in codeigniter

$status='admin';
$options=array('usertype'=>$status);
$query =  $this->db->get_where('tbl_user',$options);

//get_where with limit
$limit=10;
$query =  $this->db->get_where('tbl_user',$options,$limit);

Use of get() method with With Like, or_like, not_like, or_not_like

<h4>Sample Code 1:</h4>
$this->db->select('username');
$this->db->from('tbl_user');
$this->db->like("username","code");
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) WHERE `username` LIKE '%code%'

<h4>Sample Code 2:</h4>
$this->db->select('username,userid');
$this->db->from('tbl_user');
$this->db->like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();

//SELECT `username`, `userid` FROM (`tbl_user`) WHERE `username` LIKE '%code%' OR `usertype` LIKE '%admin%'

<h4>Sample Code 3:</h4>
$this->db->select('username,userid');
$this->db->from('tbl_user');
$this->db->not_like('username','code');
$this->db->or_like('usertype','admin');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`tbl_user`) WHERE `username` NOT LIKE '%code%' OR `usertype` LIKE '%admin%'

<h4>Sample Code 4:</h4>
$this->db->select('username,userid');
$this->db->from('tbl_user');
$this->db->where('status','active');
$this->db->or_not_like('username','code');
$query=$this->db->get();
//SELECT `username`, `userid` FROM (`tbl_user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'

get() method with group_by clause

$this->db->select('*');
$this->db->from('tbl_user');
$this->db->group_by("states");
//SELECT * FROM (`tbl_user`) group by states

get() method with having clause

$this->db->select('*');
$this->db->from('tbl_user');
$this->db->having("states=1");
//SELECT * FROM (`tbl_user`) HAVING states=1

get() method with order by clause

<h4>Sample Code 1:</h4>
$this->db->select('username');
$this->db->from('tbl_user');
$this->db->order_by('username');
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) ORDER BY `username`

<h4>Sample Code 2:</h4>
$this->db->select('username');
$this->db->from('tbl_user');
$this->db->order_by('username','desc');
$query=$this->db->get();
//SELECT `username` FROM (`tbl_user`) ORDER BY `username` desc

Mathematics Function In Codeigniter

$this->db->select_max()
$this->db->select_max('age');
$query = $this->db->get('tbl_user');
//SELECT MAX(age) as age FROM tbl_user


$this->db->select_max('age', 'userage');
$query = $this->db->get('tbl_user');
//SELECT MAX(age) as userage FROM tbl_user

<h4>$this->db->select_min()</h4>
$this->db->select_min('age');
$query = $this->db->get('tbl_user');
//SELECT MIN(age) as age FROM tbl_user

<h4>$this->db->select_sum()</h4>
$this->db->select_sum('age');
$query = $this->db->get('tbl_user');
//SELECT SUM(age) as age FROM tbl_user

Available joins types in codeigniter i.e left, right, outer, inner, left outer, and right outer

$this->db->select('*');
$this->db->from('comments');
$this->db->join('tbl_user', 'tbl_user.id = comments.id');

$query = $this->db->get();
// SELECT * FROM comments
// JOIN tbl_user ON tbl_user.id = comments.id

$this->db->num_rows()

$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);
if(query->num_rows()){
    // work process
}

$this->db->num_fields()
//The number of FIELDS (columns) returned by the query. Make sure to call the function using your query result object:
$query = $this->db->query('SELECT * FROM tbl_user');
echo $query->num_fields();

Query Result by result() method

<h4>Query Result With result()</h4>
$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);
foreach ($query- >result() as $row)
{
	echo $row- > name;
	echo $row- > age;
	echo $row- > groupname;
}


<h4>Query Result With result_array()</h4>
$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);
foreach ($query->result_array() as $row)
{
	echo $row['name'];
	echo $row['gag'];
	echo $row['groupname'];
}
<h4>Query With Single Result()</h4>
$query_str="SELECT * FROM tbl_user t limit 50";
$query=$this->db->query($query_str);

// fetch one row data
$record=$query->row();
echo $record->name;
echo $record->age;
echo $record->groupname;

// alternative method
$record=$query->row_array();
echo $record['name'];
echo $record['age'];
echo $record['groupname'];

Count of Query Result count_all() method

//Count Of Query Result
//Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter.
echo $this->db->count_all('tbl_user');
// Produces an integer, like 467

 

That’s it folks!

Thanks!