CodeIgniter 4 Query Builder where() function and parameter structure with MySQL

In SQL, the WHERE clause filters rows returned by the FROM clause table in SELECT queries using one or more search condition filters. Oftentimes, in application development, we accept user input values which in turn, are the WHERE clause conditional filters against the table columns. In this post, I am covering the CodeIgniter 4 where() function and parameter binding for safer filtering in SELECT queries. Continue reading and see examples in MySQL…

---

Get tailored articles with Refind delivered each day in your inbox. Refind is part of my daily reading habit. Make it part of yours by using my referral link. At no additional cost to you, I will be eligible for a premium subscription with more sign-ups from my link. “The essence of the web, every morning in your inbox. Subscribe for free“

---

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

---

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Housekeeping and data used

For the examples in this post, I am using a mock ’employees’ table having this data and structure:

I’m also using this Query Builder connection instance to the ’employees’ table for the query examples in this post:

$db = \Config\Database::connect();
$builder = $db->table('employees');

I’ll share the exact queries that are sent to the server via logging information from the $db->getLastQuery() method for a better understanding of each of the query examples. If you haven’t used this method yet, I highly recommend using it to understand the executed queries. I wrote all about it in the post, CodeIgniter 4 Query Helper $db->getLastQuery() method for SQL prototyping.

CodeIgniter 4 Query Builder where(): Key values

There are several different ways we can provide parameters to the CodeIgniter 4 where() function. One of the most straightforward is a simple key/value pair.

Simply specify a target table column as the first parameter and a corresponding value to filter against that column as the second parameter:

$id = 3;
$employee = $builder->select('first_name')
                     ->where('id', $id)
                     ->get()>getResult();

The above builder pattern returns this SELECT query as logged (not shown) by $db->getLastQuery():

Notice that this particular CodeIgniter 4 where() function key/value structure results in an equality comparison using the = comparison operator. This is the default unless you use a different comparison operator in the key parameter.

CodeIgniter 4 Query Builder where(): Custom key values

This next alternative where() function parameter syntax provides a great deal of flexibility for the key/value structure, allowing the actual comparison operator to be part of the key itself in the first parameter.

$id = 3;
$employee = $builder->select('first_name, last_name')
		    ->where('id <', $id)
		    ->get()->getResult();

We can see the key, ‘id <‘ uses the less-than comparison operator, resulting in a SELECT query filtering for rows with an ‘id’ column value of less than 3 as shown in this executed MySQL query code:

Chaining one or more additional where() function calls forms an AND logical operator type of query:

$id = 3;
$first_name = 'Jimmy';
$employee = $builder->select('first_name, last_name')
		    ->where('id <', $id)
		    ->where('first_name <>', $first_name)
		    ->get()->getResult();

(Note: Additional where() function chaining is valid in all forms of the parameter structure and is not limited to only custom key/value filtering.)

---

Get your WordPress website or web application up and running with a hosting plan over on Hostinger. I use Hostinger hosting for all of my personal web development applications and projects. The pricing tiers are some of the best in the industry and the service is unparallel to any other. Get your hosting now using my referral link. At absolutely no extra cost to you, I will receive a commission should you purchase one of Hostinger’s hosting plans through this link. Thank you!

---

CodeIgniter 4 Query Builder where(): Associative array

The CodeIgniter 4 where() function parameter structure can use an associative array for the filtering conditional.

Create an associative array with key/value pairings and pass that array as a parameter in the where() function call. Again, the key is a column in the table we are filtering on:

$data_array = array('id' => $id);
$employee = $builder->select('first_name, last_name')
		    ->where($data_array)
		    ->get()->getResult();

The associative array can have multiple key/value pairs, including the comparison operator itself as part of the key. Notice in this next example, the <> (not equals to) comparison operator is part of the ‘first_name’ key in the 2nd element of the $data_array associative array:

$id = 3;
$first_name = 'Jimmy';
$data_array = array('id' => $id, 'first_name <>' => $first_name);
$employee = $builder->select('first_name, last_name')
		    ->where($data_array)
		    ->get()->getResult();

---

Support my blog and content with a donation to my Tip Jar. Thank you so much!

---

CodeIgniter 4 Query Builder where(): Custom string

The last parameter structure we will visit is creating your own custom string for the CodeIgniter 4 where() function. As noted in the where() function documentation, you must manually escape any user-provided input when creating custom query strings for the where() function. In the where() function examples up to this point, the input parameters have been automatically escaped. However, when you write custom query strings as the where() function parameter, the input is not automatically escaped.

Here is an example using the $db->escape() method to escape the $first_name variable value of ‘John’:

$first_name = $db->escape('John');
$where_clause = "`first_name` = {$first_name}";
$employee = $builder->select('first_name, last_name')
		    ->where($where_clause)
		    ->get()->getResult();

With several options to build up the WHERE clause conditionals for your queries using the where() function, CodeIgniter provides a flexible means to retrieve the exact data you need.

Similar content

If you enjoyed the content in this post, you may also like any one of these related articles:

• How To Retrieve MySQL Last Insert ID in CodeIgniter 4
• MySQL Aggregate Query using CodeIgniter’s Query Builder
• Enable File Attachment with CodeIgniter 4 form helper

---

Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!!

---

As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading.

Thank you for reading this post. Please share it with someone else who would enjoy it as well.

---

Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.

Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.

More ways I can help

• Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none.
• 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
• Desktop and mobile wallpapers, digital downloads, photography services, Shopify and WooCommerce customizations, and content writing – all in one E-commerce Shop. Find your next digital purchase today!
• Take your Laravel applications next level with Battle Ready Laravel by Ash Allen. Learn how to improve the performance, maintainability, and security of your Laravel projects in this e-book.

Disclosure: Some of this blog post’s services and product links are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

---

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Disclosure: Some of this blog post’s services and product links are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.