Bulk Insert in MySql using LOAD DATA INFILE

Are you looking for the stuff to insert bulk data into MySQL table?

Go for “LOAD DATA INFILE” MySQL concept.  This is the best way I am witness of.

You might need to use this concept in 2 ways.

1)      Bulk Insert Functionality in PHP Application

2)      Import bulk records directly in MySQL

Let’s discuss “How to implement this mechanism” for first option for now, I will write on second option afterwards.

Bulk Insert Functionality in PHP Application

Your application might have Bulk Insert functionality on application itself or may be using cron job. This option can be helpful in both the way.

For Example:

What I had to implement in my application was “Data Sync”. I had to insert all records of one table from local database to global database, and the worst thing was using Button Click, client do not want to implement cron job.

If you are talking about Bulk Data Insert, do you think anyone will wait more than 5 seconds on page to let the process complete. I think “A Big No”. So what to do?

After 1-2 days of R&D on bulk insert from application, I came to the decision that I can use “LOAD DATA INFILE” concept.

But to use this concept you must have data in csv format. There are other supported formats too; you can check it out on google. But I am talking about CSV file here.

Source Code:

Create one source file named bulk_db_insert.php

$host = "localhost";
$uname = "root";
$pass = "";
$database = "dbname";

$table1 = trim($_GET['tablename']);
$fileName_csv = trim($_GET['filename']);

$connection = mysqli_connect($host,$uname,$pass,$database) or die("Database Connection Failed");

// Check connection
if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
{
   echo "connected";
}

$filename = "C:/wamp/www/daybreak/BulkInsert/".$fileName_csv;

$encloserStr = '"';
$query = 'LOAD DATA INFILE "'.$filename.'" INTO TABLE '.$table1.' FIELDS TERMINATED BY "," ENCLOSED BY "'.$encloserStr.'"';

// Perform queries
try
{
   if (!mysqli_query($connection,$query))
   {
      echo("Error description: " . mysqli_error($connection));
   }
   else
   {
      echo "<br />Query Succeed!!";
   }
}
catch(Exception $e)
{
   echo $e->message;
}

mysqli_close($connection);

CSV file here contains the comma separated values for the table’s columns in which you want to insert the records.

Everybody knows what the CSV file is and how it works; again it’s a big topic to talk about. If you do not have idea about CSV file please do little R&D on it to understand it purpose.

The above code snippet itself shows “how to do bulk inset in to MySQL DB table”.

Have a happy MySQLing… 🙂