How to seed/populate MySQL database with dummy/test data Using PHP DB Seeder

Posted by & filed under CakePHP, MYSQL, PHP, SQL.

Seed/Populate/Fill With Dummy/Test Data MySQL Database PHP Facker
In this tutorials we are going to see how to seed/populate MySQL database with dummy/test data Using PHP db seeder. This method of PHP database seeder helps us to easily fill your database with fake/dummy data. It is framework agnostic, so whatever framework (Laravel, CakePHP, Codeigniter and etc.) you use for development, for generating dummy/test data you can follow this tutorial.

For this tutorial I am using CakePHP blog tutorial database. Here is the blog database design.
how-to-seed-mysql-db-design-php

Requirements

  1. PHP Db Seeder Plugin
  2. Basic Knowledge of PHP
  3. PDO Driver

Step 1: Create MySQL Database With Required Tables To Seed dummy/test data Using PHP

First create new database in your MySQL server in the name of seeder (OR whatever name you like). Next using following SQL queries create tables in it.



SET FOREIGN_KEY_CHECKS=0; 

-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `password` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `created` DATETIME NULL DEFAULT NULL,
  `modified` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1001
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `articles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `articles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `body` TEXT NULL DEFAULT NULL,
  `published` TINYINT(1) NULL DEFAULT '0',
  `created` DATETIME NULL DEFAULT NULL,
  `modified` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `slug` (`slug` ASC),
  INDEX `user_key` (`user_id` ASC),
  CONSTRAINT `articles_ibfk_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `cake_cms`.`users` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1001
DEFAULT CHARACTER SET = utf8mb4;


-- -----------------------------------------------------
-- Table `tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tags` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(191) NULL DEFAULT NULL,
  `created` DATETIME NULL DEFAULT NULL,
  `modified` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `title` (`title` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 1001
DEFAULT CHARACTER SET = utf8mb4;


-- -----------------------------------------------------
-- Table `articles_tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `articles_tags` (
  `article_id` INT(11) NOT NULL,
  `tag_id` INT(11) NOT NULL,
  PRIMARY KEY (`article_id`, `tag_id`),
  INDEX `tag_key` (`tag_id` ASC),
  CONSTRAINT `articles_tags_ibfk_1`
    FOREIGN KEY (`tag_id`)
    REFERENCES `cake_cms`.`tags` (`id`),
  CONSTRAINT `articles_tags_ibfk_2`
    FOREIGN KEY (`article_id`)
    REFERENCES `cake_cms`.`articles` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


SET FOREIGN_KEY_CHECKS=1;

 

Step 2: Install PHP Db Seeder Plugin Using Composer

Create new project called seeder in your htdocs/workspace, and install following PHP plugin using composer.

Note: If you have not installed Composer in your system already, then install Composer using following tutorial

  1. Install Composer In XAMPP/WAMP Windows
  2. Composer Installation – Linux/Unix/OSX

Once you installed composer successfully in your system, then go to your project folder and run the following command


composer require tebazil/db-seeder

This PHP Db Seeder Plugin internally using Faker PHP Plugin to generate dummy data. So you can refer Faker PHP Plugin documents if you want.

Step 3: Import DB Seeder Class and Connect MySQL Database Using PHP PDO Driver to generate Dummy Data:

Finally create index.php file in your project root folder, then include autoload.php file from vendor directory.


require_once('./vendor/autoload.php');

Next connect with your MySQL Database using PHP PDO driver like this.


//Connecting MySQL Database
$pdo  = new PDO('mysql:host=localhost;dbname=seeder', 'root', 'mysql', array(
    PDO::ATTR_PERSISTENT => true
));
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Next disabling FOREIGN_KEY_CHECKS by setting SET FOREIGN_KEY_CHECKS=0, which helps drop all the previously existing data without any Foreign key constraint issue before generating new dummy data. Then initialize Seeder, generator & faker object to generate test data in MySQL database using PHP PDO driver.


<?php
/**
 * Populate MySQL Table Using faker
 * 
 * @author muni <muni@smarttutorials.net>
 */
require_once('./vendor/autoload.php');
try{
    $count = 1000;

    //Connecting MySQL Database
    $pdo  = new PDO('mysql:host=localhost;dbname=seeder', 'root', 'mysql', array(
        PDO::ATTR_PERSISTENT => true
    ));
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


    $seeder = new \tebazil\dbseeder\Seeder($pdo);
    $generator = $seeder->getGeneratorConfigurator();
    $faker = $generator->getFakerConfigurator();

    $stmt = $pdo->prepare("SET FOREIGN_KEY_CHECKS=0");
    $stmt->execute();


    $seeder->table('articles_tags')->columns([
            'article_id' => $generator->relation('articles', 'id'), //automatic fk
            'tag_id' => $generator->relation('tags', 'id'), //automatic fk
        ])->rowQuantity( $count );  

    $seeder->table('articles')->columns([
            'id', //automatic pk
            'user_id' => $generator->relation('users', 'id'), //automatic fk
            'title'=>$faker->text(50),
            'slug'=>$faker->text(50),
            'body'=>$faker->text,
            'published'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            },
            'created'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            },
            'modified'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            }
        ])->rowQuantity( $count );

    $seeder->table('users')->columns([
            'id', //automatic pk
            'email'=>$faker->email,
            'password'=>$faker->password,
            'created'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            },
            'modified'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            }
        ])->rowQuantity( $count );


    $seeder->table('tags')->columns([
            'id', //automatic pk
            'title'=>$faker->unique()->text(10),
            'created'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            },
            'modified'=> function(){
                return date('Y-m-d H:i:s', rand(0, time()));
            }
        ])->rowQuantity( $count );     

    $seeder->refill();

    $stmt = $pdo->prepare("SET FOREIGN_KEY_CHECKS=1");
    $stmt->execute();

} catch(Exception $e){
    echo '<pre>';print_r($e);echo '</pre>';
}

Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts

If you want any of my script need to be customized according to your business requirement,

Please feel free to contact me [at] muni[at]smarttutorials.net

Note: But it will be charged based on your customization requirement

Get Updates, Scripts & Other Useful Resources to your Email

Join 10,000+ Happy Subscribers on feedburner. Click to Subscribe (We don't send spam)
Every Email Subsciber could have access to download 100+ demo scripts & all future scripts.

Get Instant Script Download Access!