Insert dummy data into MYSQL using PHP

Every project requires some initial data for testing. Either it’s a user profile information or product description; we need data to test the proper flow of application.

But where we can get dummy data?

Every time, we should ask the client to send us some dummy data so we can start project development?

Or we should add some dummy data into database and show to the client for testing purpose?

Answer is, it depends on the project scope. If you are working on corporate website then you may get company profiles, about us text, company’s portfolio, image gallery and contact us page text. But if you are developing a shopping cart or a content management system (CMS) then you require more data which client may not send you.

So in this post I am going to show you how to insert dummy data into mysql using PHP Faker Library. I already covered PHP Faker configuration and its basic use in my previous post.

What we are going to do:

We are going to create a database as my_cart. Then we will create 2 tables i.e. users and products. Then we will add data into both tables. Now lets’ start.

Create Database:


create database my_cart;
use my_cart;

create database my_cart;

use my_cart;

Create Tables:

Users Table:


CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(255) NOT NULL,
`user_email` VARCHAR(255) NOT NULL,
`user_phone` VARCHAR(255) NOT NULL,
`user_password` VARCHAR(255) NOT NULL,
`user_address` VARCHAR(255) NOT NULL,
`user_country` VARCHAR(255) NOT NULL,
`user_postcode` VARCHAR(255) NOT NULL,
`user_ip` VARCHAR(255) NOT NULL,
`created` DATE NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `users` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`user_name` VARCHAR(255) NOT NULL,

`user_email` VARCHAR(255) NOT NULL,

`user_phone` VARCHAR(255) NOT NULL,

`user_password` VARCHAR(255) NOT NULL,

`user_address` VARCHAR(255) NOT NULL,

`user_country` VARCHAR(255) NOT NULL,

`user_postcode` VARCHAR(255) NOT NULL,

`user_ip` VARCHAR(255) NOT NULL,

`created` DATE NOT NULL,

PRIMARY KEY (`id`)

)

COLLATE='latin1_swedish_ci'

ENGINE=InnoDB

;

Products Table:


CREATE TABLE `products` (
`pid` INT(11) NOT NULL AUTO_INCREMENT,
`product_name` VARCHAR(255) NOT NULL,
`product_desc` TEXT NOT NULL,
`product_short_desc` VARCHAR(1000) NOT NULL,
`product_color` VARCHAR(255) NOT NULL,
`product_image` VARCHAR(255) NOT NULL,
`product_sku` VARCHAR(255) NOT NULL,
`product_company_email` VARCHAR(255) NOT NULL,
`product_company_url` VARCHAR(255) NOT NULL,
PRIMARY KEY (`pid`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=12
;

CREATE TABLE `products` (

`pid` INT(11) NOT NULL AUTO_INCREMENT,

`product_name` VARCHAR(255) NOT NULL,

`product_desc` TEXT NOT NULL,

`product_short_desc` VARCHAR(1000) NOT NULL,

`product_color` VARCHAR(255) NOT NULL,

`product_image` VARCHAR(255) NOT NULL,

`product_sku` VARCHAR(255) NOT NULL,

`product_company_email` VARCHAR(255) NOT NULL,

`product_company_url` VARCHAR(255) NOT NULL,

PRIMARY KEY (`pid`)

)

COLLATE='latin1_swedish_ci'

ENGINE=InnoDB

AUTO_INCREMENT=12

;

Now we have created 2 tables it’s time to start PHP coding. So first we need to download faker library from github.

Faker Github Link:

https://github.com/fzaninotto/Faker

You can also see my post my previous post How to use Faker – PHP Library that generates Fake Data

Users.php

In this file we will insert data into users table.


<?php
require '../src/autoload.php';

$faker = Faker\Factory::create();

$conn = mysqli_connect("localhost","root","","my_cart");
if(!$conn)
{
	die(mysqli_error());
}
	
for ($i=0; $i <= 10; $i++)
{
	$sql = "insert into users (user_name, user_email, user_phone, user_password, user_address, user_country, user_postcode, user_ip, created) values('".$faker->firstName."', '".$faker->email."', '".$faker->phoneNumber."', '".$faker->password."', '".$faker->address."', '".$faker->country."' , '".$faker->postcode."' , '".$faker->ipv4."', '".$faker->date('Y-m-d','now')."')";
	
	mysqli_query($conn, $sql);

} 

?>

<?php

require '../src/autoload.php';

$faker = Faker\Factory::create();

$conn = mysqli_connect("localhost","root","","my_cart");

if(!$conn)

{

die(mysqli_error());

}

for ($i=0; $i <= 10; $i++)

{

$sql = "insert into users (user_name, user_email, user_phone, user_password, user_address, user_country, user_postcode, user_ip, created) values('".$faker->firstName."', '".$faker->email."', '".$faker->phoneNumber."', '".$faker->password."', '".$faker->address."', '".$faker->country."' , '".$faker->postcode."' , '".$faker->ipv4."', '".$faker->date('Y-m-d','now')."')";

mysqli_query($conn, $sql);

}

First we load the autoload.php file and then create the object using $faker variable. $conn is the database connection and then we start loop that is running 11 times and insert row in users table. You can below image every iteration of for will create different data.

Products.php

In this file we will insert data in products table.


<?php
require '../src/autoload.php';

$faker = Faker\Factory::create();

$conn = mysqli_connect("localhost","root","","my_cart");
if(!$conn)
{
	die(mysqli_error());
}
	
for ($i=0; $i <= 10; $i++)
{
	$productName = $faker->words(2,true);
	$productDesc = $faker->text(500);
	$productShortDesc =  $faker->paragraphs(1,true);
	$productColor = $faker->colorName;
	$productImgUrl = $faker->imageUrl(640,480);
	$productSku = $faker->numberBetween(1000,50000);
	$productCompanyEmail = $faker->companyEmail;
	$productDomainName = $faker->domainName;
	
	$sql = "insert into products (product_name, product_desc, product_short_desc, product_color, product_image, product_sku, product_company_email, product_company_url) values ('".$productName."', '".$productDesc."' , '".$productShortDesc."' , '".$productColor."' , '".$productImgUrl."', '".$productSku."', '".$productCompanyEmail."' , '".$productDomainName."')";
	
	mysqli_query($conn, $sql);

} 

?>

<?php

require '../src/autoload.php';

$faker = Faker\Factory::create();

$conn = mysqli_connect("localhost","root","","my_cart");

if(!$conn)

{

die(mysqli_error());

}

for ($i=0; $i <= 10; $i++)

{

$productName = $faker->words(2,true);

$productDesc = $faker->text(500);

$productShortDesc = $faker->paragraphs(1,true);

$productColor = $faker->colorName;

$productImgUrl = $faker->imageUrl(640,480);

$productSku = $faker->numberBetween(1000,50000);

$productCompanyEmail = $faker->companyEmail;

$productDomainName = $faker->domainName;

$sql = "insert into products (product_name, product_desc, product_short_desc, product_color, product_image, product_sku, product_company_email, product_company_url) values ('".$productName."', '".$productDesc."' , '".$productShortDesc."' , '".$productColor."' , '".$productImgUrl."', '".$productSku."', '".$productCompanyEmail."' , '".$productDomainName."')";

mysqli_query($conn, $sql);

}

In products.php file first we load the autoload.php file then we create instance having $faker name. After that we created database connection and then add for loop which is running 10 times. $productName holds the 2 word name of product, $productDesc has 500 character text in it, $productShortDesc has 1 paragraph, $productImgUrl holds the image with 640 width and 480 height and $productSku generate random number in between 1000 to 5000. After running above code 11 records are inserted into products table.