
Do you want to import data from CSV file to MySql Database using PHP?
Probably every PHP developer needs to deal with CSV file import (we also called it as a comma separated data). So basically developer needs get the data from the CSV file and insert into the MySQL database.
In this tutorial I am going to help to import data easily from CSV file using a very basic PHP script
Let’s start with the basic example:
Sample CSV File to Import:
Here we are importing users from users.csv
file, take a look at below file, which is having basic details of users like Name, Mobile Number and Email address.
of course this is just a demo, you can use your own file to import with any format or number of columns just make sure to focus on each column accordingly
let go to the next step:
Create MySQL Sample Database and required Table for this example:
We have our CSV file ready with record set, now we need to create MySQL database table to store the records, go ahead and create table using following SQL query:
1 2 3 4 5 6 |
CREATE TABLE `users` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 100 ) NOT NULL , `mobile` VARCHAR( 20 ) NOT NULL , `email` VARCHAR( 150 ) NOT NULL ) ENGINE = MYISAM ; |
Create PHP MySQL Database connection File:
Create database connection file called db_connection.php
to connect MySQL.
Use following code to and make sure to update connection variables with your server configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?php /* * iTech Empires: How to Import Data from CSV File to MySQL Using PHP Script * Version: 1.0.0 * Page: DB Connection */ // Connection variables $host = "localhost"; // MySQL host name eg. localhost $user = "root"; // MySQL user. eg. root ( if your on local server) $password = ""; // MySQL user password (if password is not set for your root user then keep it empty ) $database = "database"; // MySQL Database name // Connect to MySQL Database $con = new mysqli($host, $user, $password, $database); // Check connection if ($con->connect_error) { die("Connection failed: " . $con->connect_error); } ?> |
PHP Script to Import CSV File Data:
Create import.php
file: this is the user interface where we are going to have feature to select and submit file to the server.
import.php:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
<?php /* * iTech Empires: How to Import Data from CSV File to MySQL Using PHP Script * Version: 1.0.0 * Page: Import.PHP */ // Database Connection require 'db_connection.php'; $message = ""; if (isset($_POST['submit'])) { $allowed = array('csv'); $filename = $_FILES['file']['name']; $ext = pathinfo($filename, PATHINFO_EXTENSION); if (!in_array($ext, $allowed)) { // show error message $message = 'Invalid file type, please use .CSV file!'; } else { move_uploaded_file($_FILES["file"]["tmp_name"], "files/" . $_FILES['file']['name']); $file = "files/" . $_FILES['file']['name']; $query = <<<eof LOAD DATA LOCAL INFILE '$file' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' IGNORE 1 LINES (name,mobile,email) eof; if (!$result = mysqli_query($con, $query)) { exit(mysqli_error($con)); } $message = "CSV file successfully imported!"; } } // View records from the table $users = '<table class="table table-bordered"> <tr> <th>No</th> <th>Name</th> <th>Mobile</th> <th>Email</th> </tr> '; $query = "SELECT * FROM users"; if (!$result = mysqli_query($con, $query)) { exit(mysqli_error($con)); } if (mysqli_num_rows($result) > 0) { $number = 1; while ($row = mysqli_fetch_assoc($result)) { $users .= '<tr> <td>' . $number . '</td> <td>' . $row['name'] . '</td> <td>' . $row['mobile'] . '</td> <td>' . $row['email'] . '</td> </tr>'; $number++; } } else { $users .= '<tr> <td colspan="4">Records not found!</td> </tr>'; } $users .= '</table>'; ?> <!doctype html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Import Data from CSV File to MySQL Tutorial</title> <!-- Bootstrap CSS File --> <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/> </head> <body> <div class="container"> <h2> Tutorial: How to Import Data from CSV File to MySQL Using PHP </h2> <br><br> <div class="row"> <div class="col-md-6 col-md-offset-0"> <form enctype="multipart/form-data" method="post" action="import.php"> <div class="form-group"> <label for="file">Select .CSV file to Import</label> <input name="file" type="file" class="form-control"> </div> <div class="form-group"> <?php echo $message; ?> </div> <div class="form-group"> <input type="submit" name="submit" class="btn btn-primary" value="Submit"/> </div> </form> <div class="form-group"> <?php echo $users; ?> </div> </div> </div> </div> </body> </html> |
We are done, try to run your code or you can easily download tutorial code from following links. let me know if you find any difficulty running the code or getting any issues.
If you want to export data from MySQL to CSV file you learn from here – Export Data from MySQL to CSV using PHP
is there any script to Upload csv file to DB and it has to check duplicate fields