Bomshteyn
Consulting

Migrating / Importing MySQL DB Data from Custom PHP to Laravel (Seeding)

Laravel

A couple of years ago we have worked on a project for a branch of financial institution, to track and generate reports on sales and activity of advisors. The HQ of the financial institution would produce weekly reports in excel format, the branch wanted to have something more visual and exciting then a chart of numbers…

So the web app we built has import function to import the excel report data into the database. The second part of the web app is to take this data and display nice reports with graphs comparing month to month and year to year in visually appealing manner.The web app was built with old version of Yii PHP framework backend and angular.js based front end. Over time more functions were added:

  • PDF generation

  • Weeks since last sale

  • emailing reports

  • etc…

All was great, except the developer who built this doesn’t work for us anymore, and none of us particularly like / specialize in Yii. Another issue that we noticed is something that happens to projects that starts small, but then continuously add functions, some of the original architecture decisions were hurting us.  So we made a decision that its better to “bite the bullet” now and rebuild it in a framework that we know and is vastly more popular today than continue with a framework we don’t know that well and have no experience working with, plus we will have an added benefit of having a fresh start while now understanding the full scope of the project much better. The front end wouldn’t need full reworking since angular works equally well with both PHP frameworks.

In Laravel creating a new table schema is handled by Migrations. The migrations are really well documented here, but lets just quickly go through the steps:

Step 1 – create migration: php artisan make:migration createadvisorstable --create=advisors After its done open the created file and add the fields that you require

Here what our file looked like:

<?php

use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateAdvisorsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('advisors', function (Blueprint $table) {
            $table->increments('id');
            $table->string('advisor_code')->unique(); //issued by company
            $table->string('name'); // auto imported from report 
            $table->float('investment_plan'); // assigned goal for total Investment sales
            $table->float('insurance_plan'); // assigned goal for total Insurance sales
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('advisors');
    }
}

Step 2 – run the migration: php artisan migrate

This should have created the DB table for you. Now the part that wasn’t so well documented, was Seeding. That is the Laravel tool that is used to seed the database.

PhpMyAdmin has a couple export options for DB tables, we have decided to export the old database in JSON format as its easy to work with it in PHP, convert to PHP array and iterate over it. In the database/seeds directory we have created json-dump directory and uploaded the exported json file there.

export from phpmyadmin

Next step is to create the migration by running the following:

php artisan make:seeder AdvisorsTableSeeder

find the created file and write in the seeding code in our case it looks like so:

<?php

use IlluminateDatabaseSeeder;
use IlluminateSupportFacadesDB;

class AdvisorsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $json = file_get_contents(__DIR__ . '/json-dump/advisors.json');

        $advisors = json_decode($json);

        foreach ($advisors as $advisor){
            DB::table('advisors')->insert([
                'advisor_code' => $advisor->user_id,
                'name' => $advisor->name,
                'investment_plan' => $advisor->investment_plan,
                'insurance_plan' => $advisor->insurance_plan,
            ]);
        }
    }
}

All that’s left todo is run the migration:

php artisan db:seed --class=AdvisorsTableSeeder

Selected work

Case studies

All case studies

DWG · 1 · Distribution & Wholesale

A connected ERP for a wholesale distributor

We replaced a legacy desktop ERP with a portal for purchasing, receiving, inventory, sales and department workflows, connected to third-party warehouse inventory snapshots.

Before

  • Legacy desktop ERP used across departments
  • Historical records held in CSV files

Portal

  • Purchase orders, vendor offers and vendor bills
  • Receiving, discrepancy reports and per-container cost baselines
  • Inventory lots with FIFO and average landed cost
Read case study

DWG · 2 · Construction & Logistics

One logistics platform across multiple yards

We connected orders, dispatch, yard stock, drivers, equipment rentals and shipment-based invoicing for a construction supply business with multiple yards.

Before

  • Orders, deliveries and returns across multiple yards
  • Stock and backorders by warehouse

Portal

  • Orders and shipments with job-site deliveries and returns
  • Drivers, trucks and job sites with map picker and geocoding
  • Per-yard stock, movements, picking and backorder tracking
Read case study

DWG · 3 · Non-profit & Trusts

Donor management and receipting for charities

We built a donor platform that keeps each charity's branding and receipt sequence together with donations, CRA receipts, year-end packages and reporting.

Before

  • Donor and donation records across organizations
  • Receipt sequences and sender details

Portal

  • Organization branding, sender details and receipt sequences
  • Donors, donations, categories, campaigns and tags
  • Sequential CRA receipts, legacy flags and voided-receipt watermarks
Read case study