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

Posted by & filed under MYSQL, PHP, SQL.

seed-mysql-database-using-php-faker
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.

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

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] muni2explore[at]gmail.com

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!