OpenLampTech issue #11 - MySQL LAG() Window Function
OpenLampTech issue #11 - MySQL LAG() Window Function
Installing phpMyAdmin; jQuery in WordPress; LAG() window function; CodeIgniter 4 design patterns; Arch Linux Handbook; JSON in MySQL
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:
The LAG() Window Function
This month’s deep-dive featured piece is on the LAG() Window function. You must be using MySQL version 8 or higher to use LAG().
I recently learned of a handy use for LAG() from the phenomenal book, SQL Cookbook: Query Solutions and Techniques for All SQL Users, (this is not an affiliate link) of which I have a print edition copy on my desk and a copy on my Kindle reader - yea it is that good.
In this use case, we are using LAG() to suppress repeating column values.
I’ll cover a simple example below to give you an idea of how to use it and I’ll leave the exploring up to you and your curiosity.
I am using an arbitrary ‘friends’ table for the example. We can get the table name, all column names, and the columns’ individual position within the table by executing this query against the INFORMATION_SCHEMA database COLUMNS table (with a user that has permissions):
INFORMATION_SCHEMA COLUMNS table query.Which returns this result set:
table_name, column_name, and ordinal_postion from the COLUMNS table in the MySQL INFORMATION_SCHEMA database.We can use LAG() - along with a CASE expression - to suppress the repeating TABLE_NAME column, ‘friends’ value:
LAG() Window function to suppress repeating values.The above query produces this result set:
I can see where using LAG() like this might be handy if you needed all of this type of information for every table in a particular schema, then you could remove the repeating table name for each group.
I hope you enjoyed this LAG() use case brief overview. Let me know of some of the handy ways you have used LAG().
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.
I have curated some fantastic reads for you this week. Let’s get to them!
This Week’s Picks
✔️ How to Install phpMyAdmin on Any Operating System
#php #mysql
Web developers everywhere use phpMyAdmin. I use it all the time myself. Between phpMyAdmin and MySQL Workbench, I can get pretty much any MySQL-related task done. The Kinsta blog publishes fantastic content and this post is another example of their quality articles.
In this post, learn how to install phpMyAdmin on Windows, Mac, and several Linux distributions.
✔️ How To Properly Add jQuery Scripts To WordPress
#wordpress
I am so grateful that jQuery exists. I hear that jQuery catches a lot of flack but let's be honest here, for someone like me who struggles with Javascript, it is a godsend.
WPMUDEV has a great blog post here covering:
jQuery compatibility in WordPress
How to load jQuery with the
wp_enqueue_script()functionDe-register WordPress's version of jQuery in order to use a different version
✔️ 7 Best Free Form Builder Plugins for WordPress
#wordpress
The biggest issue I have with form builders in WordPress is which one to use! There are so many good ones out there and they all work quite well. On my technical blog, Digital Owl's Prose, I use the Contact Form 7 plugin. I also have learned a good bit about the WPForms plugin and do like it.
If you're not sure which WordPress form builder plugin to use, this blog post is a great place to start, covering 7 popular choices with the pros and cons of each.
✔️ How CodeIgniter 4 Uses Design Patterns internally
#codeigniter #php
As I have mentioned in the past, I don't normally share videos in the OpenLampTech newsletter. However, I wanted to share this particular video since it provides a good breakdown of some of the design patterns used in the CodeIgniter 4 core framework. I will absolutely lay claim that I don't necessarily know or understand all of these design patterns, but it is interesting and educational anyways to learn of them and put them on my radar of things to pick up as go along on this PHP developer journey.
✔️ How to work with JSON data in MySQL
#mysql
With support for native JSON data types, MySQL makes it easy to work with this popular data format. There are many built-in JSON-specific functions in addition to type validation.
This post has example queries for extracting specific data from a JSON document using those functions designed for it.
✔️ What Does WordPress Full Site Editing Mean for Freelancers?
#wordpress
A good overview of Full Site Editing and block themes, both of which will be an integral part of WordPress (likely by the time of this newsletter's publishing).
Here's a quote from the article’s author:
"Block themes are not only structured differently than their classic counterparts, but the contents of the templates inside are also vastly changed. They eschew PHP in favor of HTML and require knowledge of Gutenberg’s block markup."
⭐⭐⭐ 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! 🙏
Recently from the Digital Owl’s Prose blog
I reposted a Medium-first article I published on how to export query results to CSV with MySQL Workbench. It is super easy and if you are not sure how to, read this post for a simple example.
Support OpenLampTech, my blog, and my work with a coffee if you would like. ☕
Extra stuff…
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.
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.💰
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.