How to import csv into mysql have been the order of the day search always on google.

after so many google search codexworld.com and w3lessons.info provides a better tutorials or better said post that best explain how to import csv into mysql database using php.

#Do you know that


  • ‘w3lessons.info on how to import csv into mysql database using php does include the csv header while importing the csv data into the mysql database while codexworld.com on the same idea does not import the csv header.
  • The w3lessons.info on this idea importing csv into mysql database using php also updates the already existing data using column “email” as the primary key.
  • The Bootstrap CDN in codexworld codes or w3lessons of this ideal topic don’t because we noticed that the some of the CDN starts with http:// not https://
  • Bootstrap CDN never works with on http:// protocols but does with https:// protocols

#Appreciation


Kudos to codexworld and w3lessons for providing that excellent awarding article on this idea said in here in this post title though there have been as it should mistakes some where in their codes but all have been corrected in our codes below for the both parties.

#Application of this idea


web development have been extra ordinary one and below are the uses of importing csv into mysql using php the idea in this post.

  1. in financial analysis such idea is used to manage bulk banking account system
  2. in managing student / staff information system
  3. Managing statistics (statistical analysis)

#CODES EXAMPLE


w3lessons.info provides the code below to import csv (comma separated values) into mysql database using php

The mysql code:

copy the mysql code to create database table ‘users’

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(8) NOT NULL,
  `userName` varchar(55) NOT NULL,
  `password` varchar(55) NOT NULL,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The php codes:

<?php 
$conn=mysqli_connect("localhost","database_user","database_password","database_name");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

 ?>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Import csv</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="https://code.jquery.com/jquery-2.1.3.min.js"></script>
<style>
body {
  font-family: Arial;
  width: 550px;
}

.outer-scontainer {
  background: #F0F0F0;
  border: #e0dfdf 1px solid;
  padding: 20px;
  border-radius: 2px;
}

.input-row {
  margin-top: 0px;
  margin-bottom: 20px;
}



.outer-scontainer table {
  border-collapse: collapse;
  width: 100%;
}

.outer-scontainer th {
  border: 1px solid #dddddd;
  padding: 8px;
  text-align: left;
}

.outer-scontainer td {
  border: 1px solid #dddddd;
  padding: 8px;
  text-align: left;
}

#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() {
    $("#frmCSVImport").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>
<?php
if (isset($_POST["import"])) {

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

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

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

while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$sqlInsert = "INSERT into users (userId,userName,password,firstName,lastName)
values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "','" . $column[4] . "')";
$result = mysqli_query($conn, $sqlInsert);

if (! empty($result)) {
$type = "success";
$message = "CSV Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing CSV Data";
}
}
}
}
?>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2><img src="https://shopinson.com/wp-content/uploads/2018/04/import-csv.png" width="50px"/>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>
<div class="outer-scontainer">
<div class="row">
<form class="form-horizontal" action="" method="post"
name="frmCSVImport" id="frmCSVImport" enctype="multipart/form-data">
<div class="input-row">
<label class="col-md-4 control-label">Choose CSV
File</label> <input type="file" name="file"
id="file" accept=".csv"> <br>
<button type="submit" id="submit" name="import"
class="btn btn-success">Import</button>
<br />
</div>
</form>
</div>
<?php
$sqlSelect = "SELECT * FROM users";
$result = mysqli_query($conn, $sqlSelect);
if (mysqli_num_rows($result) > 0) {
?>
<table id='userTable'>
<thead>
<tr>
<th>User ID</th>
<th>User Name</th>
<th>First Name</th>
<th>Last Name</th>

</tr>
</thead>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tbody>
<tr>
<td><?php echo $row['userId']; ?></td>
<td><?php echo $row['userName']; ?></td>
<td><?php echo $row['firstName']; ?></td>
<td><?php echo $row['lastName']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php } ?>
</div>
</div>
</div>
</div>
</body>
</html>

You only need to copy and change the mysql connection variable in line 2 of the code above see illustration below.

  • database_user
  • database_password
  • database_name

codexworld.com have provides a better comprehensible on how to import csv (comma separated value) into mysql database using php

The Mysql codes:

this codes creates a database table ‘members’

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

 

The php codes:

