PHP Mysql Database Migration Using Phinx

 In this tutorial, you will learn to do Mysql database migration with Phinx. You will be able to create tables, add/alter table fields, and insert sample data to Mysql database. I recommend you to install XAMPP. It comes with Apache, Mysql and PhpMyAdmin to manage databases. 

This tutorial assumes you already read the previous tutorial: PHP Routing & Restful APIs.
In the api folder, run the command below to add Phinx to the project:

composer require robmorgan/phinx

Then, you need to create Phinx configuration file by the command:

./vendor/bin/phinx init

The command created phinx.php configuration file in api folder. Modify the content of phinx.php file below to add credential information to connect to Mysql database on your local machine.

<?php
return
[
    'paths' => [
        'migrations' => '%%PHINX_CONFIG_DIR%%/db/migrations',
        'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds'
    ],
    'environments' => [
        'default_migration_table' => 'phinxlog',
        'default_environment' => 'development',
        'production' => [
            'adapter' => 'mysql',
            'host' => 'localhost',
            'name' => 'example_db',
            'user' => 'user',
            'pass' => 'password',
            'port' => '3306',
            'charset' => 'utf8',

        ],
        'development' => [
             'adapter' => 'mysql',
            'host' => 'localhost',
            'name' => 'example_db',
            'user' => 'user',
            'pass' => 'password',
            'port' => '3306',
            'charset' => 'utf8',

        ],
        'testing' => [
            'adapter' => 'mysql',
            'host' => 'localhost',
            'name' => 'example_db',
            'user' => 'user',
            'pass' => 'password',
            'port' => '3306',
            'charset' => 'utf8',

        ]
    ],
    'version_order' => 'creation'
];


Let create migration files that will be used to create categories, subcategories, and products tables. In db folder, create migrations folder. Then, from api folder execute the commands below:

./vendor/bin/phinx create CategoriesTableMigration
./vendor/bin/phinx create SubcategoriesTableMigration
./vendor/bin/phinx create ProductsTableMigration

The commands will create migration files (xxx_categories_table_migration.php, xxx_subcategories_table_migration.php, xxx_posts_table_migration.php) in the db/migrations folder. 
To create categories table with id, name, description, created_at, and updated_at columns, modify the xxx_categories_table_migration.php file as below:

<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CategoriesTableMigration extends AbstractMigration
{
    
    public function change(): void
    {
        
        $table = $this->table('categories');
        $table->addColumn('name', 'string', ['limit' => 100])
        ->addColumn('description', 'string')
        ->addColumn('created_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'))
        ->addColumn('updated_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'));

        $table ->create();
        
    }
}

Modify the xxx_subcategories_table.php file to define schema for subcategories table with id, name, description, created_at, and updated_at columns.
<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class SubcategoriesTableMigration extends AbstractMigration
{
  
    public function change(): void
    {
        
        $table = $this->table('subcategories');
        $table->addColumn('name', 'string', ['limit' => 100])
        ->addColumn('description', 'string')
        ->addColumn('created_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'))
        ->addColumn('updated_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'))
        $table ->create();
        
    }
}

Modify the xxx_proucts_table_migration.php file to define schema for products table with id, name, description, price, slug, thumbnail, created_at, and updated_at columns.

<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class ProductsTableMigration extends AbstractMigration
{
    
    public function change(): void
    {
        $table = $this->table('products');
        $table->addColumn('name', 'string', ['limit' => 150])
        ->addColumn('description', 'string')
        ->addColumn('price', 'float',['default' => 0.0])
        ->addColumn('slug', 'string', ['limit' => 50])
        ->addColumn('thumbnail', 'string', ['limit' => 200])
        ->addColumn('created_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'))
        ->addColumn('updated_at', 'timestamp', array('default' => 'CURRENT_TIMESTAMP'));

        $table ->create();
    }
}


Then run the following command to create catetgories, subcategories, and products table in the database:

./vendor/bin/phinx migrate

Now we go further by adding foreign keys to subcatgories and products tables. Run the following command to create AddForeignKeysMigration:
./vendor/bin/phinx create AddForeignKeysMigration

Modify the AddForeignKeysMigration file to the below:
<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class AddForeignKeysMigration extends AbstractMigration
{

    public function change(): void
    {
        $table_s = $this->table('subcategories');
        $table_s->addColumn('category_id', 'integer', ['null' => true])
        ->addForeignKey('category_id', 'categories', 'id', ['delete'=> 'SET_NULL', 'update'=> 'CASCADE'])
        ->save();
        $table_p = $this->table('products');
        $table_p->addColumn('category_id', 'integer', ['null' => true])
        ->addColumn('subcategory_id', 'integer', ['null' => true])
        ->addForeignKey('category_id', 'categories', 'id', ['delete'=> 'SET_NULL', 'update'=> 'CASCADE'])
        ->addForeignKey('subcategory_id', 'subcategories', 'id', ['delete'=> 'SET_NULL', 'update'=> 'CASCADE'])
        ->save();

    
    }
}

