The example of using MySQL variables. Laravel 13, php 8.3

• The description of the task
• Installation
• MySQL console
• Description of the solution logic
• Excel file for testing

This example illustrates how to prevent a query on a huge and busy table using MySQL variables for learning purposes. The excellent package Fast Excel takes part in this task.

I will be glad to see your pull requests.

The description of the task

There is a marketplace. There is the structure of the products database, simply for learning purposes:

Suppliers of the products can export the products to the marketplace by using the Excel file.

Files for the export have the following structure:

The marketer sets the task: to create a report for each export:

• how many items in the Warehouse table were added and for what amount of goods,
• how many items were changed and how much the cost of the goods changed with the changes.

These reports should be saved in the table import_reports.

This example shows how we can avoid additional database selection to save this report. For this, we'll use MySQL variables.

Installation

1. Clone this repository (https://github.com/bibrkacity/mysql-variables-example.git) and cd into root folder ( your-path/mysql-variables-example)
2. Copy .env.example to .env
3. Run composer install
4. Run ./vendor/bin/sail up
5. Run ./vendor/bin/sail artisan migrate
6. Run ./vendor/bin/sail artisan db:seed
7. Run ./vendor/bin/sail npm run dev

Now you can enter the application at http://127.0.0.1:8080.

MySQL console

run ./vendor/bin/sail mysql to open MySQL console.

Also, you can use mysql -h0.0.0.0 -P3307 -uroot -p (password password) to connect to MySQL.

Description of the solution logic

The functionality for which the example was created is contained in the Action class app/Http/Actions/ImportAction.php (Use Action classes for clean code of the controllers according to Laracast. ).

The MySql variables inits before the import from the Excel file:

DB::statement('SET @quantity_inserted = 0, @quantity_updated = 0, 
                   @amount_inserted = 0.0, @amount_updated = 0.0');

Next, the collection gets from an Excel file using the package Fast Excel.

Next, the collection is inserted/ updated into the table using the method upsert().

There are two triggers for the table warehouse: warehouse_after_insert and warehouse_after_update. They changed the MySQL variables.

Next, the report is saved in the table import_reports using the variables. We don't need to get their values – we set it into INSERT statement.

Finally, the event ImportReportCreated is fired. Now listener ImportReportCreatedListener only logs the event. But in real project it can be notifications, broadcast etc.

Excel file for testing

You can download the Excel file for testing here.