Codeigniter Tutorials

Home » Codeigniter »

codeigniter Database Query

Connecting to a Database

We can connect to database in the following two way −

Automatic Connecting

Manual Connecting

Automatic Connecting :- Automatic connection can be done by using the file application/config/autoload.php. Automatic connection will load the database for each and every page. We just need to add the database library as shown below −

$autoload['libraries'] = array('database');

Manual Connecting − If you want database connectivity for only some of the pages, then we can go for manual connecting. We can connect to database manually by adding the following line in any class.

$this->load->database();

How to insert record in db.

To insert a record in the database, the insert() function is used. The $data is an array in which we have set the data and to insert this data to the table stud, we just need to pass this array to the insert function in the 2nd argument.

You can either pass an array or an object to the function.

$data = array(
'emp_no' => '786',
'name' => 'mahtab habib',
'mobile_no' => '7838897299'
);
$result = $this->db->insert("student_info", $data);
// Produces: INSERT INTO student_info (emp_no, name, mobile_no) VALUES ('786', 'mahtab habib', '7838897299')

############### OR ###########

$data = array(
'emp' => '786',
'name' => 'mahtab',
'mobile_no' => '78388972**'
);
// emp,name,mobile_no is the column name of the employee_info table. $result = $this->Auth_model->add_employee_data($data);

Auth_model.php

public function add_employee_data($data)
{
$result = $this->db->insert('employees_info',$data);
$inserted_id = $this->db->insert_id();
}

############### OR ###########

class Myclass {
public $title = 'MD';
public $content = 'Mahtab';
public $date = '2023/14/02';
}

$object = new Myclass;
$this->db->insert('mytable', $object);
// Produces: INSERT INTO mytable (title, content, date) VALUES ('MD', 'Mahtab', '2023/14/02')

How to fetch a Record in codeigniter.

To select a record in the database, the get function is used. The first statement fetches all the records from "stud" table and returns the object, which will be stored in $query object. The second statement calls the result() function with $query object to get all the records as array.

$qry = $this->db->query("Select * from itechxpert where department=".$dep_id);
$row_data = $qry->result_array();
return $row_data;

############### OR ###########

$sql = "select * from itechxpert";
$query = $this->db->query($sql);
return $query->result();

############### OR ###########

$this->db->select('*');
$query = $this->db->get("itechxpert");
$data['records'] = $query->result();

############### OR ###########

// Function To Fetch Selected Student Record
function show_student_id($id)
{
$this->db->select('*');
$this->db->from('students');
$this->db->where('student_id', $id);
$query = $this->db->get();
$result = $query->result();
return $result;
}

OR

public function get_contents()
{
$this->db->select('*');
$this->db->from('itechxpert');
$query = $this->db->get();
if ($query->num_rows() > 0){
// return true;
return $result = $query->result();
} else {
return false;
}
}

Closing a Connection

Database connection can be closed manually, by executing the following code.

$this->db->close();

How to show database error?

set()

This function enables you to set values for inserts or updates.

$this->db->set('name', $name);
$this->db->insert('ittutorial_table');
// Produces: INSERT INTO ittutorial_table (`name`) VALUES ('{$name}')

$this->db->set('name', $name);
$this->db->set('title', $title);
$this->db->set('status', $status);
$this->db->insert('mytable');

associative array with set()

$array = array(
'name' => $name,
'title' => $title,
'status' => $status
);

$this->db->set($array);
$this->db->insert('mytable');

Updating a Record

To update a record in the database, the update() function is used along with set() and where() functions as shown in the tables below. The set() function will set the data to be updated.

$data = array(
'id' => '786',
'name' => 'itechxpert'
);
$this->db->set($data);
$this->db->where("id", '786');
$this->db->update("stud_info", $data);
$afftectedRows = $this->db->affected_rows();
if($afftectedRows)
{
echo "update done" ;
}
else
{
echo "NOT Update";
}

############### OR ###########

function update_student_id() {
$id= $this->input->post('uid');
$data = array(
'emp_name' => $this->input->post('uname'),
'emp_email' => $this->input->post('uemail'),
'emp_mobile' => $this->input->post('umobile'),
'emp_address' => $this->input->post('uaddress')
);
$result = $this->Auth_model->update_student_info($id,$data);
}

Auth_model.php

// Update Query For Selected Student
function update_student_info($id,$data)
{
$this->db->where('emp_id', $id);
$this->db->update('itechxpert', $data);
$afftectedRows = $this->db->affected_rows();
return $afftectedRows;
}

UPDATE ittutorial SET name = 'Sana', email = 'sana@ittutorial.in', url = 'ittutorial.in' WHERE id = 1 AND status = 'active'

update multiple row in codeigniter :-

$data = array(
array(
'ID' => 1,
'Name' => 'mahi',
'Value' => 'True'
),
array(
'ID' => 2,
'Name' => 'Sana',
'Value' => 'Good'
)
);
$this->db->update_batch('tableName', $data, 'id');

Deleting a Record :-

To delete a record in the database, the delete() function is used.

$this->db->delete("stud_info", "id = 1");

############### OR ###########

function delete_user_id($id)
{
$this->db->where('employee_id', $id);
$this->db->delete('employee_details');
}

Codeigniter get last executed query OR Print Query.

public function print_query()
{
$data['data'] = $this->db->get("items")->result();
$your_query = $this->db->last_query();
print_r($your_query);
}

How to get last executed query in codeigniter?

public function test()
{
$query = $this->db->get("itechxpert");
$str = $this->db->last_query();
echo "<pre>";
print_r($str);
echo "</pre>";
exit;
}

How to get last record from MySQL table in Codeigniter?

$last = $this->db->order_by('id',"desc")
->limit(1)
->get('items')
->row();
print_r($last);

How to insert data in table?

Insert batch :-

public function InsertArray($table,$data){
if(empty($table) || empty($data) || (!is_array($data)) || (sizeof($data) < 1)){
return 'check parameters';
}
else{
$sql_query= $this->db->insert_batch($table ,$data);
if($sql_query){
return true;
}
else{
return false;
}
}
}

Insert batch Example :-

$data = array(
array(
'title' => 'Mahira' ,
'name' => 'Sana' ,
'date' => '07/05/2020'
),
array(
'title' => 'Mahi' ,
'name' => 'NB' ,
'date' => '11/06/2019'
)
);

$this->db->insert_batch('ittutorial', $data);

// Produces: INSERT INTO ittutorial (title, name, date) VALUES ('Mahira', 'sana', '07/05/2020'), ('Mahtab', 'NB', '11/06/2019')

How to get last inserted id in Codeigniter?

function add_item()
{
$data = ['name'=>'mahtab', 'designation'=>'Developer'];
$result = $this->db->insert('items', $data);
$insertId = $this->db->insert_id();
return $insertId;
}

How to update record in table?

How to fetch Single Row of table?

public function FetchSingleRow($table, $field, $id){
$this->db->select('*');
$this->db->from($table);
$this->db->where($field, $id);
$query=$this->db->get();
$result=$query->row_array();
return $result;
}

Get Row of table with Join Two table with condition?

public function get_by_id($id) {
$this->db->select('emp.*,city.name');
$this->db->from('employee as emp');
$this->db->join('cities as city', 'emp.id = city.id');
// $this->db->join('cities as city', 'emp.id = city.id','left');
$this->db->where('emp.id', $id);
$query = $this->db->get();
return $query->row();
}

How to fetch All Row of Table?

public function FetchAllRow($table){
$this->db->select('*');
$query=$this->db->get($table);
return $query->result();
}

Get All Row of Table?

public function get_all() {
$this->db->select('emp.*,city.name');
$this->db->from('employee as emp');
$this->db->join('cities as city', 'emp.id = city.id');
// $this->db->join('cities as city', 'emp.id = city.id','left');
$query = $this->db->get();
return $query->row();
}

How to fetch all row by ID

public function FetchAllRowById($table, $field, $id)
//public function FetchAllRowById($table,$id)
{
$this->db->select('*');
$this->db->from($table);
$this->db->where($field, $id);
$query=$this->db->get();
$result=$query->result();
return $result;
}

