Generate 1 Lakh Dummy Records for MySQL Database in 1 mins Using PHP, PDO

Posted 6:22 am by muni & filed under MYSQL, PHP, SQL.

In this tutorial we are going to see how to insert dummy data in MySQL database using PHP and PDO. Most of time when we develop an application we need sample data according to our requirement to develop, test or do performance run on the application.

Here explaining step by step on how to seed Mysql Database Using PHP Faker.

Requirements:

1. Faker PHP Plugin
2. PDO Driver

Step 1: Install PHP Faker 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 fzaninotto/faker

Step 2: Create MySQL Database and Tables:

Now create your database and tables in your MySQL server. I have sample test database and users table in it. Create users in your database using following sql query.

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step 3: Import Faker Object and Connect Database to generate Dummy Data:

Finally create index.php file, then include autoload.php file from vendor directory. That will taking care including Faker classes automatically when we instantiate the Faker class.

Here is my sample script which will make the connection with MySQL database and insert dummy data in it. Specify number of records in $count variable.

<?php
/**
 * Populate MySQL Table Using faker
 * 
 * @author 
 */
require_once('./vendor/autoload.php');
try{
    $count = 100000;
    $faker = \Faker\Factory::create();

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

    //Drop the table 
    $stmt = $pdo->prepare("truncate table users");
    $stmt->execute();

    //Insert the data
    $sql = 'INSERT INTO users (first_name, last_name, email, created) 
    VALUES (:first_name, :last_name, :email, :created)';
    $stmt = $pdo->prepare($sql);

    for ($i=0; $i < $count; $i++) {
        $date = $faker->dateTime($max = 'now', 'UTC')->format('Y-m-d H:i:s');
        $stmt->execute(
            [
                ':first_name' => $faker->firstName, 
                ':last_name' => $faker->lastName,    
                ':email' => $faker->email, 
                ':created' => $date
            ]
        );
    }
} catch(Exception $e){
    echo '<pre>';print_r($e);echo '</pre>';exit;
}