select Query In Codeigniter Example Tutorial

Today, We want to share with you select Query In Codeigniter Example Tutorial.In this post we will show you join query in codeigniter, hear for delete query in codeigniter we will give you demo and example for implement.In this post, we will learn about Insert Query In Codeigniter Example Tutorial with an example.

select Query In Codeigniter Example Tutorial

There are the Following The simple About join in codeigniter Full Information With Example and source code.

As I will cover this Post with live Working example to develop codeigniter join query, so the codeigniter insert query is used for this example is following below.

Retrieve data from database using CodeIgniter framework

Codeigniter supports many functions for select data for MysqL database. You will learn all about of Codeigniter CRUD Related the functions for retreving a data on MysqL database using select query with multiple clause.

select Query In Codeigniter Example Tutorial
select Query In Codeigniter Example Tutorial

Codeigniter (CRUD) Select Insert Update Delete Query

  1. Update Query In Codeigniter
  2. Select Query In Codeigniter
  3. Insert Query In Codeigniter
  4. Delete Query In Codeigniter

using $this->db->query()

To submit a query, use the following function:

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

place raw SQL in the query() method

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

Pass SQL query in a variable

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

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

Note:Use Of Query Bindings

futures of using all the data binds is that the some values are automatically fast quickly escaped, producing safer statements

$sql = "SELECT * FROM tbl_doctor WHERE name = ? AND type = ?"; 
$this->db->query($sql, array('medical', 'amitshah'));

using Active Record Query

Using $this->db->get()

Use the built-in get() method

Read Also:  Count Query Codeigniter Tutorial

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

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

Format 1

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

Format 2

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

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

Example 1

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

Example 2

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

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

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

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

$this->db->select('id, name');
$this->db->from('tbl_doctor');
$query = $this->db->get();
//SELECT `id`, `name` FROM (`tbl_doctor`) LIMIT 10, 20;

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

Example 1

$this->db->select('doctorname');
$this->db->from('tbl_doctor');
$this->db->where('doctorid',11);
$this->db->where("doctortype","admin");
$query=$this->db->get();
//SELECT `doctorname` FROM (`tbl_doctor`) WHERE `doctorid` = 11 AND `doctortype` = 'admin'

Example 2

$this->db->select('doctorname');
$this->db->from('tbl_doctor');
$this->db->where('doctorid',11);
$this->db->where("doctortype !=","admin");
$query=$this->db->get();
//SELECT `doctorname` FROM (`tbl_doctor`) WHERE `doctorid` = 11 AND `doctortype` != 'admin'

Example 3:

$this->db->select('doctorname');
$this->db->from('tbl_doctor');
$this->db->where("doctorid > 11 AND doctortype='doctor'");
$query=$this->db->get();
//SELECT `doctorname` FROM (`tbl_doctor`) WHERE `doctorid` > 11 AND doctortype='doctor'

Example 4:

$this->db->from('tbl_doctor');
$this->db->where('doctorname !=', $name);
$this->db->or_where('doctorid >', $doctorid); 
//SELECT * FROM (`tbl_doctor`) WHERE doctorname != 'admin' OR doctorid > 5

Example 5:

$names = array('drfname', 'drlname', 'drsirname');
$this->db->from('tbl_doctor');
$this->db->where('is_active !=', $is_active);
$this->db->or_where_in('doctorname', $names);
//SELECT * FROM (`tbl_doctor`) WHERE is_active='active' OR doctorname IN ('drfname', 'drlname', 'drsirname')

Example 6:

$names = array('drfname', 'drlname', 'drsirname');
$this->db->from('tbl_doctor');
$this->db->where_not_in('doctorname', $names);
//SELECT * FROM (`tbl_doctor`) WHERE doctorname NOT IN ('drfname', 'drlname', 'drsirname')

Example 7:

$names = array('drfname', 'drlname', 'drsirname');
$this->db->from('tbl_doctor');
$this->db->where('is_active !=', $is_active);
$this->db->or_where_not_in('doctorname', $names);
//SELECT * FROM (`tbl_doctor`) WHERE is_active='active' OR doctorname NOT IN ('drfname', 'drlname', 'drsirname')

Using $this->db->get_where()

Use the built-in get() method

$is_active='admin';
$options=array('doctortype'=>$is_active);
$query =  $this->db->get_where('tbl_doctor',$options);
//get_where with limit
$limit=10;
$query =  $this->db->get_where('tbl_doctor',$options,$limit);

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

Example 1:

$this->db->select('doctorname');
$this->db->from('tbl_doctor');
$this->db->like("doctorname","code");
$query=$this->db->get();
//SELECT `doctorname` FROM (`tbl_doctor`) WHERE `doctorname` LIKE '%code%'

Example 2:

