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.
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.
-- -- Dumping data for table `mytable` -- (1, 'Ila Oliver', '1-752-552-7548', '[email protected]', '8663 Velit Street', '641735', 'Hưng Yên', 'Norway'), (2, 'Vanna Burris', '1-954-846-3212', '[email protected]', 'Ap #104-9504 Quisque Avenue', '4431-2434', 'Magadan Oblast', 'Mexico'), (3, 'Oren Bentley', '1-818-826-3640', '[email protected]', '527-3682 Libero. Street', '2147', 'Murcia', 'Germany'), (4, 'Maris Holder', '(504) 734-0171', '[email protected]', '1431 Consectetuer Ave', '137408', 'Mersin', 'United Kingdom'), (5, 'Melyssa Sweeney', '1-333-252-1823', '[email protected]', '810-2589 Pharetra St.', '7678', 'Amur Oblast', 'Belgium'), (6, 'Dominic Campbell', '(641) 254-5245', '[email protected]', '177-3438 Cras Rd.', '415686', 'Western Visayas', 'Philippines'), (7, 'Lamar Salas', '1-482-488-2926', '[email protected]', 'Ap #746-3981 Et St.', '850676', 'Odessa oblast', 'Vietnam'), (8, 'Sawyer Stephenson', '1-471-406-0217', '[email protected]', 'Ap #769-7889 Natoque Avenue', '795606', 'Podkarpackie', 'United States'), (9, 'Finn Tate', '1-143-735-5978', '[email protected]', 'Ap #588-6176 Auctor Street', '13-52', 'Tula Oblast', 'Peru'), (10, 'Orson Wade', '1-803-724-3385', '[email protected]', 'P.O. Box 135, 4329 Suspendisse Av.', '962442', 'Extremadura', 'Italy'), (11, 'Alfreda Booth', '1-857-295-6776', '[email protected]', '4431 Nam St.', '50032', 'South Island', 'Pakistan'), (12, 'Cally Eaton', '1-352-578-2143', '[email protected]', '866 Arcu. Rd.', '642671', 'La Libertad', 'Vietnam'), (13, 'Derek Carr', '(876) 567-7314', '[email protected]', '4730 Vulputate Av.', '414855', 'Małopolskie', 'Chile'), (14, 'Eleanor Mills', '(620) 687-4068', '[email protected]', 'P.O. Box 849, 2628 At Rd.', '56535', 'Sevastopol City', 'Singapore'), (15, 'Sydney Glenn', '(622) 137-7816', '[email protected]', 'P.O. Box 379, 8777 Rhoncus. Street', '84-98', 'Mississippi', 'Ireland'), (16, 'Nita Mclean', '1-345-774-6481', '[email protected]', '359-5447 Est Av.', '842616', 'Bahia', 'Pakistan'), (17, 'Mark Gordon', '1-478-545-5169', '[email protected]', '992-5072 Id Ave', '4322-3579', 'North Island', 'Sweden'), (18, 'Hammett Cash', '(268) 758-5423', '[email protected]', '267-4636 Orci, Av.', '96896', 'Arauca', 'Brazil'), (19, 'Scarlet Woodard', '(166) 841-5259', '[email protected]', '621-308 Elit, Rd.', '1792', 'Anambra', 'Peru'), (20, 'Rogan Rowland', '(649) 428-6578', '[email protected]', '656 Curabitur Rd.', '27304', 'Cagayan Valley', 'Poland');
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.
<?php $host = "localhost"; $username = "root"; $pw = ""; $db = "dummy_db"; $conn= new mysqli($host, $username, $pw, $db); if(!$conn){ } ?>
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.
<?php require_once('./db-connect.php'); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous"> <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> </head> <body> <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient mb-2"> <div class="container"> </div> </nav> <div class="container"> <hr> <div class="card rounded-0"> <div class="card-header"> </div> <div class="card-body"> <div class="container-fluid"> <div class="text-end mb-2"> </div> <table class="table table-stripped table-bordered"> <colgroup> <col width="5%"> <col width="20%"> <col width="15%"> <col width="15%"> <col width="45%"> </colgroup> <thead> <tr class="bg-gradient bg-dark text-light border-dark"> </tr> </thead> <tbody> <?php $i = 1; $qry = $conn->query("SELECT *,CONCAT(`address`,', ',`region`, ', ', `postalZip`, ', ', `country`) as full_address FROM `myTable` order by abs(`name`) asc"); while($row = $qry->fetch_assoc()): ?> <tr> </tr> <?php endwhile; ?> </tbody> </table> </div> </div> </div> </div> </body> </html> <?php if(isset($conn)) $conn->close(); ?>
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.
<?php require_once('./db-connect.php'); // File Location $filename = "sampleData.csv"; // // CSV Data Content $data = []; // Data Header $data[] = ['#', 'Name', 'Phone #', 'Email', 'address']; // Fill CSV Data Content From MySQL Database $i = 1; $qry = $conn->query("SELECT *,CONCAT(`address`,', ',`region`, ', ', `postalZip`, ', ', `country`) as full_address FROM `myTable` order by abs(`name`) asc"); while($row = $qry->fetch_assoc()): $data[] = [($i++), "{$row['name']}", "{$row['phone']}", "{$row['email']}", "{$row['full_address']}"]; endwhile; // End of filling Data // Failure of opening the CSV File if($file === false){ } foreach($data as $row){ } // Close the File // Zipping the file $zip_fname = "SampleData.zip"; $zip = new ZipArchive(); if($zip->open($zip_fname) === true){ // Adding the CSV File to the zip file $zip->addFile($filename,$filename); $zip->close(); } // Downloading the zip file $conn->close(); ?>
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.
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.