where() :-

$this->db->where('name', $name);
$this->db->where('title', $title);
$this->db->where('status', $status);

Custom String :-

$where = "name='Sana' AND status='1' OR city='Ara'";
$this->db->where($where);

or_where() :-

$this->db->where('name !=', $name);
$this->db->or_where('id >', $id);
// Produces: WHERE name != 'Sana' OR id > 07

where_in() :-

$names = array('Mahtab', 'NB', 'Sana');
$this->db->where_in('username', $names);
// Produces: WHERE username IN ('Mahtab', 'NB', 'Sana')

or_where_in() :-

$names = array('Mahtab', 'NB', 'Sana');
$this->db->or_where_in('username', $names);
// Produces: OR username IN ('Mahtab', 'NB', 'Sana')

where_not_in() :-

$names = array('Mahtab', 'NB', 'Sana');
$this->db->where_not_in('username', $names);
// Produces: WHERE username NOT IN ('Mahtab', 'NB', 'Sana')

or_where_not_in() :-

$names = array('Mahtab', 'NB', 'Sana');
$this->db->or_where_not_in('username', $names);
// Produces: OR username NOT IN ('Mahtab', 'NB', 'Sana')

Custom key/value method :-

$this->db->where('name !=', $name);
$this->db->where('id <', $id);
// Produces: WHERE name != 'Sana' AND id < 45

Associative array method :-

$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->where($array);
// Produces: WHERE name = 'Sana' AND title = 'boss' AND status = 'active'

$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
$this->db->where($array);

How to insert data in another Database.

Insert Data in Another Database.

function getUser_AccessID($data){

$another_db = $this->load->database('myanotherdb', TRUE);
$qry = "INSERT INTO Users SET UserID='".$data['UserID']."' , LogIn='".$data['LogIn']."' ";
$query = $another_db->query($qry);
return $another_db->insert_id();

}

Delete Last Three month Login details data

function delete_LastThreemonth_Login_Data($uid){

$alternate = $this->load->database('myanotherdb', TRUE);
$query = $alternate->query("delete from itechxpert_userlog where userid=".$alternate->escape_str($uid)." AND datediff(now(), logintime ) > 90 ");
return;

}

Update Table.

$this->db->query('YOUR QUERY HERE');

The query() function returns a database result object. queries are run it simply returns TRUE or FALSE depending on success or failure. When retrieving data you will typically assign the query to your own variable.

$query = $this->db->query('select * from employee');

$sql = "SELECT * FROM ittutorial WHERE pk_id='0' AND status ='1' ORDER BY pk_id asc";
$query = $this->db->query($sql);
return $query->result();

function ittutorial_dept($dept_type)
{
$sql = "SELECT * FROM ittutorial WHERE (pk_id='0' AND department_type =".$dept_type.") AND status ='1' ORDER BY pk_id asc";
$query = $this->db->query($sql);
return $query->result();
}

Query Bindings

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

The question marks in the query are automatically replaced with the values in the array in the second parameter of the query function. Binding also work with arrays, which will be transformed to IN sets.

$sql = "SELECT * FROM some_table WHERE id IN ? AND status = ? AND author = ?";
$this->db->query($sql, array(array(3, 6), 'live', 'Rick'));

The resulting query will be:
SELECT * FROM some_table WHERE id IN (3,6) AND status = 'live' AND author = 'Rick'

Generating Query Results :-

There are several ways to generate query results:

Result Arrays

Result Rows

Custom Result Objects

Result Helper Methods

Class Reference

Result Arrays :- result()

This method returns the query result as an array of objects, or an empty array on failure. Typically you’ll use this in a foreach loop, like this:

$query = $this->db->query("select * from ittutorial");
$result = $query->result();
foreach ($result as $row)
{
echo $row->title;
echo $row->name;
}

insert object data in single array.

$this->db->select('*');
$this->db->from('ittutorial');
$qry = $this->db->get();
$result = $qry->result();
//echo print_r($result);"</pre>"; die;

foreach($result as $row)
{
// $itech['fki_uid'][] = array('fki_id'=>$row->fki_uid);
$itech[] = $row->fki_uid;
}
// echo "<pre>";print_r($itech); echo "</pre>";

/*
$arr = array();
for($i=0;$i<count($result); $i++)
{
array_push($arr, $result[$i]->fki_uid);
}
*/

if(!in_array($uid, $itech))
{
redirect('any_url');
}

$data['itech_list'] = $this->Itech_model->get_itech_list($uid);

// for result_array()
//echo $techDept = $data['itech_list'][0]['fki_tech_id'];

// for array object result().
echo $techDept = $data['itech_list'][0]->fki_tech_id;

$data['itech_details'] = $this->Itech_model->get_tech_details($techDept);

$this->load->view('ittutorial_view',$data);

Itech_model.php
function get_itech_list($uid)
{
$this->db->select('*');
$this->db->from('ittutorial');
$this->db->where('userid', $uid);
$qry = $this->db->get();
//$result = $qry->result_array();
return $result = $qry->result();

}

You can also pass a string to result() which represents a class to instantiate for each result object.

$query = $this->db->query("SELECT * FROM users;");
foreach ($query->result('User') as $user)
{
echo $user->name; // access attributes
echo $user->reverse_name(); // or methods defined on the 'User' class
}

result_array():-

This method returns the query result as a pure array, or an empty array when no result is produced. Typically you’ll use this in a foreach loop, like this:

$query = $this->db->query("select * from ittutorial");
$result = $query->result_array();
foreach ($result as $row)
{
echo $row['title'];
echo $row['name'];
echo $row['age'];
}

############### OR ###########

foreach ($result as $row)
{
echo $row[0]['title'];
echo $row[0]['name'];
echo $row[0]['age'];
}

############### OR ###########

$data['itech_list'] = $this->Itech_model->get_itech_list($uid);
echo $techDept = $data['itech_list'][0]['user_name'];

function get_itech_list($uid)
{
$this->db->select('*');
$this->db->from('ittutorial');
$this->db->where('userid', $uid);
$qry = $this->db->get();
$result = $qry->result_array();
}

Result Rows :- row()

This method returns a single result row. If your query has more than one row, it returns only the first row. The result is returned as an object.

$query = $this->db->query("YOUR QUERY");

$row = $query->row();

if (isset($row))
{
echo $row->title;
echo $row->name;
}

If you want a specific row returned you can submit the row number as a digit in the first parameter:

$row = $query->row(5);
You can also add a second String parameter, which is the name of a class to instantiate the row with:

$query = $this->db->query("SELECT * FROM users LIMIT 1;");
$row = $query->row(0, 'User');

echo $row->name; // access attributes
echo $row->reverse_name(); // or methods defined on the 'User' class

Result Rows :- row_array()

row_array() method returns a single result row array. The result is returned as an object.

$query = $this->db->query("YOUR QUERY");

$row = $query->row_array();

if (isset($row))
{
echo $row['title'];
echo $row['name'];
}

How to push data in array.

$deptid = $data['deptid'];

$this->db->select('ID');
$this->db->from('mydept');
$this->db->where("deptid",$deptid);
$qry = $this->db->get();
$res = $qry->result();

$ar= array();
for($i=0;$i<count($res); $i++)
{
array_push($ar,$res[$i]->ID);
}
$this->db->select('*');
$this->db->from('ITECHXPERT');
$this->db->where_in('TechID', $ar,false);
$query3 = $this->db->get();
return $query3->result();

Result Helper Methods :- num_rows()

The number of rows returned by the query.

$query = $this->db->query('SELECT * FROM my_table'); echo $query->num_rows();

OR

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
echo $row->title;
echo $row->name;
echo $row->body;
}
}

num_rows()

num_fields()

The number of FIELDS (columns) returned by the query.

$query = $this->db->query('SELECT * FROM my_table');
echo $query->num_fields();

free_result()

It frees the memory associated with the result and deletes the result resource ID. Normally PHP frees its memory automatically at the end of script execution. However, if you are running a lot of queries in a particular script you might want to free the result after each query result has been generated in order to cut down on memory consumption.

$query = $this->db->query('SELECT title FROM my_table');

