phpmyadmin Question posted in
The official documentation can be found here.

Can I merge an .sql file that runs insert into and a Laravel factory seeder that creates dummy data on 2 only columns? – Phpmyadmin

I have a factory file that creates dummy data for 2 columns: code & barcode. However, I wanted to run an .sql file that replaces data after I run the factory command. With that, I guess a merge will happen and might delete some records, or add new ones. Is that possible to merge the data instead. Like automatic mapping?

Here’s the content of my .sql file:

-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 23, 2021 at 12:14 PM
-- Server version: 10.1.28-MariaDB
-- PHP Version: 7.1.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_zachmatic`
--

--
-- Dumping data for table `products`
--

REPLACE INTO `products` (`id`, `code`, `name`, `description`, `unit`, `cost`, `srp`, `supplier`, `qty_on_hand`, `category`, `delivery_date`, `created_at`) VALUES
(12, 'P-824830', 'magnum-v', '45/90-17 MV-360 tube type tires only', 'Pieces', '', '1246', 'HH All Ventures', 3, 'Tires', '', ''),
(13, 'P-232033', 'magnum-v', '50/100-17 MV-360 tube type tire only', 'Select Product ', '', '1246', 'HH All Ventures', 1, 'Tires', '', ''),
(14, 'P-73032309', 'magnum-v', '60/90-17 MV-360 Tube Type Tire Only', 'Pieces', '', '1341', 'HH All Ventures', 0, 'Tires', '', ''),
(15, 'P-0022252', 'magnum-v', '45/90-17 MV-329 Tube Type with Tube', 'Pieces', '', '1120', 'HH All Ventures', 9, 'Tires', '', ''),
(16, 'P-323694', 'magnum-v', '50/100-17 MV-329 tube type with tube', 'Select Product ', '', '1120', 'HH All Ventures', 0, 'Tires', '', ''),
(17, 'P-023202', 'magnum-v', '60/90-17 MV-329 tube type with tube', 'Pieces', '', '1499', 'HH All Ventures', 0, 'Tires', '', ''),
(18, 'P-4729320', 'magnum-v', '70/90-17 MV-329 tube type with tube', 'Pieces', '', '1678', 'HH All Ventures', 0, 'Tires', '', ''),
(19, 'P-3020323', 'magnum-v', '120/70-13 MV-119C Tubeless', 'Pieces', '', '1829', 'HH All Ventures', 5, 'Tires', '', ''),
(20, 'P-3220830', 'magnum-v', '130/70-13 MV-119C tubeless', 'Pieces', '', '2062', 'HH All Ventures', 0, 'Tires', '', ''),

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products` DROP COLUMN `id`;
ALTER TABLE `products` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;

--
-- Drop `code` column to delete old barcodes
-- then re-add `code` column, then add `barcode` column
--
ALTER TABLE `products` DROP COLUMN `code`;
ALTER TABLE `products` ADD COLUMN code VARCHAR(255) AFTER id;

--
-- Drop `delivery_date` column to delete old column
-- then re-add `delivery_date` column, then add `barcode` column
--
ALTER TABLE `products` DROP COLUMN `delivery_date`;
ALTER TABLE `products` ADD COLUMN delivery_date DATETIME;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Then my factory file:

<?php

namespace DatabaseFactories;

use AppModelsProduct;
use IlluminateDatabaseEloquentFactoriesFactory;
use Picqer;

class ProductFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Product::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        $code = $this->faker->bothify('PHZM-##########');
        $barcode = $this->generateBarcode($code);
        return [            
            'code' => $code,
            'barcode' => $barcode,
        ];
    }

    private function generateBarcode($code_to_convert) {
        $generator = new PicqerBarcodeBarcodeGeneratorHTML();
        $barcode = $generator->getBarcode($code_to_convert, $generator::TYPE_CODE_128, 1, 15);
        return $barcode;
    }
}

Any help is appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you @alirezadp10 for the direction! I've managed to solve this by following his answer as well as exporting the products table in phpmyadmin with Update queries instead of default Insert Into as custom.

    Screenshot: Export as Custom

    1. Then uncheck the following:
    • Add DROP TABLE / TRIGGER statement
    • Add CREATE VIEW statement

    Screenshot: Uncheck these settings

    1. Select Update on the Function to use when dumping data dropdown.

    Screenshot: Select Update on the dropdown

    1. Then press Go.

    After that, I've just replaced the field values using some regex in Sublime Text 3. Link: Regular expression search replace in Sublime Text 2

    And renamed the fields to my needs. For @alirezadp10 answer to work, I've run php artisan db:seed.


  2. 0

    I guess you must create seeder for running this script file like below:

    <?php

use IlluminateDatabaseSeeder;

class SqlFileSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {

        $path = public_path('sql/File.sql');
        $sql = file_get_contents($path);
        DB::unprepared($sql);
    }

}

    and inside another seeder that your factory calls, you run this seeder too after calling factory

    <?php

use IlluminateDatabaseSeeder;

class FooSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Product::factory()->count(20)->create();
        $this->call([SqlFileSeeder::class]);
    }

}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search