Write data into CSV file using PHP

Sometimes we need to generate CSV file containing data from database table. So, in this tutorial, we are going to learn how to write data into CSV file using PHP. PHP has a default function fputcsv(), through which we can write data into CSV file. In this code, we will fetch data from MySQL table and generate a CSV file.

Database – Write data into CSV file

1. CREATE TABLE IF NOT EXISTS `user_details` (
2. `id` int(11) NOT NULL AUTO_INCREMENT,
3. `name` varchar(50) NOT NULL,
4. `mobile` bigint(10) NOT NULL,
5. `country` varchar(50) NOT NULL,
6. PRIMARY KEY (`id`)
7. );
10. INSERT INTO `user_details` (`id`, `name`, `mobile`, `country`) VALUES
11. (1, 'Aritra Samanta', 9999999991, 'India'),
12. (2, 'Rina Saha', 9999999992, 'Australia'),
13. (3, 'Anil Das', 9999999993, 'London'),
14. (4, 'Akash Samanta', 9999999994, 'America'),
15. (5, 'Niharika Roy', 9999999995, 'Bulgeria');

Create the above table into the database and insert the example data into that table.

1. <?php
2. define('HOSTNAME','localhost');
3. define('DB_USERNAME','database_username');
4. define('DB_PASSWORD','database_password');
5. define('DB_NAME', 'database_name');
7. $con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
8. //Check connection
9. if(mysqli_connect_errno($con)) echo "Failed to connect MySQL: " .mysqli_connect_error();
10. ?>

Create a PHP file “db.php” in the root folder and write the above database connection code into it. Change the database credentials as yours.

HTML code – Write data into CSV file

1. <?php
2. include("db.php");
3. $sql = "select * from `user_details`";
4. $res = mysqli_query($con, $sql);
5. if(mysqli_num_rows($res) > 0) {
6. ?>
7. <form action="csv.php" method="post">
8. <input type="submit" name="submitBtn" id="submitBtn" value="Generate CSV" />
9. <table id="100%" cellpadding="5" cellspacing="1">
10. <tbody>
11. <tr>
12. <th>SL.</th>
13. <th>NAME</th>
14. <th>MOBILE</th>
15. <th>COUNTRY</th>
16. </tr>
17. <?php
18. while($row = mysqli_fetch_array($res)) {
19. ?>
20. <tr>
21. <td align="center"><?php echo $row['id']; ?>.</td>
22. <td><?php echo $row['name']; ?></td>
23. <td align="center"><?php echo $row['mobile']; ?></td>
24. <td><?php echo $row['country']; ?></td>
25. </tr>
26. <?php
27. }
28. ?>
29. </tbody>
30. </table>
31. </form>
32. <?php
33. }
34. ?>

Create a PHP file “index.php” in the root folder and write the above contents in it. This page simply displays the contents of the table in a tabular form and a submit button which generates a CSV file based on the table data.

PHP code – Write data into CSV file

1. <?php
2. include("db.php");
3. if(isset($_POST['submitBtn'])) {
5. $filename = date('d-m-Y_H-i-s').".csv";
6. $file = fopen("php://output","w");
7. header('Content-type: application/csv');
8. header('Content-Disposition: attachment; filename='.$filename);
10. //Here we fetch the column names of the table and write these into the CSV file
11. $query = "select column_name from information_schema.columns where table_schema='database_name' and table_name='user_details'"
12. $result = mysqli_query($con, $query);
13. while ($row = mysqli_fetch_row($result)) {
14. $column_header[] = $row[0];
15. }
16. fputcsv($file, $column_header);
19. //Here we fetch the data from the table and write these data into the CSV file row wise.
20. $sql = "select * from `user_details`";
21. $res = mysqli_query($con, $sql);
22. if(mysqli_num_rows($res) > 0) {
23. while($row = mysqli_fetch_assoc($res)) {
24. fputcsv($file, $row);
25. }
26. }
27. }
28. ?>

Create another PHP file “csv.php” in the root folder and write the above contents in it.

1. $filename = date('d-m-Y_H-i-s').".csv";
2. $file = fopen("php://output","w");
3. header('Content-type: application/csv');
4. header('Content-Disposition: attachment; filename='.$filename);

The filename is dynamic, a combination of date and time. The second, third and fourth line forced to download the file as a CSV file instead of open the page in the browser.

1. $query = "select column_name from information_schema.columns where table_schema='database_name' and table_name='user_details'";
2. $result = mysqli_query($con, $query);
3. while ($row = mysqli_fetch_row($result)) {
4. $column_header[] = $row[0];
5. }
6. fputcsv($file, $column_header);

The above code performs a simple query and fetches the column names of the table and stores in an array variable “$column_header“. fputcsv($file, $column_header) writes the column names in the CSV file as the first row.

1. $sql = "select * from `user_details`";
2. $res = mysqli_query($con, $sql);
3. if(mysqli_num_rows($res) > 0) {
4. while($row = mysqli_fetch_assoc($res)) {
5. fputcsv($file, $row);
6. }
7. }

Fetch all data from the table and writes them one by one into the CSV file.

Download the full source code from the below download link and please like and share this tutorial link to others.

Download Source Code