foreach ($query->result() as $row)
{
echo $row->title;
}

$query->free_result(); // The $query result object will no longer be available

$query2 = $this->db->query('SELECT name FROM some_table');

$row = $query2->row();
echo $row->name;
$query2->free_result(); // The $query2 result object will no longer be available

Query Helper Methods

$this->db->insert_id().
The insert ID number when performing database inserts.

$this->db->affected_rows()

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

$this->db->last_query()

Returns the last query that was run (the query string, not the result). Example: $str = $this->db->last_query();

$this->db->count_all()

Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:

echo $this->db->count_all('my_table');

How to count NO of Record in Table?

$this->db->select('*);
$this->db->from('itechxpert');
//$this->db->where('id >=', 5);
$query = $this->db->get();
echo $query->num_rows();

// Outputs, 4

How to count NO of Record in Table?

$this->db->select('*');
$this->db->from('itechxpert');
echo $this->db->count_all_results();

// Outputs, 2

How to count NO of Record?

public function getrecordCount() {

$this->db->select('count(*) as total_count');
$this->db->from('ittutorial');
$query = $this->db->get();
$result = $query->result_array();

return $result[0]['total_count'];
}

Conunt Total no of record :

public function get_all_details_count($id)
{
$this->db->select('count(it.pki_id) as total');
$this->db->from('itechxpert it');
$this->db->where('it.department_id', $id);
$this->db->where('status=', '1');
$rows = $this->db->get()->result();
return $rows;

################################ OR ##########################

/*
$this->db->from('itechxpert');
$this->db->where('id', $id);
$this->db->where('status=', '1');
$query = $this->db->get();
$rowcount = $query->num_rows();
return $rowcont;
*/

}

Conunt Total record :

public function count_all_record($id)
{
$this->db->select('count(it.pki_id) as total');
$this->db->from('itechxpert it');
$this->db->where('it.department_id', $id);
$this->db->where('status=', '1');
$this->db->where('DATE(it.added_on) >=', '2020-01-01');
$rows = $this->db->get()->result();
return $rows;

}

Helper Function :- Count no of record in table.

Count no of record in table.

if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
echo $row->EmpID;
echo $row->EmpName;
echo $row->Designation;
}
}

$this->db->insert_string()

This function simplifies the process of writing database inserts. It returns a correctly formatted SQL insert string. Example:

$data = array('name' => $name, 'email' => $email, 'url' => $url);

$str = $this->db->insert_string('table_name', $data);

$this->db->update_string()

This function simplifies the process of writing database updates. It returns a correctly formatted SQL update string. Example:

$data = array('name' => $name, 'email' => $email, 'url' => $url);

$where = "author_id = 1 AND status = 'active'";

$str = $this->db->update_string('table_name', $data, $where);

Selecting Data

The following functions allow you to build SQL SELECT statements.

$this->db->get();
$query = $this->db->get('mytable'); // Produces: SELECT * FROM mytable
this query return the all records of table mytable.

The second and third parameters enable you to set a limit and offset clause:

$query = $this->db->get('mytable', 10, 20);

// Executes: SELECT * FROM mytable LIMIT 20, 10
// (in MySQL. Other databases have slightly different syntax)

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

foreach ($query->result() as $row)
{
echo $row->title;
}

$this->db->select()

Permits you to write the SELECT portion of your query:

$this->db->select('title, content, date');
$query = $this->db->get('mytable');

// Executes: SELECT title, content, date FROM mytable

$this->db->select_max()

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

$this->db->select_max('age', 'member_age');
$query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members

$this->db->select_min()

$this->db->select_min('age'); $query = $this->db->get('members'); // Produces: SELECT MIN(age) as age FROM members

$this->db->select_avg()

$this->db->select_avg('age'); $query = $this->db->get('members'); // Produces: SELECT AVG(age) as age FROM members

$this->db->select_sum()

$this->db->select_sum('age'); $query = $this->db->get('members'); // Produces: SELECT SUM(age) as age FROM members

$this->db->from()

$this->db->select('title, content, date'); $this->db->from('mytable'); $query = $this->db->get(); // Produces: SELECT title, content, date FROM mytable

$this->db->join()

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

Controller function

public function get_noof_committee()
{

// get valu from url commttid=12,34,65,20,25
$committeeid=urldecode(base64_decode($_GET['commttid']));
$member_name = urldecode(base64_decode($_GET['membername']));

$this->data['member_name'] = $member_name;
$myString = $committeeid;
$all_committee_id = explode(',', $myString);
//print_r($all_committee_id);
$this->data['techcommittee_list'] = $this->My_model->mycommdetails($all_committee_id);
//print_r($result);
$this->load->view('report',$this->data);
}

where()

$where = "name='sana' AND status='boss' OR status='active'";
$this->db->where($where);

#################### OR #################

$where = '(name="Sana" AND status="boss" OR status="active")';
$this->db->where($where);

where_in()

public function get_all_committee_details($all_committee_id)
{

$this->db->select('*');
$this->db->from('MytechComm');
// $this->db->where('ID', $all_committee_id);
$this->db->where_in('ID', $all_committee_id);
// $this->db->where_in('ID', $all_committee_id,false);
$qry = $this->db->get();
return $res = $qry->result();
//print_r($res);exit;

}

########################### #########################

function get_tech($id)
{
$this->db->select('ID');
$this->db->from('itechxpert');
$this->db->where("ID",$id);
$query=$this->db->get();
$result=$query->result_array();
$ar = array();
foreach($result as $rowdata)
{
// $ar[] = $rowdata['ID'];
array_push($ar,$rowdata['ID']);
}
$data = $this->get_itech_list($ar);
return $data;
}

function get_itech_list($ar)
{
$course_id= implode(',',$ar);
$this->db->select('it.pki_id,it.name,it.status,itx.pk_id');
// $this->db->select('it.*,itx.pk_id');
$this->db->from('ittutorial as it');
$this->db->join('itechxpert as itx', 'itx.pk_id = it.pki_id');
$this->db->where('it.status','7');
$this->db->where_in('it.courseid',$course_id,false);
$where1 = "(itx.action != 's' OR itx.action != 'S' OR itx.action IS NULL)";
$this->db->where($where1);
$query = $this->db->get();
// echo $this->db->last_query();
$result=$query->result_array();
return $result;
}

Custom key/value method:

$this->db->where('name !=', $name);
$this->db->where('id <', $id);
// Produces: WHERE name != 'Joe' AND id < 45

Associative array method:

$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->where($array);
// Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

You can include your own operators using this method as well:

$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
$this->db->where($array);

Custom string:
You can write your own clauses manually:

$where = "name='sana' AND status='boss' OR status='active'";
$this->db->where($where);

where_in() example

$this->db->select('*');
$this->db->from('mytable');
$this->db->where(name,'Sana');
$arr = array('male', 'female');
$this->db->where_in('gender', $arr);

$this->db->distinct()

$this->db->distinct();
$this->db->get('table'); // Produces: SELECT DISTINCT * FROM table

$this->db->having()

$this->db->having('user_id = 45'); // Produces: HAVING user_id = 45
$this->db->having('user_id', 45); // Produces: HAVING user_id = 45

https://www.codeigniter.com/userguide3/database/query_builder.html

How to use GROUP BY

By using $this->db->group_by() function you can use the GROUP BY in CodeIgniter. SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange similar data into groups.

File Name :

$this->db->select('user_id, COUNT(user_id) as total');
$this->db->group_by('user_id');
$this->db->order_by('total', 'desc');
$this->db->get('tablename');

File Name :

$this->db->group_by(array("userid", "date"));

GROUP_BY Example:-

File Name :

$this->db->select('*');
$this->db->from('employees');
$this->db->where('status','Active');
$this->db->group_by('name');
$this->db->get();

GROUP_BY Example2:-

File Name :

$this->db->group_by("name");
$this->db->group_by(array("name", "date"));

order_BY Example:-

Options are ASC, DESC AND RANDOM.

File Name :

$this->db->order_by("id","ASC");
$this->db->order_by('title DESC, name ASC');

