ittutorial : php tutorial, tutorials for jquery, javascript, ajax and more

Codeigniter Tutorials

Home » Codeigniter »

How to check query execution time in mysql?

mysql> set profiling=1;
mysql> select * from Itechtuto;
mysql> select * from ItechStud;
mysql> show profiles;

How to check query execution time in php?

<?php

//Record the start time before the query is executed.
$started = microtime(true);

//Execute your SQL query.
mysql_query("SELECT id, name FROM users LIMIT 5");

//Record the end time after the query has finished running.
$end = microtime(true);

//Calculate the difference in microseconds.
$difference = $end - $started;

//Format the time so that it only shows 10 decimal places.
$queryTime = number_format($difference, 10);

//Print out the seconds it took for the query to execute.
echo "SQL query took $queryTime seconds.";

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

$started = microtime(true);

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

$end = microtime(true);
$difference = $end - $started;
$queryTime = number_format($difference, 10);

echo "Query execution Time = " .$queryTime. "seconds.";

return $result;

How to import database on local xampp server using command?

step 1 : download old database such as itechtuto.sql
setp 2 : paste it at this path mysql bin directory
c:\>xampp\mysql\bin
step 3 : open cmd
c:\>cd xampp\mysql\bin
c:\>cd xampp\mysql\bin> mysql -u root -p itechDb < itechtuto.sql

Backup of database

mysql> mysqldump -u itechxpert -p db_name > /tmp/db_backup_name.sql

mysql> mysqldump -u root -p db_name > D:\myfile\db_backup_name.sql

$this->db->query()

$this->db->query('YOUR QUERY HERE');
$query = $this->db->query("select * from tbl_user");

Pass SQL query in a variable

$sql = "select * from tbl_user";
$query = $this->db->query($sql);

$this->db->query()

public function check_email_exists($email)
{
$sql = "select * from users where email = '".$this->db->escape_str($email)."'";
$query = $this->db->query($sql);
$rows = $query->result_array();
return $rows;
}

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

Note: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('code', 'php'));

Active Record Query
$this->db->get()

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

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

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

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

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

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

$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

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

$this->db->get() With Where, Or_Where, Where_In, or_where_in, where_not_in

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

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

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

$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

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

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

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

Multiple where Clause

public function posts($user_id, $reply_id, $post_id) {
$this->db->from('post');
$this->db->where('post_id', $post_id);
$this->db->where('reply_id', $reply_id);
$this->db->where('user_id', $user_id);
$query = $this->db->get();

if ($query->num_rows() > 0) {
//return $query->num_rows();
return true;
} else {
return false;
}
}

$this->db->get_where()

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

$this->db->get() With Like, or_like, not_like, or_not_like

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

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

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

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

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

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

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

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

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

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

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

$this->db->select_min()

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

$this->db->select_sum()

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

$this->db->join()

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

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

Query Result With result()

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

Query Result With result_array()

$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'];
}

Query With Single Result()

$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

echo $this->db->count_all('tbl_user');
// Produces an integer, like 1522

CodeIgniter – Insert Query

$this->db->query()
Insert With Query Bindings
Itechxpert Insert
$this->db->insert_string()
$this->db->insert_batch()
Escaping Insert Queries
Get Inserted ID
Get Affected Rows

CodeIgniter – Insert Query

$this->db->query()

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

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

CodeIgniter – Insert Multiple Record

$data = array(
array(
'name' => 'itechxpert',
'contact_no'=> '8888899999',
'email' => 'support@itechxpert.com'
),
array(
'name' => 'trendeelook.com',
'contact_no'=> '868767867',
'email' => 'info@trendeelook.com'
),
),
$this->db->insert_batch('users', $data);

CodeIgniter – Insert String

$data = array(
'name' => 'itechxpert',
'contact_no'=> '8888899999',
'email' => 'support@itechxperrt.com'
);
$this->db->insert_string('users', $data);

Get Inserted ID

$data = array(
'name' => 'itechcxpertr',
'contact_no'=> '4765756687',
'email' => 'support@itechxpert.com'
);
$this->db->insert('users', $data);
return $this->db->insert_id();

Howt to Get last Inserted ID

$data_action=array(
'id'=>$id,
'action_type' =>$action_type,
'source_ip_address'=>$_SERVER['REMOTE_ADDR'],
'status'=>1,
'created_on'=>date('Y-m-d H:i:s'),
'created_by'=>$uid
);
$action_last_insertid = $this->Sdo_regist_model->Insert($table='itechxpert',$data_action);

// model function
function Insert($table,$data){

if($table == ""){

return "Table not specified";

}
if(empty($data) || (!is_array($data)) || (sizeof($data) < 1)){
return "No data is available to be processed";
}

$result=$this->db->insert($table,$data);

if($result){

return $this->db->insert_id();
}else{

return $result;

}
}

Fetch All Record by ID

