OpenLampTech issue #15 - CodeIgniter 4 Query Builder select() methods in depth

This month's featured piece looks at CodeIgniter 4 Query Builder select methods. There is also curated content on PHP frameworks comparison, the htaccess file, PHP benchmarks, and much more.

Feb 25, 2022

Hey glad to have you here! 👍

If someone shared this newsletter with you and you are not yet subscribed, please use the Subscribe button below and join:

Select data with CodeIgniter 4 Query Builder methods

We can't much use database data in any reporting output or displays without selecting it from the tables.

The CodeIgniter 4 Query Builder class has specific select() methods that make querying the data a breeze. I'm focusing on just those select-like methods in this month's OpenLampTech featured piece...

For the example select() queries, I am using the Sakila database 'store' table with this data:

I also have this $builder connection instance used throughout the examples and will be logging the queries sent to the server using the $db->getLastQuery() method:

$db = db_connect('sakilaGroup');
$builder = $db->table('store');

📝 Related: If you haven't used the $db->getLastQuery() method, I highly recommend giving it a try. Know exactly what queries you are sending to the server by logging the return value with the log_message() function. I use $db->getLastQuery() quite often myself and have written a high-level overview of this method. Visit the post, CodeIgniter 4 Query Helper $db->getLastQuery() method for SQL prototyping, for more information.

Query select() methods

$builder->get()

We can use the $builder->get() method to execute the most simple SELECT we likely need, a 'SELECT *' query.

📝 It is typically best practice to use 'SELECT *' types of queries only during development, exploration, and ad-hoc querying phases and not in production code.

All you need to do is call the get() method on an existing $builder connection instance:

$select_all = $builder->get();
-- produces
SELECT *
FROM `store`

You can pass in optional LIMIT and OFFSET clause values to get() as well if you need any pagination. (MySQL/MariaDB syntax).

$limit_and_offset = $builder->get(5, 2);
-- produces
SELECT *
FROM `store`
LIMIT 2, 5

$builder->getCompiledSelect()

The getCompiledSelect() method does not execute the SELECT query, but instead, returns it as a string:

$compiled_select = $builder->getCompiledSelect();

echo $compiled_select;

Visit the online getCompiledSelect() documentation for more information about optionally resetting the query builder query.

$builder->select()

You can write the SELECT portion of a query using the $builder->select() method.

The first way you can do this is by listing out the column names separated by commas - the same as you would writing the raw MySQL SELECT column list - as the parameter to select():

$builder->select('address_id, store_id, last_update');
$specific_cols = $builder->get();
-- produces
SELECT `address_id`, `store_id`, `last_update`
FROM `store`

Another option is writing the entire MySQL query and providing it as the parameter to select(). Here is an arbitrary example using the data at hand. It likely doesn't produce any real-world meaningful results and is only used for demonstration:

$builder->select('(SELECT SUM(store_id) FROM store) AS store_id_sum', false);
$custom_raw_query = $builder->get()->getResult();
-- produces
SELECT (SELECT SUM(store_id) FROM store) AS store_id_sum
FROM `store`

📝 Note: In the event that the select() method 2nd parameter is set to false, CodeIgniter attempts to avoid protecting the field names.

Aggregate select() methods

The CodeIgniter 4 Query Builder class supports select methods for the standard aggregate functions:

MAX()
MIN()
AVG()
SUM()
COUNT()

For these example queries, I'm targeting the 'payment' table, which has an 'amount' column we can get some figures on.

I've edited the $builder connection instance for the 'payment' table as well:

$builder = $db->table('payment');

$builder->selectMax()

Executing the $builder->selectMax() method with the 'amount' column as the parameter, maps to a MAX() aggregate function call on that column.

$builder->selectMax('amount');
$max_query = $builder->get();
-- produces
SELECT MAX(`amount`) AS `amount`
FROM `payment`

You can specify a column alias in the 2nd parameter:

$builder->selectMax('amount', 'max_amt');
$max_query = $builder->get();
-- produces
SELECT MAX(`amount`) AS `max_amt`
FROM `payment`

📝 Note: The 2nd parameter column alias is available for all of the aggregate select methods. In order to preserve screen space within the article, I won't include column alias examples for any of the remaining aggregate select methods.

$builder->selectMin()

The $builder->selectMin() method maps to a MIN() aggregate function call on the column parameter:

$builder->selectMin('amount');
$min_query = $builder->get();
-- produces
SELECT MIN(`amount`) AS `amount`
FROM `payment`

$builder->selectAvg()

For a mapping AVG() aggregate function call, you can use the $builder->selectAvg() method:

$builder->selectAvg('amount');
$avg_query = $builder->get();
-- produces
SELECT AVG(`amount`) AS `amount`
FROM `payment`

$builder->selectSum()

Retrieving the summation of a columns' values into a single value across the rows is possible with $builder->selectSum() method:

$builder->selectSum('amount');
$sum_query = $builder->get();
-- produces
SELECT SUM(`amount`) AS `amount`
FROM `payment`

$builder->selectCount()

If you need a count of non-NULL column values for a specific column $builder->selectCount() maps to a COUNT(column_name) aggregate function call:

$builder->selectCount('amount');
$count_query = $builder->get();
-- produces
SELECT COUNT(`amount`) AS `amount`
FROM `payment`

