A CSV (comma-separated values) file stores the tabular data in plain text format. Basically, CSV file format is used to import to or export from the table data. Each line of the CSV file is a data record that consists of one or more fields. When there is needed to add the huge data into the MySQL database, it’s very time-consuming to add data one by one. In that situation, import feature helps to insert a bunch of data in one click.
Using CSV file you can store all the data and import the CSV file data into the database at once using PHP and MySQL. Import CSV into MySQL helps to save the user time and avoid repetitive work. In this tutorial, we will show you how to upload and import CSV file data into MySQL database using PHP.
Here we’ll build an example script to import members data into the database. According to this script functionality, the user would be able to upload a CSV file of members details and members data would be inserted into the MySQL database using PHP.
Create Database Table
You should create a table (members) in the database to store the member’s data. The following SQL creates a members table with some basic fields.
CREATE TABLE `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` datetime NOT NULL, `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Connect to the Database (dbConfig.php)
The dbConfig.php helps to connect and select the database.
<?php
//DB details
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'solutiondoodz';
//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if ($db->connect_error) {
die("Unable to connect database: " . $db->connect_error);
}
CSV File Upload (index.php)
In this file, existing members are listed with the Import Members link. Once Import Members link is clicked, a form will appear to choose and upload a .csv file. This form would be submitted to the importData.php file for importing the member’s data to the database.
<?php
//load the database configuration file
include 'dbConfig.php';
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 = '';
}
}
?> <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="importData.php" 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>
The bootstrap table structure is used in the above HTML. So, don’t forget to include the bootstrap libraries.
<link rel="stylesheet" href="http://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="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
Import CSV Data to Database (importData.php)
The following works are processed in this file.
- Validates the uploaded file whether it is a valid
.csvfile. - The
is_uploaded_file()function is checks whether the CSV file is uploaded via HTTP POST. - The
fopen()function opens the CSV file in read-only mode. - The
fgetcsv()function is used to parse the member’s data from the open CSV file. This function is called in the while loop to parse data from CSV file. - Members data are inserted into the database using PHP and MySQL.
- At last, the user is redirected to the
index.phppage with importing status code.
<?php
//load the database configuration file
include 'dbConfig.php';
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);
.png){kind=logo}
0 Comments