How To Create Import CSV/Excel File To MySQL Database Using PHP

Submitted by alpha_luna on Saturday, April 30, 2016 - 12:49.

Good Day!!!

If you are looking for on How To Create Import CSV/Excel File To MySQL Database Using PHP then you are at the right place. In this article, we are going to learn on how to import data using CSV/Excel File to MySQL database using PHP Language. Let's start with: We are going to make our database.

Creating our Table

To create a database:
  1. Open the PHPMyAdmin
  2. Create a database and name it as "import_file".
  3. After creating a database name, click the SQL and kindly copy the code below.
  1. --
  2. -- Table structure for table `user`
  3. --
  4.  
  5. CREATE TABLE IF NOT EXISTS `user` (
  6.   `user_id` int(11) NOT NULL AUTO_INCREMENT,
  7.   `user_name` varchar(100) NOT NULL,
  8.   `first_name` varchar(100) NOT NULL,
  9.   `last_name` varchar(100) NOT NULL,
  10.   `date_added` datetime NOT NULL,
  11.   PRIMARY KEY (`user_id`)
We are going to make our database connection.

Database Connection

This PHP Script is our database. Copy and paste this then save it as "database.php".
  1. <?php
  2. mysql_select_db('import_file',mysql_connect('localhost','root',''))or die(mysql_error());
  3. ?>
We are going to make our form field.

Creating Form Field

This form field that the admin clicks the file button to import data into the database.
  1. <form action="import_query.php" method="post" name="upload_excel" enctype="multipart/form-data">
  2. <div>
  3.         <label>Import CSV/Excel File:</label>
  4.         <input type="file" multiple name="filename" id="filename">
  5.         <button type="submit" id="submit" name="submit" data-loading-text="Loading...">Upload</button>
  6. </div>
  7. </form>
We are going to create CSV/Excel File in PHP query.

CSV/Excel File - PHP Script

This is the PHP codes for CSV/Excel File to import into MySQL database.
  1. <?php
  2. if (isset($_POST['submit']))
  3. {
  4. include('database.php');
  5.  
  6. //Import uploaded file to Database
  7. $handle = fopen($_FILES['filename']['tmp_name'], "r");
  8.  
  9. while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
  10.         mysql_query("INSERT into user (user_name, first_name, last_name, date_added)
  11.         values('$data[0]', '$data[1]', '$data[2]', NOW())");
  12.        
  13.         }
  14.        
  15. fclose($handle);
  16. //print "Import done";
  17. echo "<script type='text/javascript'>alert('Successfully Imported a CSV File for User!');</script>";
  18. echo "<script>document.location='index.php'</script>";
  19. //view upload form
  20. }
  21. ?>
This table where you can view the data after importing in the database.
  1. <table border="1" cellspacing="5" cellpadding="5">
  2.         <thead>
  3.                 <tr>
  4.                         <th>UserName</th>
  5.                         <th>FirstName</th>
  6.                         <th>LastName</th>
  7.                         <th>Date Added</th>
  8.                 </tr>
  9.         </thead>
  10. <?php
  11. include ('database.php');
  12. $result= mysql_query("select * from user order by user_id ASC") or die (mysql_error());
  13. while ($row= mysql_fetch_array ($result) ){
  14. $id=$row['user_id'];
  15. ?>
  16.  
  17.         <tbody>
  18.                 <tr>
  19.                         <td><?php echo $row['user_name']; ?></td>
  20.                         <td><?php echo $row['first_name']; ?></td>
  21.                         <td><?php echo $row['last_name']; ?></td>
  22.                         <td><?php echo date("M d, Y h:i:s a",strtotime($row['date_added'])); ?></td>
  23.                 </tr>
  24.         </tbody>
  25.  
  26. <?php } ?>
  27. </table>
And, this is our style.
  1. <style type="text/css">
  2. body {
  3.         width:700px;
  4.         margin:auto;
  5. }
  6. label {
  7.         color: blue;
  8.     font-size: 18px;
  9.     font-weight: bold;
  10.     font-family: cursive;
  11.     margin-right: 10px;
  12. }
  13. input[type="file"] {
  14.         border: blue 1px solid;
  15.     padding: 8px;
  16.     color: blue;
  17.     font-size: 15px;
  18.     border-radius: 4px;
  19.     margin-right: 10px;
  20.         cursor:pointer;
  21. }
  22. button {
  23.     font-size: 18px;
  24.     border: blue 1px solid;
  25.     font-weight: bold;
  26.     padding: 8px;
  27.     background: azure;
  28.     color: blue;
  29.     border-radius: 4px;
  30.         cursor:pointer;
  31. }
  32. div {
  33.         border: blue 1px solid;
  34.     padding: 15px;
  35.     text-align: center;
  36.     border-radius: 4px;
  37.     background: azure;
  38. }
  39. table {
  40.         width: 100%;
  41.     text-align: center;
  42.     font-size: 18px;
  43.     font-family: cursive;
  44.     border: blue 1px solid;
  45.     background: azure;
  46. }
  47. th {
  48.         color:red;
  49. }
  50. td {
  51.         color:blue;
  52. }
  53. </style>
This is the result: This is the data to be imported in MySQL Database. Data After import the data in the MySQL Database. This is the result. Success So, this is it, just follow the steps to have this CSV/Excel File Imported To MySQL Database or you can download the full source code below by clicking the "Download Code" button below. Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.
Tags
#PHP. MYSQL
Import CSV/Excel to MySQL Database
php csv import
csv import
csv
Import Excel to MySQL using PHP

Comments

Submitted by tbellazi on Tue, 03/20/2018 - 07:35

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\hrexpress\import_excel_date\index.php on line 91 Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\xampp\htdocs\hrexpress\import_excel_date\index.php on line 91

Add new comment

About text formats