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 …

Introduction In this article, we are going to see how we can enable the MySQL JDBC Driver Statement Caching mechanism, which, unfortunately, is not enabled by default. No matter what data access framework you are using, you still need to configure the JDBC Driver if you want to get the most out of the MySQL database engine. MySQL JDBC client-side PreparedStatement As I explained in this article, by default, the MySQL JDBC Driver emulates prepared statements. So, no matter if you are executing a plain Statement or a PreparedStatement, the SQL statement... Read More

The post MySQL JDBC Statement Caching appeared first on Vlad Mihalcea.

This is a simple article with an examples for “how to configure replication filters for channel”

Starting from MySQL 8.0 replication filters can be global or channel-specific, enabling you to configure multi-source replicas with replication filters on specific replication channels. Channel specific replication filters are particularly useful in a multi-source replication topology when the identical database or table is present on multiple sources, and the replica is only required to replicate it from one source.

Example:

CHANGE REPLICATION FILTER REPLICATE_DO_DB =(test,test3,test4 ), REPLICATE_REWRITE_DB = ((test1,test3),(test2,test4)) FOR CHANNEL "node1";

my.cnf confiration to make replication filter setting persistent:

replicate-do-db=node1:test
replicate-do-db=node1:test3
replicate-do-db=node1:test4
replicate-rewrite-db=node1:test1->test3 …

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 …

If you use SQL on a regular basis, then you are well aware that Window Functions are powerful. They allow us to simplify queries that would otherwise be quite the mess. We can provide meaningful insight across rows of data without collapsing the results into a single value. I have written numerous blog posts on Window Functions, many here recently. I decided to make this blog post a compilation of all the Window Function posts I have written, providing a one-stop source for any readers interested in learning more about Window Functions…

Image by Free-Photos 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:

…

Introduction In this article, I’m going to explain how the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table. When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type. For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to... Read More

The post MySQL JSON_TABLE – Map a JSON object to a relational database table appeared first on Vlad Mihalcea.