Today, we are going to implement import and export CSV file using PHP and MySQL, We are use custom PHP code to implement this so let’s start.
Create MySQL Table
CREATE TABLE employee (
firstname varchar(30) NOT NULL,
lastname varchar(30) NOT NULL,
email varchar(50) DEFAULT NULL,
reg_date varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Create Database connection file
Config.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "test";
$conn = mysqli_connect($servername, $username, $password, $db);
// Check The Connection
if (mysqli_connect_errno() ){
echo "Database Connection Failed: " . mysqli_connect_error();
die;
}
?>
Let’s create the HTML form for upload CSV file. We use online bootstrap cdn to make layout looks good. Also we are add export form and display data from table using select query.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<div class="row">
<form class="form-horizontal" action="" method="post" name="upload_excel" enctype="multipart/form-data">
<!-- File Button -->
<div class="form-group">
<label class="col-md-4 control-label" for="filebutton">Select File</label>
<div class="col-md-4">
<input type="file" name="file" id="file" class="input-large">
</div>
</div>
<!-- Button -->
<div class="form-group">
<label class="col-md-4 control-label" for="singlebutton">Import data</label>
<div class="col-md-4">
<button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Import</button>
</div>
</div>
</form>
</div>
<?php
$Sql = "SELECT * FROM employee";
$result = mysqli_query($conn, $Sql);
if (mysqli_num_rows($result) > 0) {
echo "<div class='table-responsive'><table id='myTable' class='table table-striped table-bordered'><thead><tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr></thead><tbody>";
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>
<td>" . $row['firstname']."</td>
<td>" . $row['lastname']."</td>
<td>" . $row['email']."</td>
</tr>";
}
echo "</tbody></table></div>";
} else {
echo "you have no records";
}
?>
<div>
<form class="form-horizontal" method="post" name="upload_excel" enctype="multipart/form-data">
<div class="form-group">
<div class="col-md-4 col-md-offset-4">
<input type="submit" name="Export" class="btn btn-success" value="export to excel"/>
</div>
</div>
</form>
</div>
</div>
</body>
</html>
On click of import button you need to add code after config file or create new file and include it. we will store file in tmp and read it fopen function. if you wants to skip first line of csv just add fgetcsv($file); or comment this line. fgetcsv in while loop read line by line data and store in mysql.
CSV import Code
if(isset($_POST["Import"])){
$filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
// Skip the first line
fgetcsv($file);
while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
{
$firstname=addslashes($getData[1]);
$lastname=addslashes($getData[2]);
$email=addslashes($getData[3]);
$reg_date=addslashes($getData[4]);
$sql = "INSERT into employee (firstname,lastname,email,reg_date)
values ('".$firstname."','".$lastname."','".$email."','".$reg_date."')";
$result = mysqli_query($conn, $sql);
if(!isset($result))
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
}
}
?>Now, If you wants to export data in CSV file follow the below code. add header for csv and content-disposition attachment. php://output is a write-only stream that allows write access to the output buffer
CSV Export code
if(isset($_POST["Export"])){
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');
$output = fopen("php://output", "w");
fputcsv($output, array('First Name', 'Last Name', 'Email'));
$query = "SELECT * from employee";
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_array($result))
{
$data[]=array($row['firstname'],$row['lastname'],$row['email']);
fputcsv($output, $data);
}
fclose($output);
}