$this->db->order_by('name', 'RANDOM');
// Produces: ORDER BY RAND()

$this->db->order_by(50, 'RANDOM');
// Produces: ORDER BY RAND(50)

order_BY multiple column

File Name :

$this->db->order_by("id","ASC");
$this->db->order_by('title DESC, name ASC');

order_BY multiple column sort

File Name :

$this->db->order_by("title", "desc");
$this->db->order_by("name", "asc");
// Produces: ORDER BY title DESC, name ASC

Limit Example:-

Limit clause is used in sql queries to limit the number of rows returned from the query.

By using $this->db->limit() method you can specify the number of records to return. The limit() method makes it easy to fetch the limited number of records.

File Name :

$this->db->limit(10);

// limit with offset

$this->db->limit(10,20);
$this->db->limit($limit, $start);

The limit() method takes up two parameters - the first one defines the number of records to be fetched and the second is the offset value.

Query grouping

Query grouping allows you to create groups of WHERE clauses by enclosing them in parentheses.

File Name :

$this->db->select()
->from('users')
->where('name !=', 'mahi')
->group_start() // Open bracket
->where('age <', 37)
->or_where('id <', $id)
->group_end(); // Close bracket

group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query.

File Name :

$this->db->select('*')->from('ittutorial')
->group_start()
->where('a', 'a')
->or_group_start()
->where('b', 'b')
->where('c', 'c')
->group_end()
->group_end()
->where('d', 'd')
->get();

// Generates above query this format :
// SELECT * FROM (`ittutorial`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

group_start()

File Name :

$this->db->where('meeting_created_by','1');
$this->db->join('tbl_meeting_invite','invite_meeting_id=meeting_id','left');
$this->db->where('meeting_created_by','1');
$this->db->where('meeting_created_by','1');
$this->db->where('invite_user_id !=','');
$this->db->group_start();
$this->db->or_where('invite_user_id',$this->session->userdata('user_id'));
$this->db->group_end();
$this->db->group_by('invite_meeting_id');
$data['invited_list']=$this->db->get('tbl_meeting');

File Name :

$this->db->select(' DISTINCT(employee_no) as enumber,staff,name');
$this->db->from('staff');
$this->db->join('staff', 'staff_code = employee_no','INNER');
$this->db->join('designation', 'designation.designation_id = staff.des_id','LEFT');
$this->db->where('staff.department_id',$deptID));
$this->db->where('staff.status', 1);
$this->db->where('designation.status', 1);
$this->db->group_start();
$this->db->like('staff.name', 'nickname', 'both');
$ignore = array(74,75,76,77,79,80,81,82);
$this->db->or_where_in('designation.des_id', $ignore);
$this->db->group_end();
$this->db->order_by("staff.name ASC");
$query = $this->db->get();
// echo $this->db->last_query();die;

$rows = $query->result_array();
$this->db->close();
return $rows;

$this->db->or_group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘OR’.

File Name :

$this->db->select('*')->from('my_table')
->group_start()
->where('a', 'a')
->or_group_start()
->where('b', 'b')
->where('c', 'c')
->group_end()
->group_end()
->where('d', 'd')
->get();

// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

$this->db->not_group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘NOT’.

File Name :

$this->db->or_not_group_start()

Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with ‘OR NOT’.

File Name :

$this->db->group_end()

Ends the current group by adding an closing parenthesis to the WHERE clause of the query.

File Name :

Limit with select() statment:

File Name :

$this->db->select('*');
$this->db->from('employees');
$this->db->where('status','1');
$this->db->limit(10);
$this->db->get();

http://tutorialsmint.com/codeigniter-tutorial/codeigniter-limit

Escaping Queries

It's a very good security practice to escape your data before submitting it into your database. CodeIgniter has three methods that help you do this:

$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 table (title) VALUES(".$this->db->escape($title).")";

$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 table (title) VALUES('".$this->db->escape_str($title)."')";

$this->db->escape_like_str() This method should be used when strings are to be used in LIKE conditions so that LIKE wildcards ('%', '_') in the string are also properly escaped.
$search = '20% raise';

$sql = "SELECT id FROM table WHERE column LIKE '%".$this->db->escape_like_str($search)."%'";

Query Bindings

Bindings enable you to simplify your query syntax by letting the system put the queries together for you. Consider the following example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";

$this->db->query($sql, array(3, 'live', 'Rick'));
The question marks in the query are automatically replaced with the values in the array in the second parameter of the query function.

Filter Data

public function index()
{

$filter = $this->input->post('filter');
$field = $this->input->post('field');
$search = $this->input->post('search');

if (isset($filter) && !empty($search)) {
$this->load->model('students/Student_Model');
$data['students'] = $this->Student_Model->getStudentsWhereLike($field, $search);
} else {
$this->load->model('students/Student_Model');
$data['students'] = $this->Student_Model->getStudents();
}
}

public function getStudentsWhereLike($field, $search)
{
$query = $this->db->like($field, $search)->orderBy('registered_at')->get('students');
return $query->result();
}

Get last insert id

function saveUserInfo(){
$input = ['name'=>'Ajay Gupta', 'email'=>'mahi@gmail.com'];
$this->db->insert('users', $input);
$insertId = $this->db->insert_id();
return $insertId;
}

How to insert data in database using query

$value = "Hello itech";
$sql = "INSERT INTO tagslist(name) VALUES ('".$value."')";
$this->db->query($sql);

how to insert record in single column

$value = "Hello itech";
$this->db->set('name',$value);
$this->db->insert('tagslist');

How to merge Multiple Query in Codeigniter?

function search($str) {
$query1 = $this->db->query("YOUR QUERY");
$query2 = $this->db->query("YOUR SECOND QUERY");

$result1 = $query1->result();
$result2 = $query2->result();

return array_merge($result1, $result2); // If you want to merge both results
}

How to show unique data from Multiple array in Codeigniter?

function search($str) {
$query1 = $this->db->query("YOUR QUERY");
$query2 = $this->db->query("YOUR SECOND QUERY");

$result1 = $query1->result();
$result2 = $query2->result();

return array_unique(array_merge($result1, $result2)); // If you want to merge both results
}

Example : Merge Multiple Query in Single Query in Codeigniter?

public function mysearchdata($data)
{
$query1= array();
$query2= array();
$query3= array();
$query4= array();
$query5= array();
$query6= array();
$sql = "select * from itechxpert where 1=1 ";

if($data['qry_key'] !=''){

$query1 = $data['qry_key'];

}

if($data['grpid'] != '' || $data['subgroup'] != '' || $data['sub_subgroup'] != '')
{
$subgroupid = $data['sub_subgroup'];
if($subgroupid == 'all'){
$this->db->select('ID');
$this->db->from('SubSubGroups');
$query = $this->db->get();
$qry_result['id'] = $query->result();
$resultid = $qry_result['id'];

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'ittutorial.ISID = itechxpert.ID');
$this->db->where_in('SubSubGroupID',$resultid->ID);
$query2 = $this->db->get();
//return $result = $query2->result();

}
else{
$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'ittutorial.ISID = itechxpert.ID');
$this->db->where('SubSubGroupID',$subgroupid);
$query2 = $this->db->get();
//return $result = $query2->result();
}
}

if($data['deptid'] != '' && $data['dept_committee'] !='')
{
$committee_id = $data['dept_committee'];
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->where_in('TechCommitteeID', $committee_id);
$query3 = $this->db->get();
//return $result = $query3->result();

}

if($data['aspects'] !=''){
$aspect_id = $data['aspects'];

$this->db->select('*');
$this->db->from('ittutorial');
//$this->db->join('ittutorial', 'ittutorial.Aspect = Aspects.ID');
$this->db->join('itechxpert', 'ittutorial.ISID = itechxpert.ID');
$this->db->where_in('Aspect', $aspect_id);
$query4 = $this->db->get();
//return $query5->result();
}

if($data['ministry'] != ''){
$ministry_id = $data['ministry'];

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'ittutorial.ISID = itechxpert.ID');
$this->db->where_in('Ministry', $ministry_id);
$query5 = $this->db->get();
//return $query->result();

}

