Category: Help

Get Row Level Difference Between Two Tables in MySQL

Learn how to compare two MySQL tables row-by-row using JOINs and dynamic SQL to identify field-level differences efficiently.

To check row level differences between two records from two different tables in MySQL, where you want to see which fields have changed, follow these steps:

Using JOIN with CASE to Identify Row Level Differences in MySQL

You can compare each column individually to check row level difference and mark which ones have changed using this MySQL query.

SELECT
a.id,
CASE WHEN a.column1 = b.column1 THEN 'No Change' ELSE 'Changed' END AS column1_diff,
CASE WHEN a.column2 = b.column2 THEN 'No Change' ELSE 'Changed' END AS column2_diff,
CASE WHEN a.column3 = b.column3 THEN 'No Change' ELSE 'Changed' END AS column3_diff
FROM table1 a
JOIN table2 b ON a.id = b.id;

You can add as many columns as you want.

What this does:

  • Compares each field individually.
  • Marks "Changed" if different, otherwise "No Change".

Want to learn more about MySQL? Get MySQL book from https://amzn.to/45JXmH0

Dynamic Query for Large Tables

If you have many columns and don’t want to manually compare each, you can generate a query dynamically using MySQL Information Schema:

SELECT CONCAT(
'SELECT id, ',
GROUP_CONCAT(
'CASE WHEN t1.', COLUMN_NAME, ' <> t2.', COLUMN_NAME,
' THEN "', COLUMN_NAME, ' changed" ELSE "No Change" END AS ', COLUMN_NAME SEPARATOR ', '
),
' FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id'
) AS query_text
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' AND COLUMN_NAME != 'id';

This will generate a query that automatically checks differences for all columns. Execute the generated query and you will get the differences, where all cells are marked with "Changed" or "No Change".

Conclusion

These queries can help you identify difference between two tables cell by cell. It can be useful in many ways and reduce your so much time to identify a small difference in large data.

Do you stuck with any such problem? You can write me in the comments.

Disclaimer: This post contains affiliate links. If you use these links to buy something, I may earn a commission at no extra cost to you.

Git Commands to Remove Merged or Nonexistent Local Branches

Discover git commands to delete local branches that have been removed from remote or merged into the main branch.

Over time, your local git branches list can become overwhelming, particularly if you develop on a single branch, generate a pull request, merge it into the main branch, and then remove the remote git branch once it has been merged. After the branch is removed from the remote repository, there is no need to keep it on your local machine.

The following git command will delete all local branches that have been merged into the main branch. If your git trunk branch is not named main or you wish to remove all branches that have been merged into a branch other than main, simply modify the two instances of the word main in the command to reflect the name of your branch.

List of git commands to remove local merged git branches

To remove all the local branches, which are merged into the main branch, navigate to the root of the repository and run the following git commands,

  • Fetch the latest updates from the git repository
git fetch
  • See the list of local branches available in the repository
git branch
  • Delete all local branches that have been merged to main branch 
git branch --merged main | grep -v "^\* main" | xargs -n 1 -r git branch -d

Explanation:

This command is a combination of several Git commands and shell commands that work together to delete all the local Git branches that have been merged into the main branch, except for the main branch itself.

Here’s a breakdown of the individual commands and what they do:

  1. git branch --merged main: This command lists all the local branches that have been merged into the main branch. The --merged option tells Git to only list branches that have been fully merged into main.
  2. grep -v "^\* main": This command filters out the main branch from the list of branches. The -v option tells grep to invert the match, i.e., show only the lines that do not match the pattern. The pattern in this case is "^\* main", which matches lines that start with an asterisk (\*) followed by the text main.
  3. xargs -n 1 -r git branch -d: This command passes the list of merged branches (excluding main) to the git branch -d command, which deletes each branch. The -n 1 option tells xargs to pass one branch name at a time to the git branch -d command. The -r option tells xargs to only run the git branch -d command if there is at least one branch name to pass to it. The -d option tells Git to delete the branches, but only if they have been fully merged into the current branch (in this case, main). Note that this option will fail if the branch has unmerged changes, in which case the -D option could be used instead to force-delete the branch.

Want to learn such useful commands? Get a Mastering Git book from https://amzn.to/3Hp527B

List of git commands to remove local nonexistent git branches

Similarly, run the following git commands to remove all the deleted branches from the local computer:

  • Fetch the latest updates from the git repository
git fetch
  • See the list of local branches available in the repository
git branch
  • Delete all local branches that have been merged to main branch 
git branch -vv | grep ': gone]' | grep -v '\*' | awk '{ print $1; }' | xargs -r git branch -d

Explanation:

This command is a combination of several commands in a shell pipeline that work together to delete Git branches that are marked as "gone".

Here’s a breakdown of the individual commands and what they do:

  1. git branch -vv: This command lists all the Git branches in the local repository, with additional information about their upstream branches (if any). The -vv option adds more information about whether each branch is “up to date”, “behind”, or “ahead” of its upstream branch.
  2. grep ': gone]': This command filters the output of the git branch -vv command to only show the branches that are marked as "gone". The grep command searches for lines that contain the text ": gone]", which indicates that a branch is gone.
  3. grep -v '\*': This command further filters the output to exclude any branches that are currently checked out (indicated by the asterisk symbol). The -v option tells grep to invert the match, i.e., show only the lines that do not contain an asterisk.
  4. awk '{ print $1; }': This command extracts only the branch names from the filtered output. The awk command splits each line of input into fields and prints only the first field, which is the branch name.
  5. xargs -r git branch -d: This command passes the branch names to the git branch -d command, which deletes each branch. The -r option tells xargs to only run the git branch -d command if there is at least one branch name to pass to it. The -d option tells Git to delete the branches. Note that this option will fail if the branch has unmerged changes, in which case the -D option could be used instead to force-delete the branch.