$this->db->select('doctorname,doctorid');
$this->db->from('tbl_doctor');
$this->db->like('doctorname','code');
$this->db->or_like('doctortype','admin');
$query=$this->db->get();
//SELECT `doctorname`, `doctorid` FROM (`tbl_doctor`) WHERE `doctorname` LIKE '%code%' OR `doctortype` LIKE '%admin%'

Example 3:

$this->db->select('doctorname,doctorid');
$this->db->from('tbl_doctor');
$this->db->not_like('doctorname','code');
$this->db->or_like('doctortype','admin');
$query=$this->db->get();
//SELECT `doctorname`, `doctorid` FROM (`tbl_doctor`) WHERE `doctorname` NOT LIKE '%code%' OR `doctortype` LIKE '%admin%'

Example 4:

$this->db->select('doctorname,doctorid');
$this->db->from('tbl_doctor');
$this->db->where('is_active','active');
$this->db->or_not_like('doctorname','code');
$query=$this->db->get();
//SELECT `doctorname`, `doctorid` FROM (`tbl_doctor`) WHERE `is_active` = 'active' OR `doctorname` NOT LIKE '%code%'

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

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

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

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

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

Example 1:

$this->db->select('doctorname');
$this->db->from('tbl_doctor');
$this->db->order_by('doctorname');
$query=$this->db->get();
//SELECT `doctorname` FROM (`tbl_doctor`) ORDER BY `doctorname`

Example 2:

$this->db->select('doctorname');
$this->db->from('tbl_doctor');
$this->db->order_by('doctorname','desc');
$query=$this->db->get();
//SELECT `doctorname` FROM (`tbl_doctor`) ORDER BY `doctorname` desc

Mathematics Function in Codeigniter

Using $this->db->select_max()

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

$this->db->select_max('rate', 'doctorrate');
$query = $this->db->get('tbl_doctor');
//SELECT MAX(rate) as doctorrate FROM tbl_doctor

Using $this->db->select_min()

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

Using $this->db->select_sum()

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

Using $this->db->join()

Available codeigniter Main joins types in mysql:

Read Also:  Find all ASP.NET loop through controls on Form Clear of specific type

Like as a left join, right join, outer join, inner join, left outer join, as well as right outer join.

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

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

Using $this->db->num_rows()

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

Using $this->db->num_fields()

The number of total MySQL Databse Table FIELDS (columns) returned by the query. Create sure to call the methods using your query statements result object:

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

Query Result With result()

$query_str="SELECT * FROM tbl_doctor t limit 50";
$query=$this->db->query($query_str);
foreach ($query- >result() as $row)
{  
	echo $row-> name;  
	echo $row-> rate;  
	echo $row-> team_name; 
}            

Query Result With result_array()

$query_str="SELECT * FROM tbl_doctor t limit 50";
$query=$this->db->query($query_str);

foreach ($query->result_array() as $row)
{  
	echo $row['name'];
	echo $row['gag'];
	echo $row['team_name'];
}

Query With Single Result()

$query_str="SELECT * FROM tbl_doctor t limit 50";
$query=$this->db->query($query_str);
// fetch one row data
$record=$query->row();
echo $record->name;
echo $record->rate;
echo $record->team_name;
// alternative method 
$record=$query->row_array();
echo $record['name'];
echo $record['rate'];
echo $record['team_name'];

Count Of Query Result

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

Read Also:  codeigniter print last query - PHP Examples

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

In this example we will all about how to get a rows or data from MySQL database using PHP ?Based CodeIgniter framework

application\controllers\Member.php

<?php  
class Member extends CI_Controller 
{
	public function __construct()
	{

		parent::__construct();

		$this->load->database();
		

		$this->load->model('Member_model');
	}

	public function displaydata()
	{
		$result['data']=$this->Member_model->display_records();
		$this->load->view('display_records',$result);
	}
	
}
?>

application\models\Member_model.php

<?php
class Member_model extends CI_Model 
{

	function display_records()
	{
		$query=$this->db->query("select * from members");
		return $query->result();
	}
	
} 

application\views\insert.php

<html>
<head>
<title>Display records</title>
</head>
 
<body>
<table width="600" border="1" cellspacing="5" cellpadding="5">
  <tr style="background:#CCC">
    <th>Sr No</th>
    <th>Member First_name</th>
    <th>Member Last_name</th>
    <th>Member Email Id</th>
	<th>Member Delete</th>
	<th>Member Update</th>
  </tr>
  <?php
  $i=1;
  foreach($data as $member)
  {
  echo "<tr>";
  echo "<td>".$i."</td>";
  echo "<td>".$member->first_name."</td>";
  echo "<td>".$member->last_name."</td>";
  echo "<td>".$member->email."</td>";
  echo "</tr>";
  $i++;
  }
   ?>
</table>

Web Programming Tutorials Example with Demo

Read :

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about codeigniter delete query.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.