Exporting MySQL Data into a CSV File in PHP Tutorial

In this tutorial, you will learn how to create a Export MySQL Data to CSV File. With the use of some built-in PHP Functions such as PHP fopen() and fputcsv() we can create a CSV file with data. This tutorial can be useful for your future projects that requires a CSV Exportation Functionality.

Here, we will create a simple PHP application with dummy MySQL data and has an anchor/button that triggers to export the data to CSV File.

Getting Started

Download XAMPP as your local web server to run our PHP Script. After Installing the virtual server, open the XAMPP's Control Panel and start the Apache Server and MySQL.

Download Bootstrap v5 for the interface design of the application that we'll be creating. After that move the library directory to the folder where you will store the source code on your end.

Creating The Database

Open a new tab in your browser and browse the XAMPP's PHPMyAdmin. Next, create a new database naming dummy_db. Then, navigate the page into the SQL Tab/Page and paste SQL Script below to the provided text field. Lastly, click the Go Button to execute the script.

  1. CREATE TABLE `authors` (
  2. `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  3. `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  4. `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  5. `birthdate` date NOT NULL,
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  7.  
  8.  
  9. INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES
  10. (1, 'Emily', 'Hegmann', '[email protected]', '1979-03-16', '1976-04-05 11:59:54'),
  11. (2, 'Miracle', 'Anderson', '[email protected]', '1975-02-02', '2006-04-13 11:34:40'),
  12. (3, 'Kendra', 'Raynor', '[email protected]', '1979-03-19', '1987-11-17 14:51:03'),
  13. (4, 'Simone', 'Huel', '[email protected]', '1971-09-30', '2006-05-13 21:56:04'),
  14. (5, 'Ena', 'Harris', '[email protected]', '2010-10-27', '2004-12-06 06:04:25'),
  15. (6, 'Emiliano', 'Kshlerin', '[email protected]', '1979-04-20', '2009-12-10 05:42:56'),
  16. (7, 'Jedediah', 'Hermiston', '[email protected]', '1976-08-01', '1997-09-06 03:35:39'),
  17. (8, 'Garfield', 'Terry', '[email protected]', '1974-02-09', '1979-07-15 10:44:20'),
  18. (9, 'Rhea', 'Keebler', '[email protected]', '2020-08-27', '2007-11-01 16:06:43'),
  19. (10, 'Reid', 'Hegmann', '[email protected]', '2008-03-15', '1983-07-07 20:01:45');

Or, you can also import the SQL File I provided along with working source code file. The file is known as dummy_db.sql and located inside the db folder.

Creating The Database Connection

Next, create a new PHP File and save it as dummy_db.sql. This file will contain the PHP Script that connects to the MySQL Database.

  1. <?php
  2. $host = 'localhost';
  3. $username = 'root';
  4. $password = '';
  5. $dbname ='dummy_db';
  6.  
  7. $conn = new mysqli($host, $username, $password, $dbname);
  8. if(!$conn){
  9. die("Cannot connect to the database.". $conn->error);
  10. }

Creating the Interface

Below is the Script of the Main Page that contains the HTML Code of the elements to be shown to the end-users. It also contains the anchor/button to export the data to CSV. Save this file as index.php.

  1. <?php require_once('db-connect.php') ?>
  2. <!DOCTYPE html>
  3. <html lang="en">
  4.  
  5. <meta charset="UTF-8">
  6. <meta http-equiv="X-UA-Compatible" content="IE=edge">
  7. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  8. <title>Export Data to CSV</title>
  9. <link rel="stylesheet" href="./Font-Awesome-master/css/all.min.css">
  10. <link rel="stylesheet" href="./css/bootstrap.min.css">
  11. <script src="./js/bootstrap.min.js"></script>
  12. :root {
  13. --bs-success-rgb: 71, 222, 152 !important;
  14. }
  15.  
  16. html,
  17. body {
  18. height: 100%;
  19. width: 100%;
  20. font-family: Apple Chancery, cursive;
  21. }
  22.  
  23. .btn-info.text-light:hover,
  24. .btn-info.text-light:focus {
  25. background: #000;
  26. }
  27. </style>
  28. </head>
  29.  
  30. <body class="bg-light">
  31. <nav class="navbar navbar-expand-lg navbar-dark bg-dark bg-gradient" id="topNavBar">
  32. <div class="container">
  33. <a class="navbar-brand" href="https://sourcecodester.com">
  34. Sourcecodester
  35. </a>
  36.  
  37. <div>
  38. <b class="text-light">Export Data to CSV</b>
  39. </div>
  40. </div>
  41. </nav>
  42. <div class="container py-5" id="page-container">
  43. <h4 class="text-center"><b>List of Authors</b></h4>
  44. <hr>
  45.  
  46. <div class="row justify-content-end my-2">
  47. <div class="col-md-4 text-end">
  48. <a href="export.php" class="btn btn-primary rounded-0 shadow" target="_blank">Export to CSV</a>
  49. </div>
  50. </div>
  51. <!-- Authors Table -->
  52. <table class="table table-striped table-bordered">
  53. <col width="5%">
  54. <col width="20%">
  55. <col width="20%">
  56. <col width="20%">
  57. <col width="20%">
  58. <col width="15%">
  59. <tr class="bg-gradient bg-primary text-light">
  60. <th class="text-center">ID</th>
  61. <th class="text-center">Firstname</th>
  62. <th class="text-center">Lastname</th>
  63. <th class="text-center">Email</th>
  64. <th class="text-center">Birthday</th>
  65. <th class="text-center">Date Added</th>
  66. </tr>
  67. </thead>
  68. <?php
  69. // Selecting all authors in the database
  70. $qry = $conn->query("SELECT * FROM `authors` order by id asc ");
  71.  
  72. // looping list of authos into a table rows
  73. while($row = $qry->fetch_assoc()):
  74. ?>
  75. <tr>
  76. <td class="text-center"><?= $row['id'] ?></td>
  77. <td><?= ucwords($row['first_name']) ?></td>
  78. <td><?= ucwords($row['last_name']) ?></td>
  79. <td><?= $row['email'] ?></td>
  80. <td><?= date("M d, Y",strtotime($row['birthdate'])) ?></td>
  81. <td><?= date("Y-m-d H:i",strtotime($row['added'])) ?></td>
  82. </tr>
  83. <?php endwhile; ?>
  84. </tbody>
  85. </table>
  86. <!-- Authors Table -->
  87. </div>
  88. <?php
  89. if(isset($conn)) $conn->close();
  90. ?>
  91. </body>
  92.  
  93. </html>

Creating The Main Function

Lastly, create a new PHP File naming export.php. This file contains the PHP Codes for exporting the MySQL Data into a CSV File.

  1. <?php
  2. // Load the database connection
  3. require_once('db-connect.php');
  4.  
  5. //CSV Filename
  6. $fname = 'author_list.csv';
  7.  
  8. // Header Row Data: Array
  9. $header = ["ID","First Name", "Last Name", "Email", "Birthday", "Date Added"];
  10.  
  11. // Selecting all authors query
  12. $qry = $conn->query("SELECT * FROM `authors` order by `id` asc");
  13.  
  14. if($qry->num_rows <= 0){
  15. echo "<script> alert('No data has fetched.'); location.href = './'; </script>";
  16. }
  17.  
  18. //Open a File
  19. $file = fopen("php://memory","w");
  20.  
  21. // Attach Header
  22. fputcsv($file, $header,',');
  23.  
  24.  
  25. // Loop the authors and put it into the CSV file
  26. while($row = $qry->fetch_assoc()){
  27. fputcsv($file, [$row['id'], $row['first_name'], $row['last_name'], $row['email'], date("M d, Y",strtotime($row['birthdate'])), date("Y-m-d H:i",strtotime($row['added']))],',');
  28. }
  29.  
  30. fseek($file,0);
  31.  
  32. // Add headers to download the file
  33. header('Content-Type: text/csv');
  34. header('Content-Disposition: attachment; filename="'.$fname.'";');
  35.  
  36. // Read File
  37. fpassthru($file);

That's it! You can now test the application in your browser and see if we have met our goal on this tutorial. If ever you have encountered any errors, please review your source code by differentiating it from the source code I provided above. You can also test the working source code I created for this tutorial. You can download the source code zip file below this article.

DEMO VIDEO

That is the end of this tutorial. I hope you'll find this Exporting MySQL Data to CSV Tutorial useful for your current and future PHP projects.

Happy Coding :)

Add new comment