if($data['degree'] !='')
{
$degree = $data['degree'];
$this->db->select('*');
$this->db->from('itechxpert_table');
$this->db->join('itechxpert', 'itechxpert_table.isid_docid = itechxpert.ID');
$this->db->where_in('equivalence_id', $degree);
$query6 = $this->db->get();
//return $query6->result();
}

//$result1 = $query1->result_array();
//$result2 = $query2->result();
// $result3 = $query3->result();
//$result4 = $query4->result();
//$result5 = $query5->result();
//return array_merge($query2->result(), $query3->result());

if(($data['qry_key'] != '' && $data['sub_subgroup'] == '') && $data['dept_committee'] =='' && $data['aspects'] =='' && $data['ministry'] == '' && $data['degree'] =='')
{
return $query1;
}

elseif(($data['sub_subgroup'] != '') && $data['dept_committee'] =='' && $data['aspects'] =='' && $data['ministry'] == '' && $data['degree'] =='')
{
return $query2->result();
}
elseif(($data['dept_committee'] !='' && $data['sub_subgroup'] == '') && $data['aspects'] =='' && $data['ministry'] == '' && $data['degree'] =='')
{
return $query3->result();
}
// ########### ###############
elseif(($data['aspects'] !='' && $data['dept_committee'] =='' && $data['sub_subgroup'] == '') && $data['ministry'] == '' && $data['degree'] =='')
{
return $query4->result();
}
// ########### ###############
elseif(($data['ministry'] != '' && $data['aspects'] =='' && $data['dept_committee'] =='' && $data['sub_subgroup'] == '') && $data['degree'] =='')
{
return $query5->result();
}

// ########### ###############
elseif(($data['degree'] !='' && $data['ministry'] == '' && $data['aspects'] =='' && $data['dept_committee'] =='' && $data['sub_subgroup'] == ''))
{
return $query6->result();
}

// ########### execute Only Two field ###############
elseif(($data['sub_subgroup'] != '' && $data['dept_committee'] !='') && $data['aspects'] =='' && $data['ministry'] == '' && $data['degree'] =='')
{
return array_merge($query2->result(), $query3->result());
}
// ########### execute Only Three field ###############
elseif(($data['sub_subgroup'] != '' && $data['dept_committee'] !='' && $data['aspects'] !='') && $data['ministry'] == '' && $data['degree'] =='')
{
return array_merge($query2->result(), $query3->result(), $query4->result());
}

// ########### execute only four field ###############
elseif($data['sub_subgroup'] != '' && $data['dept_committee'] !='' && $data['aspects'] !='' && $data['ministry'] != '' && $data['degree'] =='')
{
return array_merge($query2->result(), $query3->result(), $query4->result(),$query5->result());
}

// ########### execute all field ###############
elseif($data['sub_subgroup'] != '' && $data['dept_committee'] !='' && $data['aspects'] !='' && $data['ministry'] != '' && $data['degree'] !='')
{
return array_merge($query2->result(), $query3->result(), $query4->result(),$query5->result(),$query6->result());
}

}

How to use Union?

UNION operator to combine two or more result sets from multiple SELECT statements into a single result set.

SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
...

the number and the orders of columns that appear in all SELECT statements must be the same.

the data types of columns must be the same or compatible.

The columns in each SELECT statement must also be in the same order

By default, the UNION operator removes duplicate rows even if you don’t specify the DISTINCT operator explicitly.

$sql = 'SELECT Name, Email FROM students UNION SELECT Name, Email FROM Teachers';
$query = $this->db->query($sql);

Union Query

$this->db->select('Name, Email');
$this->db->from('Students');
$query1 = $this->db->get_compiled_select();

$this->db->select('Name, Email');
$this->db->from('Teachers');
$query2 = $this->db->get_compiled_select();

$query = $this->db->query($query1 . ' UNION ' . $query2);

// Produces SQL:
// SELECT Name, Email FROM Students
// UNION
// SELECT Name, Email FROM Teachers;

Note :- $this->db->get_compiled_select()

Compiles the selection query just like $this->db->get() but does not run the query. This method simply returns the SQL query as a string.

Union

function get_merged_result($ids){
$this->db->select("column");
$this->db->distinct();
$this->db->from("table_name");
$this->db->where_in("id",$model_ids);
$this->db->get();
$query1 = $this->db->last_query();

$this->db->select("column2 as column");
$this->db->distinct();
$this->db->from("table_name");
$this->db->where_in("id",$model_ids);

$this->db->get();
$query2 = $this->db->last_query();
$query = $this->db->query($query1." UNION ".$query2);

return $query->result();
}

Union

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Union All Query

The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).

$query = $this->db->query($query1 . ' UNION ALL ' . $query2);

###################################################

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

it return all city of customer table and suppliers table.

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SubQuery

$this->db->select('BookID');
$this->db->from('Orders');
$sub_query = $this->db->get_compiled_select();

