The phpMyAdmin allows us different ways to export the Table data. One of the method is CSV(Comma Separated Value).
There are two ways to create a CSV file with PHP –
- By comma-separated string, or
- using fputcsv() method
I am using the fputcsv() method (writes an array to the file line by line) in the demonstration, where I am listing MySQL table data in the Tabular format and a button to export data.
When the export button gets clicked then creating a new CSV file and download it.
Contents
1. Create a Table
I am using users
table in the example.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(50) NOT NULL, `gender` varchar(10) NOT NULL, `email` varchar(70) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Database Configuration
Create a new config.php
to define database connection.
Completed Code
<?php $host = "localhost"; /* Host name */ $user = "root"; /* User */ $password = ""; /* Password */ $dbname = "tutorial"; /* Database name */ $con = mysqli_connect($host, $user, $password,$dbname); // Check connection if (!$con) { die("Connection failed: " . mysqli_connect_error()); }
3. HTML and PHP
Create a <form>
that have a table layout, Textarea, and submit button.
Fetch and display users
Table data in <table>
. Create an Array variable that initializes while creating a new row.
Store the array value in the <textarea>
after serializing the data using serialize() function.
Handle <form >
submit on download.php
page.
Completed code
<?php include "config.php"; ?> <div class="container"> <form method='post' action='download.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>Gender</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']; $uname = $row['username']; $name = $row['name']; $gender = $row['gender']; $email = $row['email']; $user_arr[] = array($id,$uname,$name,$gender,$email); ?> <tr> <td><?php echo $id; ?></td> <td><?php echo $uname; ?></td> <td><?php echo $name; ?></td> <td><?php echo $gender; ?></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>
4. Create and Download CSV file
Create a download.php
file for CSV export.
Unserialize the $_POST['export_data']
value using unserialize() function which returns an array and using this to insert data in open file using fputcsv()
method.
Within
fputcsv()
passing two parameters –
- File variable and
- An array value.
After successfully file creation prepare it for download and delete it after downloading using unlink()
method.
Completed code
<?php $filename = 'users.csv'; $export_data = unserialize($_POST['export_data']); // file creation $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();
5. Demo
6. Conclusion
I exported a MySQL table data for this created an array of the content and write to the open file with fputcsv()
.
You can use it to generate a CSV output of a report.
If you want to know how to import CSV file data into a MySQL database table you can view my earlier tutorial.
If you found this tutorial helpful then don't forget to share.