In this article, we will see how to export data from MySQL to CSV in PHP.
Here, we will learn about how to export data to CSV files from the database using PHP. CSV (Comma Separated Values) is the most widely used and supported file format for transferring tabular data into plain text.
So, we will fetch data from the database using PHP and MySQL. After that create a CSV file in PHP and save data in it. then we will export MySQL data and download it in a CSV file using PHP.
In this example, we will use fputcsv() to export data. The fputcsv() function formats a line as CSV and writes it to an open file.
So, let's see how to export data to CSV in PHP, PHP export CSV file, how to export data to CSV file using PHP and MySQL, and how to export data to CSV from MySQL.
Step 1: Create User Table
In this step, we will create a user table. So, add the following code.
CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`username` varchar(80) NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(70) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Step 2: Database Configuration
Now, we will create a config.php file and add the database configuration.
<?php
$host = "localhost";
$user = "root";
$password = "password";
$dbname = "csv_example";
$con = mysqli_connect($host, $user, $password, $dbname);
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
Step 3: Create HTML Form
In this step, we will create an HTML form and include a config.php file, and also display records from the database. Also, we will include a download.php file.
<?php
include "config.php";
?>
<div class="container">
<form method='post' action='download_csv.php'>
<input type='submit' value='export' name='export'>
<table border='1' style='border-collapse:collapse;'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Name</th>
<th>Email</th>
</tr>
<?php
$query = "SELECT * FROM users ORDER BY id asc";
$result = mysqli_query($con,$query);
$user_arr = array();
while($row = mysqli_fetch_array($result)){
$id = $row['id'];
$user_name = $row['username'];
$name = $row['name'];
$email = $row['email'];
$user_arr[] = array($id, $user_name, $name, $email);
?>
<tr>
<td><?php echo $id; ?></td>
<td><?php echo $user_name; ?></td>
<td><?php echo $name; ?></td>
<td><?php echo $email; ?></td>
</tr>
<?php
}
?>
</table>
<?php
$serialize_user_arr = serialize($user_arr);
?>
<textarea name='export_data' style='display: none;'><?php echo $serialize_user_arr; ?></textarea>
</form>
</div>
Step 4: Download the CSV File
Now, we will create a download_csv.php file for export data from the MySQL database.
<?php
$filename = 'users.csv';
$export_data = unserialize($_POST['export_data']);
// Create File
$file = fopen($filename,"w");
foreach ($export_data as $line){
fputcsv($file,$line);
}
fclose($file);
// Download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=".$filename);
header("Content-Type: application/csv; ");
readfile($filename);
// Deleting File
unlink($filename);
exit();
You might also like:
Top comments (0)