PHP - How To Upload CSV File Into MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 3110 times.

In this tutorial we will try to upload a CSV File Into MySQLi. This simple script can process and insert the CSV file into MySQli Database. It can lessen and fasten your database processing if your csv is already have a data. So let's do the coding.

Before we started:
First you have to download & install WAMPserver or any local server that run PHP scripts. Here's the link for WAMP server http://www.wampserver.com/en/.

Creating the database Connection
This is the where the database connection, just simple copy/paste the provided code below.

  1. <?php
  2. $conn = new mysqli("localhost", "root", "", "phptut");
  3. if(!$conn){
  4. die("Fatal Error: Connect Error!");
  5. }
  6. ?>

The Main Interface
This is where the main layout is located, to make this one just simply copy/paste the code below.

  1. <!DOCTYPE HTML>
  2. <?php
  3. require 'connect.php';
  4. ?>
  5.  
  6. <html lang = "en">
  7. <head>
  8. <title>PHP - How To Upload CSV Data Into MySQLi</title>
  9. <meta charset = "UTF-8" name = "viewport" content = "width=device-width, initial-scale=1" />
  10. <link rel = "stylesheet" type = "text/css" href = "css/bootstrap.css" />
  11. <link rel = "stylesheet" type = "text/css" href = "css/jquery.dataTables.css"/>
  12. </head>
  13.  
  14. <body>
  15. <nav class = "navbar navbar-default">
  16. <div class = "container-fluid">
  17. <a class = "navbar-brand" href = "<a href="https://sourcecodester.com">Sourcecodester</a>
  18. " rel="nofollow">https://sourcecodester.com">Sourcecodester</a>
  19. </a> </div>
  20. </nav>
  21. <div class = "col-md-3"></div>
  22. <div class = "col-md-6 well">
  23. <h3 class = "text-primary">PHP - How To Upload CSV Data Into MySQLi</h3>
  24. <hr style = "border-top:1px dotted #000;"/>
  25. <form action = "upload.php" class = "form-inline" method = "POST" enctype = "multipart/form-data">
  26. <div class = "form-group">
  27. <label>CSV File: <input type = "file" name = "file" class = "form-control"/></label>
  28. <button type = "submit" name = "save" class = "btn btn-primary form-control"><span class = "glyphicon glyphicon-upload"></span> UPLOAD</button>
  29. </div>
  30. </form>
  31. <br />
  32. <table id = "table" class = "table table-bordered">
  33. <thead>
  34. <tr>
  35. <th>Firstname</th>
  36. <th>Lastname</th>
  37. <th>Address</th>
  38. </tr>
  39. </thead>
  40. <tbody>
  41. <?php
  42. $query = $conn->query("SELECT * FROM `member`");
  43. while($fetch = $query->fetch_array()){
  44. ?>
  45. <tr>
  46. <td><?php echo $fetch['firstname']?></td>
  47. <td><?php echo $fetch['lastname']?></td>
  48. <td><?php echo $fetch['address']?></td>
  49. </tr>
  50. <?php
  51. }
  52. ?>
  53. </tbody>
  54. </table>
  55. </div>
  56. </body>
  57. <script src = "js/jquery-3.2.1.js"></script>
  58. <script src = "js/jquery.dataTables.js"></script>
  59. <script type = "text/javascript">
  60. $(document).ready(function(){
  61. $('#table').DataTable();
  62. });
  63. </script>
  64. </html>

The Upload Script
This is where the upload script occur, this script will handle the processing of the CSV file. Then later will be upload to MySQLi database server to be display in a web interface. To do that just simply copy/paste the code below.

  1. <?php
  2. require 'connect.php';
  3.  
  4. if(ISSET($_POST['save'])){
  5. if($_FILES['file']['name']){
  6. $filename = explode(".", $_FILES['file']['name']);
  7. if($filename[1] == 'csv'){
  8. $handler = fopen($_FILES['file']['tmp_name'], "r");
  9. while($data = fgetcsv($handler)){
  10. $conn->query("INSERT INTO `member` (firstname, lastname, address) VALUES('$data[0]', '$data[1]', '$data[2]')");
  11. }
  12.  
  13. fclose($handler);
  14. }
  15. }
  16.  
  17. header('location:index.php');
  18.  
  19. }
  20. ?>

There you have it we simple Upload a CSV file into the MySQLi database. I hope that this simple tutorial help you for what you are looking for. For more updates and tutorial just kindly visit this site. Enjoy Coding!!


Tags: 

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.