How to import and export CSV files using PHP and MySQL – onlinecode
In this post we will give you information about How to import and export CSV files using PHP and MySQL – onlinecode. Hear we will give you detail about How to import and export CSV files using PHP and MySQL – onlinecodeAnd how to use it also give you demo for it if it is necessary.
Today, In this tutorial, we will be explaining how to import and export CSV files using PHP and MySQL. so let’s discuss Import and export CSV file.
It’s also very helpful in such as if you want to backup of data and you have data of CSV file then you can import the data into the database.
CSV extension stands for “Comma Separated Values” and contains all data in comma-separated. Normally, we have large data and need to import data into the database that time we use the following file types.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to Import and Export CSV files using PHP and MySQL - onlinecode</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>
<div style="margin-top:50px;">
<div >
<div ><h2>Import and export csv file</h2></div>
<div ><a href="export.php" >Export</a></div>
<div ><button type="button" data-toggle="modal" data-target="#importModal">Import</button></div>
</div>
<table >
<thead>
<tr>
<th>Id</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Email</th>
<th>Phone</th>
<th>Created</th>
</tr>
</thead>
<tbody>
<?php
include('connection.php');
$sql = 'SELECT * FROM users ORDER BY id desc';
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['id'];?></td>
<td><?php echo $row['first_name'];?></td>
<td><?php echo $row['last_name'];?></td>
<td><?php echo $row['email'];?></td>
<td><?php echo $row['phone'];?></td>
<td><?php echo $row['created'];?></td>
</tr>
<?php
} ?>
</tbody>
</table>
</div>
<!-- Modal -->
<div id="importModal" role="dialog">
<div >
<!-- Modal content-->
<div >
<div >
<button type="button" data-dismiss="modal">×</button>
<h4 >Upload Csv file</h4>
</div>
<div >
<form action="import.php" method="post" enctype="multipart/form-data">
<div >
<div >
<input type="file" name="csv_file" id="csv_file" data-icon="false">
</div>
</div>
<div >
<input type="submit" value="Upload file" id="upload_btn">
</div>
</form>
</div>
<div >
<button type="button" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</body>
</html>connection.php
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'csv_export_import';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
if(! $conn ) {
die('Could not connect: ' . mysqli_error());
}
?>export.php
<?php
// including the connection file
include('connection.php');
// set headers of csv format and force download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=users.csv');
$output = "First Name,Last Name,Email,Phonen";
$sql = 'SELECT * FROM users ORDER BY id desc';
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)) {
$output .= $row['first_name'].",".$row['last_name'].",".$row['email'].",".$row['phone']."n";
}
echo $output;
exit;
?>import.php
here, We will use the fopen() and fgetcsv() php function in the below file. The fgetcsv() method reads for the field in the csv formate and returns the array.
<?php
include('connection.php');
$csv_file = $_FILES['csv_file']['tmp_name'];
if (is_file($csv_file)) {
$input = fopen($csv_file, 'a+');
$row = fgetcsv($input, 1024, ','); // here you got the header
while ($row = fgetcsv($input, 1024, ',')) {
// insert into the database
$date = date('Y-m-d H:i:s');
$sql = 'INSERT INTO users(first_name,last_name,email,phone,created) VALUES("'.$row[0].'","'.$row[1].'","'.$row[2].'","'.$row[3].'","'.$date.'")';
mysqli_query($conn, $sql);
}
}
header('location: index.php');
?>Hope this code and post will helped you for implement How to import and export CSV files using PHP and MySQL – onlinecode. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs