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

Posted 2:19 am by muni & filed under CakePHP, MYSQL, PHP, SQL.

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.

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.

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>';
}