$this->db->select('BookID, BookName, AuthorName');
$this->db->from('Books');
$this->db->where("BookID IN ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT BookID, BookName, AuthorName
// FROM Books
// WHERE BookID IN (SELECT BookID FROM Orders);

Subquery

$this->db->select_avg('Salary');
$this->db->from('Employees');
$sub_query = $this->db->get_compiled_select();

$this->db->select('*');
$this->db->from('Employees');
$this->db->where("Salary > ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT *
// FROM Employees
// WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Like Array in CodeIgniter

$array = array('BookName' => 'Power', 'Author' => 'e', 'ISBN' => '14');
this->db->from('Books');
$this->db->like($array);
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE BookName LIKE '%Power%' AND Author LIKE '%e%' AND ISBN LIKE '%14%';

Codeigniter Multiple Like Query:

$this->db->select('*');
$this->db->from('Books');
$this->db->like('Author', 'Robert', 'after');
$this->db->like('Category', 'Self');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE Author LIKE 'Robert%' AND Category LIKE '%Self%';
$this->db->like() automatically adds the %s and escapes the string.

Query Result:

$this->db->from('Books');
$this->db->like('Category', 'Self', 'after');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE Category LIKE 'Self%';
$this->db->like() automatically adds the %s and escapes the string.

Search Query

$this->db->select('*');
$this->db->from('Books');
$this->db->like('BookName', 'Power');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE BookName LIKE '%Power%';
$this->db->like() automatically adds the %s and escapes the string.

Like

SQL LIKE Operator Description
WHERE first_name LIKE 'a%'; Finds any records start with a.
WHERE first_name LIKE '%a'; Finds any records end with a.
WHERE first_name LIKE '%a%'; Finds any records that have 'a' in any position.
WHERE first_name LIKE '%_a%'; Finds any records that have 'a' in second position.
WHERE first_name LIKE '%a_%'; Finds any records that have 'a' in any position.
WHERE first_name LIKE '%__a%'; Finds any records that have 'a' in any position.
WHERE first_name LIKE '%a_b%'; Finds any records that have 'a' in any position.

sql injection using like

$query = $this->db->escape_like_str($key);
$this->db->like('title', $query);
$res = $this->db->get('film');

like with after, before

$this->db->like('title', 'match', 'before');
// Produces: WHERE title LIKE '%match'

$this->db->like('title', 'match', 'after');
// Produces: WHERE title LIKE 'match%'

$this->db->like('title', 'match', 'both');
// Produces: WHERE title LIKE '%match%'

Advance Search

/* ================== Search ================== */

public function get_search_keyparameter($key)
{

/*
//$key = $this->db->escape($key);
$key = $this->db->escape_like_str($key);
$qry = "SELECT * FROM itechxpert WHERE (DOC_NO like '%$key%')";
$qr = $this->db->query($qry);
//echo $this->db->last_query();
return $result = $qr->result();
*/

//$key = $this->db->escape_str($key);
$key = $this->db->escape_like_str($key);
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->like('DOC_NO', $key);
$qr = $this->db->get();
return $this->db->last_query();
//return $result = $qr->result();

}
public function get_search_keyparameter2($key1,$selector1,$key2)
{
/*
$key1 = $this->db->escape_like_str($key1);
$selector1 = $this->db->escape_like_str($selector1);
$key2 = $this->db->escape_like_str($key2);
$qry = "SELECT * FROM itechxpert WHERE ( DOC_NO like '%$key1%') $selector1 (DOCUMENT_TITLE like '%$key2%')";
$qr = $this->db->query($qry);
//echo $this->db->last_query();
return $result = $qr->result();
*/

$key1 = $this->db->escape_like_str($key1);
$key2 = $this->db->escape_like_str($key2);
$selector1 = $this->db->escape_str($selector1);
if($selector1 == 'and'){
$this->db->select('*');
$this->db->from('it');
$this->db->like('docno', $key1);
$this->db->like('doctotle', $key2);
$qr = $this->db->get();
return $this->db->last_query();
//return $result = $qr->result();

}
else{
$this->db->select('ID,DOC_NO');
$this->db->from('ittutorial');
$this->db->like('DOC_NO', $key1);
$this->db->or_like('doctitle', $key2);
$qr = $this->db->get();
return $this->db->last_query();
//return $result = $qr->result();

// SELECT `ID`, `DOC_NO` FROM `itechxpert` WHERE `DOC_NO` LIKE '% 1224 %' ESCAPE '!' OR `DOC_NO` LIKE '% 1224 %' ESCAPE '!'
}

}
public function get_search_keyparameter3($key2,$selector2,$key3)
{
$key2 = $this->db->escape_like_str($key2);
$selector2 = $this->db->escape_str($selector2);
$key3 = $this->db->escape_like_str($key3);

/*
$qry = "SELECT * FROM itechxpert WHERE ( DOC_NO like '%$key2%') $selector2 (DOC_NO like '%$key3%')";
$qr = $this->db->query($qry);
//echo $this->db->last_query();
return $result = $qr->result();
*/

if($selector2 == 'and'){
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->like('doctitle', $key2);
$this->db->like('docno', $key3);
$qr = $this->db->get();
return $this->db->last_query();
//return $result = $qr->result();

// SELECT * FROM `itechxpert` WHERE `docno` LIKE '% 1224 %' ESCAPE '!' AND `docno` LIKE '% 1224 %' ESCAPE '!'

}
else{
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->like('doctitle', $key2);
$this->db->or_like('docno', $key3);
$qr = $this->db->get();
return $this->db->last_query();
//return $result = $qr->result();

}

}
public function get_search_keyparameters($key1,$key2,$key3,$selector1,$selector2)
{
$key1 = $this->db->escape_like_str($key1);
$key2 = $this->db->escape_like_str($key2);
$key3 = $this->db->escape_like_str($key3);
$selector1 = $this->db->escape_str($selector1);
$selector2 = $this->db->escape_str($selector2);

$qry = "SELECT * FROM itechxpert WHERE DOC_NO like '%$key1%' $selector1 DOCUMENT_TITLE like '%$key2%' $selector2 DOC_NO like '%$key3%'";
$qr = $this->db->query($qry);
return $this->db->last_query();
//return $result = $qr->result();

}

public function mysearch($data)
{
$query0 = array();
$query1= array();
$query2= array();
$query3= array();
$query4= array();
$query5= array();
$query6= array();
$query7= array();

// $sql = "select * from itechxpert where 1=1 and ";

$sql = "select * from itechxpert where ID=0 ";

if($data['qry_key'] !=''){

$query0 = $data['qry_key'];
$sql.= " union (".$query0.")";

}

if($data['grpid'] !='' && $data['subgroup'] !='' && $data['sub_subgroup'] =='' ){
$groupid = $data['grpid'];
$subgroupid = $data['subgroup'];

if($groupid != '' && $subgroupid == 'all'){
$this->db->select('ID');
$this->db->from('mySubGroup');
$this->db->where('GroupID',$groupid);
$query = $this->db->get();

$qry_result['sgid'] = $query->result();

$subgroupid = $qry_result['sgid'];

$ar_subgroup = array();
for($i=0;$i<count($subgroupid); $i++)
{
array_push($ar_subgroup,$subgroupid[$i]->ID);
}

$this->db->select('ID');
$this->db->from('my_Sub_Sub_Group');
$this->db->where_in('SubGroupID',$ar_subgroup);
$querysub = $this->db->get();

$sub_result['sid'] = $querysub->result();
$qry_sub_result = $sub_result['sid'];

$ar_subsubgroup = array();
for($j=0;$j<count($qry_sub_result); $j++)
{
array_push($ar_subsubgroup,$qry_sub_result[$j]->ID);
}

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where_in('SubSubGroupID',$ar_subsubgroup);
$query1 = $this->db->get();
$qry1 = $this->db->last_query();

$sql.= " union (".$qry1.")";

}
else{

$this->db->select('ID');
$this->db->from('my_Sub_Sub_Group');
$this->db->where('SubGroupID',$subgroupid);
$qry_subsubgroupid = $this->db->get();
$qrysubresult = $qry_subsubgroupid->result();

$ar_subsubgroup_id = array();
for($k=0;$k<count($qrysubresult); $k++)
{
array_push($ar_subsubgroup_id,$qrysubresult[$k]->ID);
}

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where_in('SubSubGroupID',$ar_subsubgroup_id);
$query1 = $this->db->get();
$qry1 = $this->db->last_query();

//return $result = $query2->result();
$sql.= " union (".$qry1.")";

}

}

//if($data['grpid'] != '' && $data['subgroup'] != '' && $data['sub_subgroup'] != '')
if($data['sub_subgroup'] !='')
{
//$sub_group = $data['subgroup'];
$subgroupid = $data['sub_subgroup'];

if($subgroupid == 'all'){
$this->db->select('ID');
$this->db->from('itSubSubGroup');
$query = $this->db->get();
$qry_result['id'] = $query->result();
$resultid = $qry_result['id'];

$subsubgroup_ar = array();
for($j=0;$j<count($resultid); $j++)
{
array_push($subsubgroup_ar,$resultid[$j]->ID);
}

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where_in('SubSubGroupID',$subsubgroup_ar);
$query2 = $this->db->get();
$qry2 = $this->db->last_query();

//return $result = $query2->result();
$sql.= " union (".$qry2.")";
}
else{

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where('SubSubGroupID',$subgroupid);
$query2 = $this->db->get();
$qry2 = $this->db->last_query();

//return $result = $query2->result();

$sql.= " union (".$qry2.")";

}
}

// #################### ####################

if($data['deptid'] !='' && $data['dept_committee'] =='')
{
$deptid = $data['deptid'];

if($deptid == 'all')
{
$this->db->select('ID');
$this->db->from('Tech_Departments');
$qry = $this->db->get();
$res_deptid = $qry->result();

$ar= array();
for($i=0;$i<count($res_deptid); $i++)
{
array_push($ar,$res_deptid[$i]->ID);
}

$this->db->select('ID');
$this->db->from('Tech_Comm');
$this->db->where_in("DepartmentID",$ar);
$qry_tech = $this->db->get();
$qry_tech_result = $qry_tech->result();

$ar1= array();
for($j=0;$j<count($qry_tech_result); $j++)
{
array_push($ar1,$qry_tech_result[$j]->ID);
}
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->where_in('Tech_Comm_id', $ar1);
$query3 = $this->db->get();
$qry3 = $this->db->last_query();
//return $query3->result();

$sql.= " union (".$qry3.")";

}
else
{
$this->db->select('ID');
$this->db->from('Tech_Comm');
$this->db->where("DepartmentID",$deptid);
$qry = $this->db->get();
$res = $qry->result();

$ar= array();
for($i=0;$i<count($res); $i++)
{
array_push($ar,$res[$i]->ID);
}
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->where_in('Tech_id', $ar);
$query3 = $this->db->get();
$qry3 = $this->db->last_query();
//return $query3->result();
$sql.= " union (".$qry3.")";
}

}

if($data['dept_committee'] !='')
{
$committee_id = $data['dept_committee'];
$this->db->select('*');
$this->db->from('itechxpert');
$this->db->where_in('TechCommitteeID', $committee_id);
$query3 = $this->db->get();
$qry3 = $this->db->last_query();
//return $result = $query3->result();
$sql.= " union (".$qry3.")";

}

// #################### ####################
if($data['aspects'] !=''){
$aspect_id = $data['aspects'];

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where_in('Asp', $aspect_id);
$query4 = $this->db->get();
$qry4 = $this->db->last_query();
//return $query4->result();
$sql.= " union (".$qry4.")";

}

// #################### ####################
if($data['ministry'] != ''){
$ministry_id = $data['ministry'];

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where_in('Ministry', $ministry_id);
$query5 = $this->db->get();
$qry5 = $this->db->last_query();
//return $query5->result();

$sql.= " union (".$qry5.")";

}

// #################### ####################
if($data['degree'] !='')
{
$degree = $data['degree'];
$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.isid_docid');
$this->db->where_in('equivalence_id', $degree);
$query6 = $this->db->get();
$qry6 = $this->db->last_query();
//return $query6->result();
$sql.= " union (".$qry6.")";
}
// #################### ####################
if($data['Sd_goals'] != ''){
$sdgoals = $data['Sd_goals'];
$this->db->select('fki_doc_id');
$this->db->from('itechxpert');
$this->db->where_in('fki_sdg_id',$sdgoals);
$sdg_query = $this->db->get();
$res['id'] = $sdg_query->result();
$sdggoal = $res['id'];

$ar_sdg = array();
for($j=0;$j<count($sdggoal); $j++)
{
array_push($ar_sdg,$sdggoal[$j]->fki_doc_id);
}

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('itechxpert', 'itechxpert.ID = ittutorial.ISID');
$this->db->where_in('ISID', $ar_sdg);
$query7 = $this->db->get();
$qry7 = $this->db->last_query();

//return $query7->result();
$sql.= " union (".$qry7.")";

}

//echo $sql;
$query_result = $this->db->query($sql);
return $query_result->result();

}

How to filter data using two dates.

$this->db->query("SELECT * FROM TABLE WHERE date BETWEEN '$startDate' AND '$endDate'");

##################### OR ############################
$this->db->where('order_date >=', $first_date);
$this->db->where('order_date <=', $second_date);
return $this->db->get('orders');

##################### OR ############################
$this->db->where('sell_date BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

Fetch records between date range using Codeigniter active record

public function getAllDataBetweenDates($firstDate,$secondDate){
$this->db->where('date >=', $firstDate);
$this->db->where('date <=', $secondDate);
return $this->db->get('your_table_name');
}

How to filter data between two dates.

//public function get_current_userinfo($start_datetime,$end_datetime)
public function get_current_userinfo($start_datetime)
{
$this->db->select('*');
$this->db->from('lotery_number');
$this->db->where('created_at >=', $start_datetime);
//$this->db->where('created_at <=', $end_datetime);
$this->db->order_by('id', 'desc');
$qry = $this->db->get();
return $result = $qry->result();
//echo $this->db->last_query();

//$qry = "SELECT * FROM lotery_number WHERE created_at BETWEEN $start_datetime AND $end_datetime";
//$this->db->where('created_at', BETWEEN $start_datetime AND $end_datetime);
//$result = $this->db->query($qry);
//return $result;

}

How to get column value which not null?

public function get_meeting_docfiles($meetingid)
{
$this->db->select('doc_upload_file');
$this->db->from('meeting_file_upload');
$this->db->where('meeting_id_fk', $meetingid);
$this->db->where('doc_upload_file IS NOT NULL', null, false);
$query=$this->db->get();
$result=$query->result();
return $result;
}

$this->db->where('archived IS NOT NULL');

The generated query is:
WHERE archived IS NOT NULL;

select * from donors where field is NOT NULL;

For is not null:

where('archived IS NOT NULL', null);

For is null:

where('archived', null);

FILTER_VALIDATE_EMAIL Filter

<?php
$email = "sana@itechxpert.in";

if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
echo("$email is a valid email address");
} else {
echo("$email is not a valid email address");
}
?>

Check Valid Employee EmailId

function CheckValidEmployee($email){

if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
return false;
}
}

IS NOT NULL

“NOT NULL” is the keyword that performs the Boolean comparison. It returns true if the supplied value is not NULL and false if the supplied value is null.

$this->db->select('*');
$this->db->where('contact is NOT NULL', NULL, FALSE);
$this->db->get('ittutorial');

$where = "field is NOT NULL";
$this->db->where($where);

$this->db->select('*'); $where = "ontact is NOT NULL"; $this->db->where($where); $this->db->get('ittutorial');

SELECT * FROM `members` WHERE contact_number IS NOT NULL;

IS NULL

“IS NULL” is the keyword that performs the Boolean comparison. It returns true if the supplied value is NULL and false if the supplied value is not NULL.

File Name :

SELECT * FROM `members` WHERE contact_number IS NULL;

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->where('name', $name);
$this->db->where('section IS NULL', NULL, FALSE);
// $this->db->where('section IS NULL','',TRUE);
$qry = $this->db->get();

File Name :

$customer_id = $this->input->get('id'); $this->load->model('Common_model'); $data = $this->Common_model->chat_ittutorial($id); echo $data->chat_id; public function chat_ittutorial($id)
{
$this->db->select('*');
$this->db->from('ittutorial');
$this->db->order_by('timestamp','desc');
$this->db->where('ittutorial.id',$id);
$this->db->join('chat','chat.ittutorial_id = ittutorial.id','left');
$this->db->join('chat_reply', 'chat_reply.chat_id = chat.chat_id', 'left');
$query = $this->db->get();
return $query->row();
}

How to pass array in function as parameter

$data['conditions'] = array('id'=>$id, 'status'=>1);
$this->data['comdetails'] = $this->ittutorial_model->get_myRows('tutorial',$data);

public function get_myRows(tutorial,$data){
$this->db->select('*');
$this->db->from('tutorial');
$this->db->where('id',$data['id']);
$query = $this->db->get();
}

How to call stored procedure in codeigniter?

Stored Procedure :-
CREATE DEFINER=`root`@`localhost` PROCEDURE `myprocedure`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT * FROM ittutorial WHERE name = 'sana';
END

public function pc()
{
$query = $this->db->query("CALL myprocedure()");
return $query->result();
}

call procedure in model class.

public function getTutorial_details($pid,$name,$address,$status)
{
$call_to_procedure ="CALL myprocedure('".$pid."','".$name."','".$address."','".$status."')";
$query = $this->db->query($call_to_procedure);
$result = $query->row_array();
return $result;
}

create procedure.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_user`(
IN `name` VARCHAR(255),
IN `email` VARCHAR(255),
IN `phone` BIGINT,
IN `address` VARCHAR(255)
)
BEGIN
INSERT INTO user_table(name,email,phone,address) VALUES(name,email,phone,address);
END//
DELIMITER ;

Stored procedure call in codeigniter.

class Common_model extends CI_Model {
function insert_user($name, $email, $phone, $address)
{
$insert_user_stored_proc = "CALL sp_insert_user(?, ?, ?, ?)";
$data = array('name' => $name, 'email' => $email, 'phone' => $phone, 'address' => $address);
$result = $this->db->query($insert_user_stored_proc, $data);
if ($result !== NULL) {
return TRUE;
}
return FALSE;
}
}

Join multiple table. inner, left

function get_details($id)
{
$this->db->select('tbl1.Email AS email, tbl1.userNo AS user_no, tbl1.Name as name,tbl2.email as user_email, tbl2.name as username');
$this->db->from('Tech as te');
$this->db->join('table1 as tbl1','te.ID=tbl1.DeptID','INNER');
$this->db->join('members as tbl_mem','tbl1.EmpNo=tbl_mem.emp_number','LEFT');
$this->db->where('te.ID',$id);
$this->db->where('tbl1.status',1);
$query=$this->db->get();
return $row = $query->row_array();

}

subquery with join tables

function getdetails($id){
$sql="select wp.id as id, (select code_name from Departments where ID=tc.DepID) as codename,
tc.CNumber as Com_Number, sc.sub_number as subnumber, wp.level as level,wp.position as position,
wp.sub_id as subid, wp.title as title, tc.Description as description,
sc.subtitle as sub_title
from tbl1 wp
INNER JOIN tbl2 tc ON wp.tech_id=tc.ID
LEFT JOIN tbl3 sc ON wp.sub_id=sc.id
where 1=1 AND sc.tech_comm IN(SELECT ID FROM TechComm WHERE DepID =".$this->db->escape($id).")
OR wp.tech_id IN(SELECT ID FROM TechComm WHERE DepID =".$this->db->escape($id).")
and wp.status='1' ";
$query = $this->db->query($sql);
return $rows = $query->result_array();
}

function getDetails($id)
{
$sql = "select br.title as Title, bm.role as Role, bm.gender as Gender, (select name from orgdetails where id=bm.org_id) as org_title, (select title from members where status_id=bm.status) as member_title, (select Name from Departments where ID=bm.deptid) as dept_name from members_details bm
LEFT JOIN memberRoles br ON bm.role=br.role_id
where bm.uid=".$id."";

$query = $this->db->query($sql);
$row = $query->row();
//$row = $query->row_array();
return $row;
}

use switch case in query

function getdetails($reference_id,$reference_type)
{

switch($reference_type){

case 1 :
case '1' :
$SQL = "select * from tutorial where id='".$reference_id."'";
$query = $this->db->query($SQL);
return $rows = $query->row_array();

case 2 :
case '2' :
$SQL = "your query";
$query = $this->db->query($SQL);
return $rows = $query->row_array();

case 3 :
case '3' :
$SQL = "your query";
$query = $this->db->query($SQL);
return $rows = $query->row_array();

default :
return;
}

}

Send SMS

public function sendsms($message,$str)
{
header('Content-Type: text/html; charset=UTF-8');
$username="mahi"; //username of the department
$password="sana*#12345#"; //password of the department
$senderid="tutorial";

$mobileNos= $str;
$deptSecureKey= "gbh5476df-76gf-43ec-98b9-dd3f3dcae321";

//encryption of message...
$encryp_password = sha1($password);
$key=hash('sha512', trim($username).trim($senderid).trim($message).trim($deptSecureKey));
$data = array(
"username" => $username,
"password" => $encryp_password,
"senderid" => $senderid,
"content" => $message,
"smsservicetype" =>"bulkmsg",
"bulkmobno" =>$mobileNos,
"key" => $key
);
$SmsSent = " ";
$a = $this->posttourl("https://ittutorial.in/esms/sendsmsrequest",$data);

if (preg_match('/402/',$a) )
{
$SmsSent = 1;
}

return $SmsSent;

//calling posttourl to send bulk sms
//return $a;
}

public function posttourl($url, $data)
{

$fields = '';
foreach($data as $key => $value)
{
$fields .= $key . '=' . $value . '&';
}
rtrim($fields, '&');
$post = curl_init();
curl_setopt($post, CURLOPT_URL, $url);
curl_setopt($post, CURLOPT_POST, count($data));
curl_setopt($post, CURLOPT_POSTFIELDS, $fields);
curl_setopt($post, CURLOPT_RETURNTRANSFER, 1);
$result = curl_exec($post); //result from mobile seva server

return $result;
curl_close($post);

}

Check Valid User

array_key_exists()

Check if the key exists in an array:

combined query with multiple condition

<?php $aspect=$_GET['aspect'];
$doe=$_GET['doe'];
$from=$_GET['dt_from'];
$to=$_GET['dt_to'];

$Stds=$tecDep;
$count = 1;
$stds_cnt=0;
foreach ($tecDep as $key => $value) {

$depid=$value['id'];
$dep=$value['Name'].' ('.$value['code'].')';
$depname=$value['code'];
$unclassified_count=0;
$fun_join="";
$tutorial_in="";
$tutorial_join="";

if(!empty($from) && !empty($to))
{
$dt_from=date('Y-m-d', strtotime($from));
$dt_to=date('Y-m-d', strtotime($to));
$where=" AND (DATE(created_date) BETWEEN '".$dt_from."' AND '".$dt_to."')";
}

if($aspect!='' && $aspect!=0){
$tutorial_join=" inner join tutorial s on i.pk_id=s.pid " ;
$where .=" AND s.Aspect=".$aspect;
$tutorial_in=" AND i.pk_is_id IN (select DISTINCT(uid) from tutorial s) ";
}
if($doe>0){
$fun_join=" inner join funtoosapp cis on cis.id=i.pk_is_id ";
$where .=" AND cis.option=".$doe;
}

$sql="select count(i.pk_id) as total from ittutorial i ".$fun_join." ". $tutorial_join." where i.tid IN (select ID from Techdept where DID=".$depid." AND status='1') AND COALESCE(withdraw_status,'')<>'W' ".$tutorial_in. " ".$where;

$query=$this->db->query($sql);
$res=$query4->result_array();
$total=$res[0]['total'];
$result[]=$total;

?>

call nested function in model class.

$sql="SELECT tbl.* FROM itechxpert tbl WHERE tbl.fki_id=".$id;
$query = $this->db->query($sql);
$result = $query->rusult();
$new_dara_array=array();
if($query->num_rows()>=1)
{
foreach ($result as $key => $value) {
$new_dara_array1[]=array(
'id' => $value->id,
'course_code' => $value->fki_id,
'scheme' => $value->scheme,
'plan' => $value->plan,
'fki_id' => $value->fki_id,
'category' => $value->category,
'path' => $value->path,

);
}
}
$sql1="SELECT tbl.* FROM itechxpert tbl WHERE tbl.fki_id=".$id;
$query1 = $this->db->query($sql);
$result1 = $query->rusult();
$new_dara_array2=array();
if($query1->num_rows()>=1)
{
foreach ($result1 as $key => $value) {
$new_dara_array2[]=array(
'id' => $value->id,
'course_code' => $value->fki_id,
'scheme' => $value->scheme,
'plan' => $value->plan,
'fki_id' => $value->fki_id,
'category' => $value->category,
'path' => $value->path,

);
}
}

$data_array = array(
'course_naem' =>$row->name,
'created_on' => date('d-m-Y' , strtotime($row->created_on)),
'tbl1_data' => $new_dara_array1,
'tbl2_data' => $new_dara_array2,
);
return $data_array;

$sql = "SELECT * FROM `tutorial` t
INNER JOIN itechxpert i ON i.`pk_id`=t.`t_id`
INNER JOIN `funtoosapp` fun ON fun.`fki_id`=t.`fki_id`
WHERE t.`status`=1 AND i.`techid`='".$id."' AND (t.`date`>='2022-04-01' AND t.`date`<='2023-03-31') GROUP BY t.`t_id`";
$query = $this->db->query($sql);
$result = $query->rusult();
retrun $result;

Itechtuto

Codeigniter Tutorials

codeigniter Database Query

Connecting to a Database

How to insert record in db.

How to fetch a Record in codeigniter.

Updating a Record

update multiple row in codeigniter :-

Deleting a Record :-

Codeigniter get last executed query OR Print Query.

How to get last executed query in codeigniter?

How to get last record from MySQL table in Codeigniter?

How to get last inserted id in Codeigniter?

where() :-

$this->db->query('YOUR QUERY HERE');

Query Bindings

Generating Query Results :-

Result Arrays :- result()

insert object data in single array.

result_array():-

Result Rows :- row()

Result Rows :- row_array()

Result Helper Methods :- num_rows()

num_fields()

free_result()

Query Helper Methods

$this->db->affected_rows()

$this->db->last_query()

$this->db->count_all()

$this->db->insert_string()

$this->db->update_string()

Selecting Data

$this->db->select()

$this->db->select_max()

$this->db->select_min()

$this->db->select_avg()

$this->db->select_sum()

$this->db->from()

$this->db->join()

Controller function

where()

where_in()

Custom key/value method:

Associative array method:

$this->db->distinct()

$this->db->having()

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

Escaping Queries

Query Bindings

Filter Data

Get last insert id

How to insert data in database using query

how to insert record in single column

How to merge Multiple Query in Codeigniter?

How to show unique data from Multiple array in Codeigniter?

Example : Merge Multiple Query in Single Query in Codeigniter?

File Name :

File Name :

Quick Links