select query with where clause in codeigniter - CodeIgniter Select Query - how to run Select Query in CodeIgniter

1 of the Best select query with where clause in codeigniter – CodeIgniter

by

CodeIgniter Select Query – how to run Select Query in CodeIgniter

In this post we will show you how to run CodeIgniter Select Query, we give you differnet method for run Select Query in CodeIgniter.

how to run CodeIgniter Select Query, group_by, With Query Bindings, Select The Fields, With Limit, With Offset,Limit, With select, from, With Where, Or_Where, Where_In, or_where_in, where_not_in, With Like, or_like, not_like, or_not_like,With group_by, With having, With Order BY, select_max(), select_min(), select_sum(), join(), num_rows(), num_fields(), With result(), result_array(), Query With Single Result(), Count Of Query Result.

CodeIgniter ‘SELECT’ query will execute using following functions. They are

See also :  PHP - import excel file into mysql database tutorial

$this->db->query()
$this->db->query() With Query Bindings
$this->db->get()
$this->db->get() Select The Fields
$this->db->get() With Limit
$this->db->get() With Offset,Limit
$this->db->get() With select, from
$this->db->get() 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
$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 Select Query :: $this->db->query()

To submit/run this query, we use the following function:

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

place raw SQL in the query() method

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

Pass SQL query in a variable

// simple select query
$sql_query = "select * from tb_all_user";
$get_query = $this->db->query($sql_query);

CodeIgniter Select Query :: $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

// SELECT query WHERE name AND type
$sql_query = "SELECT * FROM tb_all_user WHERE name = ? AND type = ?";
// execut query 
$this->db->query($sql_query, array('code', 'php'));

CodeIgniter Select Query :: Active Record Query

CodeIgniter Select Query :: $this->db->get()

Use the built-in get() method

// get users data 
$get_query = $this-> db-> get('users');
// get employees data 
$get_query = $this-> db-> get('employees');

CodeIgniter Select Query :: $this->db->get() Select The Fields

Format 1

// get users data 
$this->db->select('name');
// execut query 
$get_query = $this-> db-> get('tb_all_user');

Format 2

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

CodeIgniter Select Query :: $this->db->get() With Limit

Program 1

$limit = 15;
// execut query with limit
$get_query = $this-> db-> get('tb_all_user',$limit);

Program 2

// get id,name
$this->db->select('id, name');

$this->db->from('tb_all_user');
// limit is 1
$this->db->limit(1);

$get_query = $this-> db-> get();
// limit is 1 and get id,name
//select id, name from tb_all_user limit 1;

CodeIgniter Select Query :: $this->db->get() With Offset,Limit

// limit 15, 25
$limit  = 15;
$offset = 25;

// set limit and offset - 15, 25 
$get_query = $this-> db-> get('tb_all_user',$offset,$limit);

// query work somthing like this 
// select * from tb_all_user limit 15, 25
$this->db->get() With select, from

// get id,name
$this->db->select('id, name');
// pass table name
$this->db->from('tb_all_user');
// execut query with limit
$get_query = $this->db->get();

// SELECT `id`, `name` FROM (`tb_all_user`) LIMIT 15, 25;

CodeIgniter Select Query :: $this->db->get() With Where, Or_Where, Where_In, or_where_in, where_not_in

Program 1

// get username data 
$this->db->select('username');

$this->db->from('tb_all_user');

$this->db->where('userid',17);
// get username where is admin
$this->db->where("usertype","admin");

$get_query=$this->db->get();
// get username where is admin
//SELECT `username` FROM (`tb_all_user`) WHERE `userid` = 17 AND `usertype` = 'admin'

Program 2

$this->db->select('username');

$this->db->from('tb_all_user');

$this->db->where('userid',17);

$this->db->where("usertype !=","admin");

$get_query=$this->db->get();

//SELECT `username` FROM (`tb_all_user`) WHERE `userid` = 17 AND `usertype` != 'admin'

Program 3:

$this->db->select('username');

$this->db->from('tb_all_user');

$this->db->where("userid > 17 AND usertype='users'");

$get_query=$this->db->get();

//SELECT `username` FROM (`tb_all_user`) WHERE `userid` > 17 AND usertype='users'

Program 4:

$this->db->from('tb_all_user');

$this->db->where('username !=', $name);

$this->db->or_where('userid >', $userid);

//SELECT * FROM (`tb_all_user`) WHERE username != 'admin' OR userid > 6

Program 5:

$user_names = array('user_name1', 'user_name2', 'user_name3');

$this->db->from('tb_all_user');

$this->db->where('status !=', $status);

$this->db->or_where_in('username', $user_names);

//SELECT * FROM (`tb_all_user`) WHERE status='active' OR username IN ('user_name1', 'user_name2', 'user_name3')

Program 6:

$user_names = array('user_name1', 'user_name2', 'user_name3');

$this->db->from('tb_all_user');

$this->db->where_not_in('username', $user_names);

//SELECT * FROM (`tb_all_user`) WHERE username NOT IN ('user_name1', 'user_name2', 'user_name3')

Program 7:

$user_names = array('user_name1', 'user_name2', 'user_name3');

$this->db->from('tb_all_user');

$this->db->where('status !=', $status_user);

$this->db->or_where_not_in('username', $user_names);

//SELECT * FROM (`tb_all_user`) WHERE status = 'active' OR username NOT IN ('user_name1', 'user_name2', 'user_name3')
$this->db->get_where()

CodeIgniter Select Query :: Use the built-in get() method

$status_user = 'admin';

$options = array('usertype'=>$status_user);

$get_query =  $this->db->get_where('tb_all_user',$options);