public function FetchAllRowById($table, $field, $id){

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

Fetch All Row single

public function FetchAllRowsingle($table, $field){

$this->itech_db = $this->load->database('itechxpert', TRUE);

$this->itech_db->select('*');
$this->itech_db->from($table);
$this->itech_db->where($field);
$query=$this->itech_db->get();

if(!$query)
{
$db_error= $this->itech_db->error();
log_message('error: ',$db_error);

throw new Exception("Database error! Error Code [" . $db_error['code'] . "] Error Message: " . str_replace("'",'"', $db_error['message']) ) ;
return false;
}
else
{
$result=$query->row_array();
return $result;
}
}

Delete Row

Insert With Query Bindings

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

Insert

$sql = "INSERT INTO tbl_user (name, groupname, age)
VALUES (".$this->db->escape($name).", ".$this->db->escape($groupname).".", ".$this->db->escape($age).")";
$this->db->query($sql);

$this->db->insert_string()

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

$this->db->insert_batch()

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

Escaping Insert Queries

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

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

Get Inserted ID

$this->db->insert_id()

Get Affected Rows

$this->db->affected_rows();

CodeIgniter – Update Query

$this->db->update()

$data = array(
'name' = > $_POST['name'] ,
'groupname'= > $_POST['groupname'],
'age' = > $_POST['age']
);
$this->db->where('id', $_POST['id']);
$this->db->update('tbl_user', $data);
return $this->db->affected_rows();

$this->db->update_string()

$data = array(
'groupname'= >$_POST['groupname']
);
$where = "status = 'user'";
$str = $this->db->update_string('tbl_user', $data, $where);

$this->db->update_batch()

$data = array(
array(
'name' = > 'name1' ,
'groupname'= > 'groupname1',
'age' = > 'age1'
),
array(
'name' = > 'name2' ,
'groupname'= > 'groupname2',
'age' = > 'age2'
)
);
$this->db->update_batch('tbl_user', $data);

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

public function receipt_voucher($data,$debit_id,$credit_id)
{
$this->db->insert('voucher',$data);

$dr_amount = $data['debit_amount'];
$cr_amount = $data['credit_amount'];

$data = array(
array(
'id' => $debit_id,
'dr_amount' => $dr_amount

),
array(
'id' => $credit_id,
'cr_amount' => $cr_amount
)
);

$this->db->update_batch('ledger_details', $data, 'id');
// $this->db->update_batch('mytable', $data, 'where_key'); }

How to Delete Row in codeigniter?

$this->db->delete('tbl_user', array('id' => $id));
//DELETE FROM tbl_user WHERE id = $id

$this->db->where('id', $id);
$this->db->delete('tbl_user');
//DELETE FROM tbl_user WHERE id = $id
return $this->db->affected_rows();

$id=5;
$tables = array('table1', 'table2', 'table3');
$this->db->where('id', $id);
$this->db->delete($tables);

return $this->db->affected_rows();

$this->db->empty_table()

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

$this->db->truncate()

$this->db->from('tbl_user');
$this->db->truncate();
(OR)
$this->db->truncate('tbl_user');
// TRUNCATE table tbl_user;

Delete With Join

$this->db->from("table1");
$this->db->join("table2", "table1.t1_id = table2.t2_id");
$this->db->where("table2.t2_id", $id);
$this->db->delete("table1");

Fetch Single Row

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();
//echo "<pre>";
//print_r($result);
return $result;
}

Fetch All Rows

public function FetchAllRow($table){

$this->db->select('*');
$query=$this->db->get($table);
//return $query->result();
return $query->result_array();
}

How to execute multiple query?

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";
//echo $sql;
$query_result = $this->db->query($sql);
return $query_result->result();

Here See more mysql query

https://www.tutsmake.com/select-query-in-codeigniter-with-multiple-clause/

Trigger :-

$this->db->query("
CREATE TRIGGER `delete_post` BEFORE DELETE ON `posts`\r\n
FOR EACH ROW BEGIN\r\n
DELETE FROM page_content WHERE page_content.post_id = OLD.post_id;\r\n
END\r\n
//\r\n
");

Search Available Rooms

function searchFreeRooms($data){
$check_in = $data['fields']['check_in'];
$check_out = $data['fields']['check_out'];
$query1 = $this->db->query("select rooms from res_hotel where (check_in <= '$check_in' AND check_out >= '$check_in') OR (check_in <= '$check_out' AND check_out >= '$check_out') OR (check_in >= '$check_in' AND check_out <= '$check_out' )");
$query1_result = $query1->result();
$room_id= array();
foreach($query1_result as $row){
$room_id[] = $row->rooms;
}
$room = implode(",",$room_id);
$ids = explode(",", $room);
$this->db->select("*");
$this->db->from('core_hotel_rooms');
$this->db->where_not_in('id', $ids);
$query = $this->db->get();
return $query->result();
}

Itechtuto

Codeigniter Tutorials

$this->db->query()

$this->db->query()

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

Active Record Query $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() With Where, Or_Where, Where_In, or_where_in, where_not_in

$this->db->get_where()

$this->db->get() With Like, or_like, not_like, or_not_like

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

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

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

Mathematics Function $this->db->select_max()

$this->db->select_min()

$this->db->select_sum()

$this->db->join()

$this->db->num_rows()

$this->db->num_fields()

Query Result With result()

Query Result With result_array()

Query With Single Result()

Count Of Query Result

CodeIgniter – Insert Query

CodeIgniter – Insert Query

CodeIgniter – Insert Multiple Record

CodeIgniter – Insert String

Get Inserted ID

Howt to Get last Inserted ID

Fetch All Record by ID

Fetch All Row single

Delete Row

Insert With Query Bindings

Insert

$this->db->insert_string()

$this->db->insert_batch()

Escaping Insert Queries

Get Inserted ID

Get Affected Rows

CodeIgniter – Update Query

$this->db->update_string()

$this->db->update_batch()

$this->db->empty_table()

$this->db->truncate()

Delete With Join

Here See more mysql query

Trigger :-

Quick Links

Active Record Query
$this->db->get()

Mathematics Function
$this->db->select_max()