OpenLamp.tech issue #4
OpenLamp.tech issue #4
MySQL geospatial function queries; Use MySQL to generate MySQL; WordPress privileges best practices; MySQL cursors and for loops
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:
Once again, I have curated some fantastic reads for you this week. Let’s get to them!
This Week’s Picks
✔️ Efficient Distance Querying in MySQL
#mysql #laravel
Boy oh boy, talk about right up my alley! This one surely is!
This post is so interesting and full of useful information for someone like me who works with geospatial data daily in my day job. I'm currently prototyping an application/set of processes (for the 1/3 of the time that I do actually get to write PHP and MySQL) to use in my work, in which I'm going to need to filter some rows with MySQL geospatial functions (or something comparable). Without a doubt, I will be referencing (and rereading) this blog post!
Great examples, explanations, and thinking behind optimizing large queries and using the EXPLAIN command to dig into the query and see how the optimizer is doing things.
Also, using MySQL Generated Columns so that they can be indexed and change Range-based queries (BETWEEN) to equality-based (IN()) are key takeaways from this post.
✔️ Using cursors and for loops in MySQL
#mysql
Stored Procedure and Function programming feels to me like one of those skills that don't get the attention it deserves. This is the dark art folks. The stuff of legend, going on behind the scenes. Making things work...
I do have a lot of respect for MySQL (or any SQL) server programming. Having recently created a Stored Procedure to maintain summary table information in a web application, I can attest to how powerful Stored Procedures are.
In this post, you learn about the do and don'ts of cursors, important keywords, necessary variable declarations, and the characteristics of a cursor. A simple example stored procedure using a cursor is covered and goes over the pros and cons of cursors and what they may be used for.
✔️ 3 Ways To Implement The Principle Of Least Privilege On Your WordPress Site
#wordpress
Even without any of the latest WordPress security issues (at the time of writing), security should be a constant on everyone's mind. In this day in age, everything is fair game and should be treated as such.
This post covers 3 (sensible) approaches you can take to tighten down the security of your WordPress site by implementing the least privileges.
In the general sense, they are:
Limit WordPress write access
Remove file editing capabilities
Know who has what permissions (and adjust accordingly)
✔️ Understanding lockForUpdate and sharedLock in Laravel
#laravel #mysql
This post has several relatable examples that I actually understand, putting me that much more closer to wrapping my head around Transactions and locking in MySQL.
More importantly, how things can go bad without them and why to use locking (in some situations). Makes use of the Laravel lockForUpdate() method, which prevents specific records from being modified or selected by other shared locks.
Plenty of good stuff here.
✔️ Use MySQL to generate MySQL for Admin tasks
#mysql
I recently learned how to use MySQL to generate MySQL statements for several admin-type Data Definition Language (DDL) SQL queries I needed to execute on the server. For many DBA’s this is likely nothing new and in fact, is a well-used tool in their kit. However, I mostly focus on developer-related tasks, so it is a fresh-in-my-mind learning experience.
Published recently over on my blog, Digital Owl's Prose.
✔️ Code With Dary YouTube Channel
#php
I normally share written content in the OpenLamp.tech newsletter, but I must share these 3 videos by the Code With Dary YouTube channel.
I didn’t fully understand Model-View-Controller (MVC) design until I watched and learned from these videos. They really helped put it all together for me and I am sure they will for you as well if you are trying to grasp the concepts. These videos are indeed fantastic!
⭐⭐⭐ 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! 🙏
Extra stuff…
Get syntax highlighting right with Torchlight.
15 tools you can use to help grow your blog.
Build WordPress themes using this plugin. No coding is required.
Did you enjoy this issue of OpenLamp.tech? Please share it with others so that they can enjoy the content as well. Thank you!!!
Please “Reply” and let me know what you think of this issue of OpenLamp.tech.
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.
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.
Cover photo creds: Image by Gerd Altmann from Pixabay