chevron_left
660 points
6 2 1

Select query with where condition in CodeIgniter.
Photo by Markus Spiske

In SQL, the `WHERE` clause filters the rows returned by the `FROM` clause table of a `SELECT` query using one or more search criteria filters. Application development often accepts user input values. This is a conditional filter in the `WHERE` clause of the table column. This post demonstrates CodeIgniter 4 where() function and parameter binding for safer filtering in `SELECT` queries. Read on to see the `MySQL` example to have a better understanding.

What is the Codelgniter select query? #

This tutorial shows how Codeigniter retrieves data from a database. A SELECT operation is also called a READ and belongs to the CRUD process of the database. Codeigniter has a comprehensive query-building interface for building complex, database-independent queries. The Active Records library also provides a set of functions that form a wide range of selected queries for reading data from the database.

This is the general format for writing select query in code igniter.

$this->db->select('column1, column2');
$this->db->from('table_name');
$this->db->where('column', 'value');
$query = $this->db->get();

The function $this->db->select(); lets you write the select portion of the SQL query i.e, to select the columns to be fetched from database. $this->db->from(); allows you to specify from which table you want to fetch data. $this->db->where(); helps to write the where clause of the select query. There are multiple variations of this where() function in Codeigniter and let's see them later in this tutorial. $this->db->get(); executes the select query and returns the resultset.

Select query with where condition in CodeIgniter. #

CodeIgniter gives you access to the query builder class. Using this pattern, you can retrieve, insert, and update information in your database with minimal scripting. In some cases, just one or two lines of code are required to perform a database action. CodeIgniter does not require each database table to be its own class file. Instead, it offers a simplified interface. The main advantage of using the Query Builder functionality, besides its simplicity, is that you can use it to create database-independent applications. This is because the query syntax is generated by each database adapter. Also, the system automatically masks the values, allowing for safer queries.

Here is a select query with a where clause that retrieves data of a specific record from the given table.

$this->db->select('EmpID, EmpName, Designation');
$this->db->from('Employees');
$this->db->where('EmpID', 2);
$query = $this->db->get();

Alternatively you can ditch from() method and pass the 'table_name' as parameter to $this->db->get() function itself.

$this->db->select('*');
$this->db->where('Department', 'Finance');
$query = $this->db->get('Employees');

Please note that if you want to retrieve all columns in select(), then you can skip select statement.

The method get_where() is the combination of get() and where() and takes up an array of where conditioning as second parameter.

$query = $this->db->get_where('Employees', array('EmpName' => 'Colleen Hurst'));

The Solution #

There are several ways to provide parameters to the CodeIgniter 4 where() function. One of the simplest is a simple key-value pair. If you haven't used this method before, I highly recommend using it to understand the queries you're running. Simply specify a target table column as the first parameter and a corresponding value to filter against that column as the second parameter.

Solution one

You can apply multiple Where clauses in a single select query. Under Codeigniter, the selected query example uses multiple where clauses combined with the AND keyword.

$this->db->where('DeptName', 'Sales');
$this->db->where('Salary >=', 100000);
$query = $this->db->get('Employees');

If you want to use OR keyword in-between where conditions, then use $this->db->or_where(); instead of where().
$this->db->where('DeptName', 'Sales');
$this->db->or_where('Designation', 'Manager');
$query = $this->db->get('Employees');

Now we will pass Array to Where Clause for Multiple Conditions:

$array = array('Department' => 'HQ', 'Designation !=' => 'Manager', 'Salary <' => 100000);
$this->db->where($array);
$query = $this->db->get('Employees');
Using the where_in() function allows you to all fetch records that have matching field values present in the provided list.
$department = array('HQ', 'Finance', 'Software');
$this->db->where_in('DeptName', $department);
$query = $this->db->get('Employees');
On the other hand where_not_in() function allows you to fetch records that don't have the matching field value present in the list provided.
$id = array(2, 3, 8);
$this->db->where_not_in('EmpID', $id);
$query = $this->db->get('Employees');

Solution two

On the other hand, you can use the where_not_in() function to retrieve records that do not contain matching field values in the specified list.

$id = array(2, 3, 8);
$this->db->where_not_in('EmpID', $id);
$query = $this->db->get('Employees');

You can form select query with like clause using $this->db->like(); function. Here is a query example with a like clause.

$this->db->from('Employees');
$this->db->like('EmpName', 's', after);
$query = $this->db->get();

This query selects all employee records with 'EmpName' starting with the character 's'. The like() function has an optional third parameter that takes up 'before', 'after', or 'both' keys to control the placement of the '%' wildcard character in the matching string.

Method $this->db->limit(); should be used to add a limit clause in a select query and controls the number of records returned by the query.

$this->db->select('*');
$this->db->from('Employees');
$this->db->limit(5, 10);
$query = $this->db->get();

The Conclusion

Codeigniter Select Queries with Multiple Clauses.Today we shared how to use multiple clauses in Codeigniter select queries. Codeigniter offers many features for selecting data in your database. Learn the most important and basic functions to retrieve data into the database using multiple clauses. We always welcome your valuable feedback, questions, and comments on this article. If you like this post and like it, don't forget to like it.

If you read this far, tweet to the author to show them you care. Tweet a Thanks

More Posts

Creating dynamic next/previous buttons with PHP and MySQL AnkurRanpariyav - Aug 4
Calculate sum (total) of column in php Bushra Rubab - Sep 8
How to filter records from the database with dropdown using PHP Codeigniter AnkurRanpariyav - Aug 5
How to generate pdf file from dynamic data coming from MySQL database in PHP AnkurRanpariyav - Aug 3
How to get checked and unchecked checkbox array values in PHP AnkurRanpariyav - Aug 2
There are 4 bytes in an ipv4 address. what is the highest decimal value you can have for one byte? James - Jun 1
How to save pdf file in the folder using php. James - May 31
How to get full path of uploaded file in html using JavaScript James - May 31
How to get mac address of client machine in PHP James - May 31
Error 1452: cannot add or update a child row: a foreign key constraint fails Ankur Ranpariya 1 - Aug 28
Error 1045 (28000): Access denied for user zhteja - Aug 22
Error 1045 (28000): access denied for user 'odbc'@'localhost' (using password: no) zhteja - Aug 19
Detected Resolved Migration not Applied to Database Hussain Zafar - Jun 14
How to do Increment value by 1 in select in SQL? Bushra Rubab - Sep 7
Given a string, return a string where for every char in the original, there are two chars. Ankur Ranpariya - Jul 19
How to tell what version of PHP a site is running? NoirHusky - Sep 12
Your server is running PHP version 5.4.45 but WordPress 5.2 requires at least 5.6.20 Ankur Ranpariya 1 - Aug 20
Jquery autocompletes multiple fields using jquery ajax PHP and MySQL AnkurRanpariyav - Aug 4
PHP notice: use of undefined constant Tsiyon Regassa - Jun 5
The configuration file now needs a secret passphrase (blowfish_secret). James - Jun 1