//get_where with limit

$limit = 15;

$get_query =  $this->db->get_where('tb_all_user',$options,$limit);
$this->db->get() With Like, or_like, not_like, or_not_like

Program 1:

$this->db->select('username');

$this->db->from('tb_all_user');

$this->db->like("username","code");

$get_query=$this->db->get();

//SELECT `username` FROM (`tb_all_user`) WHERE `username` LIKE '%code%'

Program 2:

$this->db->select('username,userid');

$this->db->from('tb_all_user');

$this->db->like('username','code');

$this->db->or_like('usertype','admin');

$get_query=$this->db->get();

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

Program 3:

$this->db->select('username,userid');

$this->db->from('tb_all_user');

$this->db->not_like('username','code');

$this->db->or_like('usertype','admin');

$get_query=$this->db->get();

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

Program 4:

$this->db->select('username,userid');

$this->db->from('tb_all_user');

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

$this->db->or_not_like('username','code');

$get_query=$this->db->get();

//SELECT `username`, `userid` FROM (`tb_all_user`) WHERE `status` = 'active' OR `username` NOT LIKE '%code%'

CodeIgniter Select Query :: $this->db->get() With group_by

 

$this->db->select('*');

$this->db->from('tb_all_user');

$this->db->group_by("states");

//SELECT * FROM (`tb_all_user`) group by states

CodeIgniter Select Query :: $this->db->get() With having

$this->db->select('*');

$this->db->from('tb_all_user');

$this->db->having("states=1");

//SELECT * FROM (`tb_all_user`) HAVING states=1

CodeIgniter Select Query :: $this->db->get() With Order BY

Program 1:

$this->db->select('username');

$this->db->from('tb_all_user');

$this->db->order_by('username');

$get_query = $this->db->get();

//SELECT `username` FROM (`tb_all_user`) ORDER BY `username`

Program 2:

$this->db->select('username');

$this->db->from('tb_all_user');

$this->db->order_by('username','desc');

$get_query=$this->db->get();

//SELECT `username` FROM (`tb_all_user`) ORDER BY `username` desc
Mathematics Function

CodeIgniter Select Query :: $this->db->select_max()

$this->db->select_max('age');

$get_query = $this->db->get('tb_all_user');

//SELECT MAX(age) as age FROM tb_all_user

$this->db->select_max('age', 'userage');

$get_query = $this->db->get('tb_all_user');

//SELECT MAX(age) as userage FROM tb_all_user

CodeIgniter Select Query :: $this->db->select_min()

$this->db->select_min('age');

$get_query = $this->db->get('tb_all_user');

//SELECT MIN(age) as age FROM tb_all_user

CodeIgniter Select Query :: $this->db->select_sum()

$this->db->select_sum('age');

$get_query = $this->db->get('tb_all_user');
// select query for SUM for age
//SELECT SUM(age) as age FROM tb_all_user

CodeIgniter Select Query ::$this->db->join()

hear Available joins types in mysql for left, right, outer, inner, left outer, and right outer in codeginiter.

$this->db->select('*');

$this->db->from('comments');

$this->db->join('tb_all_user', 'tb_all_user.id = comments.id');

$get_query = $this->db->get();
// select query 
// SELECT * FROM comments
// for JOIN table
// JOIN tb_all_user ON tb_all_user.id = comments.id

CodeIgniter Select Query ::$this->db->num_rows()

// select query 
$select_query = "SELECT * FROM tb_all_user t limit 50";

$get_query = $this->db->query($select_query);

if(query->num_rows()){

    // get data 

}

CodeIgniter Select Query ::$this->db->num_fields()

The number of FIELDS (columns) returned by the query. Make sure to call the function using your query result object:

$get_query = $this->db->query('SELECT * FROM tb_all_user');

echo $get_query->num_fields();

CodeIgniter Select Query ::Query Result With result()

$select_query = "SELECT * FROM tb_all_user t limit 50";

$get_query = $this->db->query($select_query);

foreach($query- >result() as $row_val)

{ 

    echo $row_val->name; 

    echo $row_val->age; 

    echo $row_val->groupname;

}

CodeIgniter Select Query ::Query Result With result_array()

$select_query = "SELECT * FROM tb_all_user t limit 50";

$get_query=$this->db->query($select_query); 

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

CodeIgniter Select Query ::Query With Single Result()

$select_query = "SELECT * FROM tb_all_user t limit 50";

$get_query=$this->db->query($select_query);

// fetch one row data

$get_record = $query->row();

echo $get_record->name;

echo $get_record->age;

echo $get_record->groupname;

// alternative method

$get_record = $get_query->row_array();

echo $get_record['name'];

echo $get_record['age'];

echo $get_record['groupname'];

CodeIgniter Select Query ::Count Of Query Result

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

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

// Produces an integer, like 1523
I hope it can help you... & Thanks for reading...
See also :  get domain name in php - Extract domain, path etc from a full url with PHP

Related posts:

  1. Advantages of php – Top 10 Fundamental PHP Advantages And Disadvantages
  2. code for upload image in php – How to Upload Files on Server in PHP?
  3. how to find Get Current year php in 2024
  4. Best 6 step to login with facebook and google in php

Leave a Comment

ItSolutionStuck

ItSolutionstuck website focuses on all articles, news updates, Laravel, mobile, how-to, software, MySQL, hardware, video, security, product reviews and up-to-date source for technology news.

Categorys

Blog

Facebook

Instagram

YouTube

Site Links

About Us

Disclaimer

Contact Us

Privacy Policy

© ItSolutionStuck | All rights reserved

Privacy Policy | Disclaimer