importing csv into mysql database using php [ vs]
importing csv into mysql database using php [ vs]

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

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

#Do you know that

  • ‘ on how to import csv into does include the csv header while importing the csv data into the mysql database while on the same idea does not import the csv header.
  • The on this idea importing 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


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 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=""> <head> <title>Import csv</title> <link rel="stylesheet" href="" crossorigin="anonymous"> <link rel="stylesheet" href="" crossorigin="anonymous"> <script src="" crossorigin="anonymous"></script> <script src=""></script> <script src=""></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="" 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 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=""> <script src=""></script> <script src=""></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/', '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’;


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 using php
csv to use for the demo  using php or to download

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


Please enter your comment!
Please enter your name here