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


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
Housekeeping and data used

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

Current data in the employee’s table.
Table structure for the employee’s table.

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

$db = ConfigDatabase::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)

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

Select query using where() function and key/value pair.

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)

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:

Select query from where() function using custom key/value parameter structure.

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)

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

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’)

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’)

Custom query string for the where clause.

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 4MySQL Aggregate Query using CodeIgniter’s Query BuilderEnable 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.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients.

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

How can I help you?

Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.

The Newsletter for PHP and MySQL Developers

Disclosure: Some of the services and products links in this post 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 post CodeIgniter 4 Query Builder where() function and parameter structure with MySQL appeared first on Digital Owl’s Prose.