Export MySQL Data to CSV and Download It as a ZIP File in PHP Tutorial

Introduction

In this tutorial, you will learn How to Export MySQL Data into CSV and Download it as a Zip File using PHP Language. Here, I will show to the steps on how to achieve the said feature for your current or future PHP Projects. The main goal of this tutorial is to give the IT Students or new programmers that are new in PHP Language an idea for exporting data to CSV and zipping files in PHP.

Software Needed for this tutorial

  • Download and Install any virtual server such as XAMPP or WAMPP which contains a Apache and MySQL Server. This will let our local machine run our PHP Scripts
  • Make sure Zip Library is Enabled in your PHP.
  • Download and Install any text editor such as Notepad++, Sublime Text, VS Code, or etc.

PHP Methods and Classes to be use

  • fopen()
  • fputcsv()
  • fclose()
  • ZipArchive()
  • header()
  • readFile()

Getting Started

Before we contineu to the coding part, please make sure that your Apache and MySQL server are already started. If not, for those who are using XAMP/WAMPP, open the XAMPP/WAMPP's Control Panel and start the said servers needed.

  • Create a new folder where you will store all of your source code files. If you are using XAMPP place it inside the htdocs directory or www directory for using WAMPP.
  • Open your created source code folder into your preferred text editor.

Creation of the Dummy Database

  1. Open your preferred browser such as Chrome Browser and browse the PHPMyAdmin i.e. http://localhost/phpmyadmin.
  2. Create a New Database naming dummy_db.
  3. Navigate the page to the SQL Page and copy/paste the following MySQL Script on the provided text field and click the Go button to execute the mysql script.
  1. CREATE TABLE `mytable` (
  2. `name` varchar(255) DEFAULT NULL,
  3. `phone` varchar(100) DEFAULT NULL,
  4. `email` varchar(255) DEFAULT NULL,
  5. `address` varchar(255) DEFAULT NULL,
  6. `postalZip` varchar(10) DEFAULT NULL,
  7. `region` varchar(50) DEFAULT NULL,
  8. `country` varchar(100) DEFAULT NULL
  9.  
  10. --
  11. -- Dumping data for table `mytable`
  12. --
  13.  
  14. INSERT INTO `mytable` (`id`, `name`, `phone`, `email`, `address`, `postalZip`, `region`, `country`) VALUES
  15. (1, 'Ila Oliver', '1-752-552-7548', '[email protected]', '8663 Velit Street', '641735', 'Hưng Yên', 'Norway'),
  16. (2, 'Vanna Burris', '1-954-846-3212', '[email protected]', 'Ap #104-9504 Quisque Avenue', '4431-2434', 'Magadan Oblast', 'Mexico'),
  17. (3, 'Oren Bentley', '1-818-826-3640', '[email protected]', '527-3682 Libero. Street', '2147', 'Murcia', 'Germany'),
  18. (4, 'Maris Holder', '(504) 734-0171', '[email protected]', '1431 Consectetuer Ave', '137408', 'Mersin', 'United Kingdom'),
  19. (5, 'Melyssa Sweeney', '1-333-252-1823', '[email protected]', '810-2589 Pharetra St.', '7678', 'Amur Oblast', 'Belgium'),
  20. (6, 'Dominic Campbell', '(641) 254-5245', '[email protected]', '177-3438 Cras Rd.', '415686', 'Western Visayas', 'Philippines'),
  21. (7, 'Lamar Salas', '1-482-488-2926', '[email protected]', 'Ap #746-3981 Et St.', '850676', 'Odessa oblast', 'Vietnam'),
  22. (8, 'Sawyer Stephenson', '1-471-406-0217', '[email protected]', 'Ap #769-7889 Natoque Avenue', '795606', 'Podkarpackie', 'United States'),
  23. (9, 'Finn Tate', '1-143-735-5978', '[email protected]', 'Ap #588-6176 Auctor Street', '13-52', 'Tula Oblast', 'Peru'),
  24. (10, 'Orson Wade', '1-803-724-3385', '[email protected]', 'P.O. Box 135, 4329 Suspendisse Av.', '962442', 'Extremadura', 'Italy'),
  25. (11, 'Alfreda Booth', '1-857-295-6776', '[email protected]', '4431 Nam St.', '50032', 'South Island', 'Pakistan'),
  26. (12, 'Cally Eaton', '1-352-578-2143', '[email protected]', '866 Arcu. Rd.', '642671', 'La Libertad', 'Vietnam'),
  27. (13, 'Derek Carr', '(876) 567-7314', '[email protected]', '4730 Vulputate Av.', '414855', 'Małopolskie', 'Chile'),
  28. (14, 'Eleanor Mills', '(620) 687-4068', '[email protected]', 'P.O. Box 849, 2628 At Rd.', '56535', 'Sevastopol City', 'Singapore'),
  29. (15, 'Sydney Glenn', '(622) 137-7816', '[email protected]', 'P.O. Box 379, 8777 Rhoncus. Street', '84-98', 'Mississippi', 'Ireland'),
  30. (16, 'Nita Mclean', '1-345-774-6481', '[email protected]', '359-5447 Est Av.', '842616', 'Bahia', 'Pakistan'),
  31. (17, 'Mark Gordon', '1-478-545-5169', '[email protected]', '992-5072 Id Ave', '4322-3579', 'North Island', 'Sweden'),
  32. (18, 'Hammett Cash', '(268) 758-5423', '[email protected]', '267-4636 Orci, Av.', '96896', 'Arauca', 'Brazil'),
  33. (19, 'Scarlet Woodard', '(166) 841-5259', '[email protected]', '621-308 Elit, Rd.', '1792', 'Anambra', 'Peru'),
  34. (20, 'Rogan Rowland', '(649) 428-6578', '[email protected]', '656 Curabitur Rd.', '27304', 'Cagayan Valley', 'Poland');

Creating the Database Connection

In your text editor, create a new PHP File naming db-connect.php. Then, copy/paste the following PHP code. The code below is the script connecting the application to the database. The file will be included to other PHP files that manages data in the database.

  1. <?php
  2. $host = "localhost";
  3. $username = "root";
  4. $pw = "";
  5. $db = "dummy_db";
  6. $conn= new mysqli($host, $username, $pw, $db);
  7. if(!$conn){
  8. die("Database Connection Failed. Error:". $conn->error);
  9. }
  10.  
  11. ?>

Creating the Main Interface

The following code is an HTML File script that contains the codes for the user intarface of sample application. This file contains the also the script of displaying the MySQL Data into a Table Element. Copy/paste the code below and save it as index.php into your source code folder.

  1. <?php
  2. require_once('./db-connect.php');
  3. ?>
  4. <!DOCTYPE html>
  5. <html lang="en">
  6. <meta charset="UTF-8">
  7. <meta http-equiv="X-UA-Compatible" content="IE=edge">
  8. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  9. <title>Export Data and Zip in PHP</title>
  10.  
  11. <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
  12.  
  13. <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
  14. <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
  15. </head>
  16. <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient mb-2">
  17. <div class="container">
  18. <a class="navbar-brand" href="https://sourcecodester.com" target="_blank">SourceCodester</a>
  19. </div>
  20. </nav>
  21.  
  22. <div class="container">
  23. <h2 class="fw-bold">Export MySQL Data to CSV File and Download it as ZIP.</h2>
  24. <hr>
  25. <div class="card rounded-0">
  26. <div class="card-header">
  27. <div class="card-title h4 fw-bold">Sample Data</div>
  28. </div>
  29. <div class="card-body">
  30. <div class="container-fluid">
  31. <div class="text-end mb-2">
  32. <a class="btn btn-success btn-sm rounded-0" href="./export_data.php">Export Data</a>
  33. </div>
  34. <table class="table table-stripped table-bordered">
  35. <col width="5%">
  36. <col width="20%">
  37. <col width="15%">
  38. <col width="15%">
  39. <col width="45%">
  40. <tr class="bg-gradient bg-dark text-light border-dark">
  41. <th class="p-1 text-center">#</th>
  42. <th class="p-1 text-center">Name</th>
  43. <th class="p-1 text-center">Phone #</th>
  44. <th class="p-1 text-center">Email</th>
  45. <th class="p-1 text-center">Address</th>
  46. </tr>
  47. </thead>
  48. <?php
  49. $i = 1;
  50. $qry = $conn->query("SELECT *,CONCAT(`address`,', ',`region`, ', ', `postalZip`, ', ', `country`) as full_address FROM `myTable` order by abs(`name`) asc");
  51. while($row = $qry->fetch_assoc()):
  52. ?>
  53. <tr>
  54. <td class="px-2 py-1 align-middle text-center"><?= $i++ ?></td>
  55. <td class="px-2 py-1 align-middle"><?= $row['name'] ?></td>
  56. <td class="px-2 py-1 align-middle"><?= $row['phone'] ?></td>
  57. <td class="px-2 py-1 align-middle"><?= $row['email'] ?></td>
  58. <td class="px-2 py-1 align-middle"><?= $row['full_address'] ?></td>
  59. </tr>
  60. <?php endwhile; ?>
  61. </tbody>
  62. </table>
  63. </div>
  64. </div>
  65. </div>
  66. </div>
  67. </body>
  68. </html>
  69. <?php
  70. if(isset($conn))
  71. $conn->close();
  72. ?>
Output Exporting MYSQL Data to CSV and download it as zip

Creating the Main Function

Create a ne PHP File in your source code folder. Name the file as export_data.php. Then, copy/paste PHP code below and save it. This file contains the main function to meet our goal for this tutorial. It contains the Exporting data to CSV process and Downloading the file as a Zip file function. This script will be executed when the user clicks the Export Data button on the front-end.

  1. <?php
  2. require_once('./db-connect.php');
  3.  
  4. // File Location
  5. $filename = "sampleData.csv";
  6.  
  7. //
  8. $file = fopen($filename,'w');
  9.  
  10. // CSV Data Content
  11. $data = [];
  12.  
  13. // Data Header
  14. $data[] = ['#', 'Name', 'Phone #', 'Email', 'address'];
  15.  
  16. // Fill CSV Data Content From MySQL Database
  17. $i = 1;
  18. $qry = $conn->query("SELECT *,CONCAT(`address`,', ',`region`, ', ', `postalZip`, ', ', `country`) as full_address FROM `myTable` order by abs(`name`) asc");
  19. while($row = $qry->fetch_assoc()):
  20. $data[] = [($i++), "{$row['name']}", "{$row['phone']}", "{$row['email']}", "{$row['full_address']}"];
  21. endwhile;
  22. // End of filling Data
  23.  
  24. // Failure of opening the CSV File
  25. if($file === false){
  26. die("An Error Occurred. Error: Fialed to open ".$filename);
  27. }
  28.  
  29. foreach($data as $row){
  30. fputcsv($file, $row);
  31. }
  32.  
  33. // Close the File
  34. fclose($file);
  35.  
  36.  
  37. // Zipping the file
  38. $zip_fname = "SampleData.zip";
  39.  
  40. if(is_file($zip_fname))
  41. unlink($zip_fname);
  42.  
  43. touch($zip_fname);
  44. $zip = new ZipArchive();
  45. if($zip->open($zip_fname) === true){
  46. // Adding the CSV File to the zip file
  47. $zip->addFile($filename,$filename);
  48. $zip->close();
  49. }
  50.  
  51.  
  52. // Downloading the zip file
  53. header('Content-Type: application/octet-stream');
  54. header("Content-Transfer-Encoding: Binary");
  55. header("Content-disposition: attachment; filename=\"" . basename($zip_fname) . "\"");
  56. readfile($zip_fname);
  57.  
  58. if(isset($conn))
  59. $conn->close();
  60.  
  61. ?>
Output Downloaded File Result

The downloaded file will result as a Zip File naming SampleData.zip that contains a sampleData.csv file.

That's it! You can now test the source code on your end and check if it meets our goal for this tutorial which is to Export MySQL Data into CSV File and Download it as a Zip File. If you encountered any errors on your end, please check your source code and differentiate it from the source code I provided above. You can also download the provided zip file of the source code I created for this tutorial.

DEMO ViDEO

That's the end of this tutorial. I hope this will help you with what you are looking for and you'll find this useful for your current and future PHP Projects. Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
8 + 10 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.