Note: you can test these git commands by creating temporary repository on any git platform like, GitHub, GitLab or Bitbucket.

Disclaimer: This post contains affiliate links. If you use these links to buy something, I may earn a commission at no extra cost to you.

Using Laravel to upload a file

Learn how to handle file uploads in Laravel, including validations and best practices.

Uploading a file in any programming is a challenge. In this post, we focus on uploading a file and some validations to use with file upload using Laravel.

To upload a file using Laravel, you can follow these steps:

Create a new form in your Laravel view with an input field for the file:

<form method="POST" action="{{ route('file.upload') }}" enctype="multipart/form-data">
    @csrf

    <input type="file" name="file">

    <button type="submit">Upload</button>
</form>

In above code, we have added file.upload route as an action of the form. So, we need to define this route in routes/web.php file. This route should point to the controller method that will handle the file upload.

The following code will define a new route in your routes/web.php file:

Route::post('/file/upload', [App\Http\Controllers\FileController::class, 'upload'])->name('file.upload');

Above code has defined a route, which points to the upload method of the FileController. So, create a new controller FileController and method upload inside that to handle the file upload as follows:

class FileController
{
    public function upload(Request $request)
    {
        // Validate the uploaded file
        $request->validate([
            'file' => 'required|file|max:1024', // limit file size to 1 MB
        ]);

        // Store the uploaded file in the storage/app/public directory
        $path = $request->file('file')->store('public');

        // Generate a URL for the uploaded file
        $url = Storage::url($path);

        // Redirect back with a success message
        return back()->with('success', 'File uploaded successfully: ' . $url);
    }
}

In the upload() method, we first validate that the uploaded file meets our requirements. So, we have added some validations for our file. These validations are as follows:

required:

The field under this validation must be present in the input data and must not empty. A field is “empty” if it meets one of the following criteria:

  • The value is null.
  • The value is an empty string.
  • The value is an empty array or empty Countable object.
  • The value is an uploaded file with no path.

file:

The field under this validation must be a successfully uploaded file.

max:1024:

The field under this validation must be less than or equal to a 1024 bytes. Here, 1024 is value of file size. You can change it according to your requirements.

We then use the store() method on the uploaded file to store it in the storage/app/public directory. This directory is publicly accessible, so we can generate a URL for the file using the url() method on the Storage facade. Finally, we redirect back to the form with a success message that includes the URL of the uploaded file.

You can now test the file upload functionality by navigating to the form and selecting a file to upload. If the file meets the validation requirements, it will be uploaded and a success message will be displayed. You can then access the uploaded file at the generated URL.

Laravel Middleware Explained: What It Is & How to Create One

Learn what middleware is in Laravel, why it’s essential, and how to create custom middleware for handling requests efficiently. Step-by-step guide included.

Middleware provides a convenient mechanism for inspecting and filtering HTTP requests entering your application. It’s best to envision middleware as a series of “layers” for HTTP requests that must pass through before they hit your application. Each layer can examine the request and even reject it entirely.

For example, Laravel includes a middleware that verifies the authenticity of the user of your application. If the user is not authenticated, the middleware will redirect the user to your application’s login screen. However, if the user is authenticated, the middleware will allow the request to proceed further into the application.

To perform different tasks, we can develop many middlewares besides authentication. For example, a logging middleware might log all incoming requests to your application. 

Laravel framework has included many middlewares, including middleware for authentication and CSRF protection. All of these middlewares are located in the app/Http/Middleware directory.

Create a custom middleware

To create a middleware, we can use the following command,

php artisan make:middleware <middleware-name>

For example, if we want to create a middleware for checking transactions, we can run the following command,

php artisan make:middleware CheckTransaction

 After successful execution of the command, a middleware class will be created under the app/Http/Middleware directory.

In this class, we can define methods to check transactions. If the transaction is not completed, we can redirect the user back to the failed transaction page. However, on the successful transactions, we can allow users to proceed to the next page.

<?php
 
namespace App\Http\Middleware;
 
use Closure;
 
class CheckTransaction
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        if ($request->input('status') !== 'completed') {
            return redirect('transaction-failed');
        }
 
        return $next($request);
    }
}

As you can see, if the transaction status does not set to “completed”, the middleware will return an HTTP redirect to the client; otherwise, the request will be passed further into the application.

To pass the request deeper into the application (allowing the middleware to “pass”), you should call the $next callback with the $request.

Update a bunch of images at once and export them as separate images using GIMP

Scaling of images can be achieved without using any scripts/extensions, but to export all images as separate image files, we need to install a plugin in GIMP named ‘Export Layers’.

You can download this plugin from the following link,

https://kamilburda.github.io/gimp-export-layers

This plugin is available for Windows, Linux, and macOS. For Windows, it can be installed using an executable file.

After installation of this plugin, restart GIMP.

Now, that we have the plugin installed, there is a very simple way to accomplish this task using the following easy steps.

  1. File > Open as layers to select all images to perform a specific task (This is one single action since the file selector allows the selection of multiple images)
  2. Image > Scale image to 75×75 to scale all layers together (We can perform many different actions similar to scaling like transforming, resizing, cropping, etc.)
  3. File > Export Layers will open a dialog that appears allows you to choose the output folder and file extension.
Export Layers Dialog Box

The above steps will save all your image layers to separate files. This could reduce so many steps of similar tasks.

You can perform many different actions like transforming, cropping, resizing, etc. using same steps.