<title>import csv using php</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<style type="text/css">
    .panel-heading a{float: right;}
    #importFrm{margin-bottom: 20px;display: none;}
    #importFrm input[type=file] {display: inline;}
</style>
<?php

//DB details
$dbHost = 'localhost';
$dbUsername = 'database-username';
$dbPassword = 'database-password';
$dbName = 'database-name';

//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Unable to connect database: " . $db->connect_error);
}
if(!empty($_GET['status'])){
    switch($_GET['status']){
        case 'succ':
            $statusMsgClass = 'alert-success';
            $statusMsg = 'Members data has been inserted successfully.';
            break;
        case 'err':
            $statusMsgClass = 'alert-danger';
            $statusMsg = 'Some problem occurred, please try again.';
            break;
        case 'invalid_file':
            $statusMsgClass = 'alert-danger';
            $statusMsg = 'Please upload a valid CSV file.';
            break;
        default:
            $statusMsgClass = '';
            $statusMsg = '';
    }
}

if(isset($_POST['importSubmit'])){
    
    //validate whether uploaded file is a csv file
    $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$csvMimes)){
        if(is_uploaded_file($_FILES['file']['tmp_name'])){
            
            //open uploaded csv file with read only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            
            //skip first line
            fgetcsv($csvFile);
            
            //parse data from csv file line by line
            while(($line = fgetcsv($csvFile)) !== FALSE){
                //check whether member already exists in database with same email
                $prevQuery = "SELECT id FROM members WHERE email = '".$line[1]."'";
                $prevResult = $db->query($prevQuery);
                if($prevResult->num_rows > 0){
                    //update member data
                    $db->query("UPDATE members SET name = '".$line[0]."', phone = '".$line[2]."', created = '".$line[3]."', modified = '".$line[3]."', status = '".$line[4]."' WHERE email = '".$line[1]."'");
                }else{
                    //insert member data into database
                    $db->query("INSERT INTO members (name, email, phone, created, modified, status) VALUES ('".$line[0]."','".$line[1]."','".$line[2]."','".$line[3]."','".$line[3]."','".$line[4]."')");
                }
            }
            
            //close opened csv file
            fclose($csvFile);

            $qstring = '?status=succ';
        }else{
            $qstring = '?status=err';
        }
    }else{
        $qstring = '?status=invalid_file';
    }

//redirect to the listing page
header("Location: index.php".$qstring);

}


?>

<div class="container">
    <?php if(!empty($statusMsg)){
        echo '<div class="alert '.$statusMsgClass.'">'.$statusMsg.'</div>';
    } ?>
    <div class="panel panel-default">
        <div class="panel-heading">
            Members list
            <a href="javascript:void(0);" onclick="$('#importFrm').slideToggle();">Import Members</a>
        </div>
        <div class="panel-body">
            <form action="" method="post" enctype="multipart/form-data" id="importFrm">
                <input type="file" name="file" />
                <input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT">
            </form>
            <table class="table table-bordered">
                <thead>
                    <tr>
                      <th>Name</th>
                      <th>Email</th>
                      <th>Phone</th>
                      <th>Created</th>
                      <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                    //get records from database
                    $query = $db->query("SELECT * FROM members ORDER BY id DESC");
                    if($query->num_rows > 0){ 
                        while($row = $query->fetch_assoc()){ ?>
                    <tr>
                      <td><?php echo $row['name']; ?></td>
                      <td><?php echo $row['email']; ?></td>
                      <td><?php echo $row['phone']; ?></td>
                      <td><?php echo $row['created']; ?></td>
                      <td><?php echo ($row['status'] == '1')?'Active':'Inactive'; ?></td>
                    </tr>
                    <?php } }else{ ?>
                    <tr><td colspan="5">No member(s) found.....</td></tr>
                    <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

Now change in the above code line 13 to 16 variable to your own configuration as shown below. for example:

  • $dbUsername = ‘your database_username’;
  • $dbPassword = ‘your database_password’;
  • $dbName = ‘your database_name’;

#DONE

Now you are up to begin using your application built to import csv from any source into mysql database using php.


Download the codexworld  file on how to import csv into mysql database using php
csv to use for the codexworld.com demo importing csv into mysql database using php or to download

Download the w3lessons file on how to import csv into mysql database using php
csv to use for the w3lessons.info demo importing csv into mysql database using php or to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here