How to import CSV File into MySQL using PHP and JavaScript.

This article will illustrate how to import CSV file into MySQL database using Php. Here user will import CSV file using Html form. According to this script functionality, the user would be able to upload a CSV file and CSV file data will be inserted into the MySQL database using PHP.

inport csf file into mysql database

You can download CSV file from here: Download

MySql Script to create users Table:

 CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `userId` int(8) NOT NULL,
  `userName` varchar(55) NOT NULL,
  `email` varchar(55) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL
);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

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

DataSource.php:

<?php

namespace Coderglass;


class DataSource
{

const HOST = 'localhost';

const USERNAME = 'username';

const PASSWORD = 'password';

const DATABASENAME = 'database name';

private $conn;

function __construct()
{
$this->conn = $this->getConnection();
}

public function getConnection()
{
$conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, 
self::DATABASENAME);

if (mysqli_connect_errno()) {
trigger_error("Problem with connecting to database.");
}

$conn->set_charset("utf8");
return $conn;
}

public function select($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);

if (! empty($paramType) && ! empty($paramArray)) {

$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}

if (! empty($resultset)) {
return $resultset;
}
}


public function insert($query, $paramType, $paramArray)
{
$stmt = $this->conn->prepare($query);
$this->bindQueryParams($stmt, $paramType, $paramArray);

$stmt->execute();
$insertId = $stmt->insert_id;
return $insertId;
}


public function execute($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);

if (! empty($paramType) && ! empty($paramArray)) {
$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
}


public function bindQueryParams($stmt, $paramType, $paramArray = array())
{
$paramValueReference[] = & $paramType;
for ($i = 0; $i < count($paramArray); $i ++) {
$paramValueReference[] = & $paramArray[$i];
}
call_user_func_array(array(
$stmt,
'bind_param'
), $paramValueReference);
}


public function getRecordCount($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);
if (! empty($paramType) && ! empty($paramArray)) {

$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$stmt->store_result();
$recordCount = $stmt->num_rows;

return $recordCount;
}
}

?>

index.php:

<?php
use Coderglass\DataSource;

require_once 'DataSource.php';
$db = new DataSource();
$conn = $db->getConnection();

if (isset($_POST["import"])) {

$fileName = $_FILES["file"]["tmp_name"];


if ($_FILES["file"]["size"] > 0) {

$file = fopen($fileName, "r");

while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {

$userId = "";
if (isset($column[0])) {
$userId = mysqli_real_escape_string($conn, $column[0]);
}
$userName = "";
if (isset($column[1])) {
$userName = mysqli_real_escape_string($conn, $column[1]);
}
$email = "";
if (isset($column[2])) {
$email = mysqli_real_escape_string($conn, $column[2]);
}

$mobile = "";
if (isset($column[1])) {
$userName = mysqli_real_escape_string($conn, $column[3]);
}
$address = "";
if (isset($column[2])) {
$email = mysqli_real_escape_string($conn, $column[4]);
}


$sqlInsert = "INSERT into users (userId,userName,email,mobile,address)
   values (?,?,?,?,?)";
$paramType = "issss";
$paramArray = array(
$userId,
$userName,
$email,
$mobile,
$address
);
$insertId = $db->insert($sqlInsert, $paramType, $paramArray);

if (! empty($insertId)) {
$type = "success";
$message = "CSV Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing CSV Data";
}
}
}
}
?>



<!DOCTYPE html>
<html>

<head>
<script src="jquery-3.2.1.min.js"></script>

<style>
body {
    font-family: Arial;
    width: 550px;
}

.btn-submit {
    background: #333;
    border: #1d1d1d 1px solid;
    color: #f0f0f0;
    font-size: 0.9em;
    width: 100px;
    border-radius: 2px;
    cursor: pointer;
}

#response {
    padding: 10px;
    margin-bottom: 10px;
    border-radius: 2px;
    display: none;
}

.success {
    background: #c7efd9;
    border: #bbe2cd 1px solid;
}

.error {
    background: #fbcfcf;
    border: #f3c6c7 1px solid;
}

div#response.display-block {
    display: block;
}
</style>
<script type="text/javascript">
$(document).ready(function() {
$("#cvsImport").on("submit", function () {

$("#response").attr("class", "");
$("#response").html("");
var fileType = ".csv";
var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+(" + fileType + ")$");
if (!regex.test($("#file").val().toLowerCase())) {
$("#response").addClass("error");
$("#response").addClass("display-block");
$("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");
return false;
}
return true;
});
});
</script>
</head>

<body>
<h2>Import CSV file into Mysql using PHP</h2>

<div id="response"
class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>">
<?php if(!empty($message)) { echo $message; } ?>
</div>

<form class="form-horizontal" action="" method="post"
name="cvsImport" id="cvsImport" enctype="multipart/form-data">

<label class="col-md-4 control-label">Choose CSV File</label> 
<input type="file" name="file" id="file" accept=".csv">
<button type="submit" id="submit" name="import" class="btn-submit">Import</button>

</form>

</body>

</html>