Most developers use some form of auto-incrementing integer counter for a given database table, ensuring uniqueness among the rows. Several of the popular SQL dialects implement this facility. For instance, MySQL’s AUTO_INCREMENT attribute is used to provide a unique identity for a table row. What exactly is the behavior of AUTO_INCREMENT? Can you explicitly use a value of your choosing for it if you need to? How does it count? Continue reading and know the answers to these questions and more…

Image by Gerd Altmann from …

I recently published a blog post about a portfolio project I am developing using the LAMP stack, Bootstrap 4, jQuery, and the MVC (Model-View-Controller) design pattern in core PHP. In this post, I will introduce an additional feature I integrated into the existing project.

Image by xresch from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and …

I am super pleased to share that I have completed and uploaded my first (that I can share at least) personal portfolio piece written in PHP to a subdomain on my personal hosting server located at walk.openlamp.tech. Over the better part of the last year, I have developed a custom reporting dashboard written in PHP for my (current) employer, but do not share any of that work as it is proprietary and not owned by me. However, for a personal project, I can share far and wide. In this post, I provide a brief overview of my simple (in theory at least) application/site, built on the LAMP stack using the MVC (Model-View-Controller) design pattern in core PHP along with Bootstrap 4, jQuery, and MySQL.

Image by …

Having spent the majority of this year programming a custom reporting dashboard for my employer written in PHP and MySQL, it is an understatement to say that I have learned a lot. Nothing beats real-world experience and I really have come into my own with PHP, learning more and more daily as I continue progressing forward on my goal of becoming a Back-End Web Developer. I have written several PHP-related posts this year and wanted to provide them all in one easy-to-access list. Enjoy!

Image by Aleksey Nemiro from …

Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…

Image by …

Rolling sum and average query results are possible by combining the aggregate functions SUM() or AVG() with the OVER() clause, making for powerful analytic queries. I recently learned how to compute a rolling average or sum of values by using the Windowing option of the OVER() clause, applying the concepts to a data set I am familiar with. I wanted to share my learning with any readers who might be interested…

Image by Steve Buissinne from …

The Window Ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK() each rank rows with an increasing integer value. I wrote a previous blog post, ROW_NUMBER() Window Function – find duplicate values, where I covered how the ROW_NUMBER() window function can be used to target any duplicate rows, with the use of the PARTITION BY clause in the OVER() clause. In this post, I cover the differences between RANK(), and DENSE_RANK() in handling any ties according to the sorting performed by the ORDER BY clause with regards to the assigned increasing integer. Continue reading and see examples…

…

Many times, we do not want duplicate rows or values in our SQL tables. On the other hand, in some situations, it does not matter if there are duplicates present. For whatever reason, suppose duplicates have found their way into one of your tables. How can you find them quickly and easily? The ROW_NUMBER() Window function is a fantastic tool to use. Continue reading and see example queries you can apply to your own tables and find those duplicates…

Photo by Joe Green on Unsplash

OS and DB used:

…

These days, I mostly program in PHP with MySQL as the database, which is just fine by me. I have had a long-time interest in MySQL (SQL in general), and after several steady months of programming in PHP, I must say I have really come into the language and developed a fondness for it. All that being said, I still enjoy using and learning the MySQL Shell in Python mode. As Database Developers, we often need the LAST INSERT ID value from a previous INSERT statement on a column that has the AUTO_INCREMENT attribute. MySQL Shell has a get_auto_increment_value() method we can call against a Shell object result and retrieve that value. Continue reading and see examples of the MySQL Shell get_auto_increment_value() method used in Python mode…

…

PHP provides a handy function, empty(), that is used to determine whether a variable is empty. Perhaps that is a bit confusing to someone unfamiliar with the empty() function and I can see how. In this blog post, I will cover: what empty() means in PHP, what the empty() function does, and a use case pairing up empty() with the PHP ternary operator conditional construct. Both used in combination with the MySQL NULL value. Continue reading and see examples of empty()…

Photo by Debby Hudson on …