Execute the ./vendor/bin/phinx migrate again. It will create category_id colunn in subcategories table and make a relationship to the categories table. Also add two columns category_id, subcategory_id to the products table and make relationships from the products table to categories, and subcategories tables.
Now it is ready to add sample data to the tables. You have to create seeds folder in db folder. Then run the commands below to create seed files to insert sample data to categories, subcategories, and products table.

./vendor/bin/phinx seed:create CategorySeed
./vendor/bin/phinx seed:create SubcategorySeed
./vendor/bin/phinx seed:create ProductSeed

CategorySeed.php
<?php


use Phinx\Seed\AbstractSeed;

class CategorySeed extends AbstractSeed
{
   
    public function run()
    {
        $data = [
            [
                'name'    => 'Camera & Photo',
                'description' => 'Best selling Camera & Photo',
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ],
            [
                'name'    => 'Smart Electronics',
                'description' => 'Best selling Smart Electronics',
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ]
        ];

        $table = $this->table('categories');
        $table->insert($data)
              ->saveData();
    }
}

Execute ./vendor/bin/phinx seed:run to insert data to categories table.

SubcategorySeed.php
<?php


use Phinx\Seed\AbstractSeed;

class SubcategorySeed extends AbstractSeed
{
   
    public function run()
    {
        $data = [
            [
                'name'    => 'Digital Cameras',
                'description' => 'Best selling Digital Cameras',
                'category_id'    => 1,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ],
            [
                'name'    => 'Smart Watches',
                'description' => 'Best selling Smart Watches',
                'category_id'    => 2,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ]
        ];

        $table = $this->table('subcategories');
        $table->insert($data)
              ->saveData();
    }
}

Execute ./vendor/bin/phinx seed:run to insert data to subcategories table.

ProductSeed.php
<?php


use Phinx\Seed\AbstractSeed;

class ProductSeed extends AbstractSeed
{
    
    public function run()
    {
        $data = [
            [
                'name'    => 'NIKON D3200 DSLR Digital Camera',
                'description' => 'NIKON D3200 DSLR Digital Camera with Nikon 18-55 LENS',
                'price' => 950,
                'slug' => 'nikonD3200',
                'category_id'    => 1,
                'subcategory_id'    => 1,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ],
            [
                'name'    => 'Canon Camera Canon EOS R',
                'description' => 'Canon Camera Canon EOS R Mirrorless Professional Full-frame Digital Dslr Camera 4K UHD 2160p RF24-105mm F4-7.1 DIGIC 8 Image',
                'price' => 1580,
                'slug' => 'CanonEOSR',
                'category_id'    => 1,
                'subcategory_id'    => 1,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ],
            [
                'name'    => 'Android Smartwatch 32G HD',
                'description' => 'Android Smartwatch 32G HD Camera Wifi Heart Rate Body Temperature GPS Positioning Man for amazfit Gift for xiaomi Youtube phone',
                'price' => 70,
                'slug' => 'androismart32g',
                'category_id'    => 2,
                'subcategory_id'    => 2,
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ]
        ];

        $table = $this->table('products');
        $table->insert($data)
              ->saveData();
    }
}

Execute ./vendor/bin/phinx seed:run to insert data to products table.

Now our data is ready. In the next article, we will create APIs to fetch all products from products table, add new product, update and delete products.

Comments

Post a Comment

Popular posts from this blog

Angular with PHP & MYSQL to display products list & paging

Image
In the third part of Angular tutorials, you learn to create Angular component and service to display products list from API endpoints, filter products by name and do pagination. Execute command below to create produce service in services folder. The product service is used to count products with search text and retrieve product list with search text and  a range of specified start and limit parameters from API endpoints. The APIs requires a token to authenticate. So, you need to add the token to the request headers. ang14-client>ng generate service services/product services/product.service.ts import { Injectable } from '@angular/core' ; import { HttpClient} from '@angular/common/http' ; import { Observable } from 'rxjs' ; import { map, catchError } from 'rxjs/operators' ; import { HttpHeaders } from '@angular/common/http' ; import { AuthService } from '../services/auth.service' ; import {api_url_root} from "../app.constant
Read more

Angular Upload Form Data & Files

Image
In the previous tutorial, you learnt to create UI to display products list and do pagination . To finish the Angular tutorials to build a simple admin panel, we add functionalities to add products with image files, update and delete existing products from MYSQL database. Execute the following command to create ProductUpload component.  ang14-client>ng generate component ProductUpload product-upload/product-upload.component.ts import { Component, OnInit } from '@angular/core' ; import { ProductService } from '../services/product.service' ; import { AuthService } from '../services/auth.service' ; import { HttpClient } from '@angular/common/http' ; import { ActivatedRoute, Router } from '@angular/router' ; @ Component({ selector : 'app-productupload' , templateUrl : './product-upload.component.html' , styleUrls : [ './product-upload.component.css' ] }) export class ProductUploadComponent implements OnIni
Read more