Try out the CodeIgniter 4 Query Builder select() methods

There are many categories of Query Builder functions and methods. In this OpenLampTech newsletter issue, I've only covered those with SELECT-like functionality and my hope is that I've provided sufficient explanations on each for anyone interested in using the CodeIgniter 4 framework.

Recently from the Digital Owl’s Prose blog

This week I published a useful and fun-to-write post on the MySQL GROUP_CONCAT() function. Read, Create a comma-separated list with MySQL GROUP_CONCAT(), for more details…

I have curated some fantastic reads for you this week. Let’s get to them!

This Week’s Picks

✔️ Which PHP Framework Is Right for You? CodeIgniter vs. CakePHP vs. Laravel vs. Yii

#php #yii #laravel #cakephp #codeigniter

I first read this post shared in the PHP Weekly newsletter.

There is no shortage of articles like this on the internet and lord knows, I've read my share of them.

But, this one is different

This one is worth your time.

In this comparison of 4 major PHP frameworks: Yii, Laravel, CakePHP, and CodeIgniter, each one is reviewed for 7 key sections focusing on a particular aspect:

Trend & Popularity
Structure & Update
Libraries & Online help
Database Integration
Ease of Development & Templating
Things to Like about
Things to Dislike about

In addition to your individual project-specific needs, if there is an article that can help you decide which PHP framework you should use, it is probably this one.

Enjoy...

➡️ Read this post…

✔️ Deploy MySQL and phpMyAdmin with Docker

#php #mysql

I don't have a lot of experience with Docker, but, I am quite familiar with phpMyAdmin. In this post, you will learn how to install and configure a docker container for MySQL and one for phpMyAdmin. Then, connect the 2 containers.

Best practices are covered for the phpMyAdmin container by creating a dedicated user instead of working with the root account.

Solid and well-written post.

➡️ Read this post…

✔️ What is the htaccess file

#apache

To be such an integral component of (mostly) all the LAMP stack applications we work on and develop, the .htaccess file - and Apache in general - sure do get pushed over in the corner (in my opinion).

Well, here is a fantastic resource to learn about this key file and the role it plays.

This blog post has a ton of information in it about the .htaccess file and includes a nice table of server variables and parameters with the meaning of each.

A lot to learn here...

➡️ Read this post…

Do you need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don't know where to start. Learn more about my premium MySQL blog posts and content as I develop and release them.

Tell me more!

✔️ The Definitive PHP 7.2, 7.3, 7.4, 8.0, and 8.1 Benchmarks (2022)

#php

Once again Kinsta has published another in-depth article. This one is benchmarking different PHP versions on several prominent PHP frameworks such as Laravel, WordPress, CakePHP, October CMS, and many more.

All the numbers are here folks.

➡️ Read this post…

✔️ How To Submit An Associative Array With HTML & PHP

#php

You can learn a lot about PHP arrays, submitting them in an HTML form, and what they look like on the backend in this post. Form $_POST requests with multiple arrays can be tricky and this article has good examples of how to process them.

➡️ Read this post…

✔️ Introducing MySQL: A Beginner’s Guide

#php #mysql

This article is actually an excerpt from the updated 7th edition of one of my absolute favorite books to learn PHP development; PHP & MySQL: Novice to Ninja, 7th Edition. I'm actually working on a book recommendation blog post (for the 6th edition as I haven't gotten an updated copy of the 7th edition yet) over on my technical blog, Digital Owl's Prose.

I can't say enough about this book and its impact on my overall PHP development learning and understanding. It literally made me want to focus on PHP as my language of choice - which I did.

This article provides an excellent tone and feel of the book overall. Learn how to install and set up MySQL, along with some basics such as inserting, deleting, and updating data.

➡️ Read this post…

⭐⭐⭐ Many of the posts I curate and share in this newsletter, I read and enjoyed on Medium. If you are not yet and member and wish to join the platform, I would be more than grateful if you would use my sign-up link. At no extra cost to you, I will receive a small portion of your membership fee when you sign up. Thank you! 🙏

Support OpenLampTech, my blog, and my work with a coffee if you would like.☕

Newsletter Shout out 📣

PHP Weekly

Extra stuff…

I invite you to join Refind. Every day Refind picks 7 links that make you smarter, tailored to your interests, curated from 10,000+ sources. Subscribe for free using my invite link.

Did you enjoy this issue of OpenLampTech? Please share it with others so that they can enjoy the content as well. Thank you!!!

“Reply” or comment and let me know what you think of this issue of OpenLampTech.

What do you want to see more of?!?

What do you want to see less of?!?

I’d love to know!

Also, share with me links to your published blogs and articles so that I can read them and possibly include them here in the newsletter!

Thank you for reading. I hope you have a great rest of your week.

Take care.

Joshua Otwell

Visit my blog Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, and SQL. I’m also active on Medium, LinkedIn, Twitter, and Instagram.

Share OpenLamp.tech

How can I help you?

Are you thinking of starting up a blog? I use WordPress for my blog, Digital Owl’s Prose. 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 with my referral link.
Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.

Want to support this newsletter and my work? Drop some spare change in my Tip Jar.💰

Tip Jar

Disclosure: Some of the services and products links in this email 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.

Cover photo creds: Image by OpenClipart-Vectors from Pixabay