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

Submitted by oretnom23 on Thursday, June 16, 2022 - 11:06.

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

Open your preferred browser such as Chrome Browser and browse the PHPMyAdmin i.e. http://localhost/phpmyadmin.
Create a New Database naming dummy_db.
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.

CREATE TABLE `mytable` (
  `id` mediumint(8) UNSIGNED NOT NULL Primary Key AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `postalZip` varchar(10) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
--
-- Dumping data for table `mytable`
--
 
INSERT INTO `mytable` (`id`, `name`, `phone`, `email`, `address`, `postalZip`, `region`, `country`) VALUES
(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');

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.

<?php
$host = "localhost";
$username = "root";
$pw = "";
$db = "dummy_db";
$conn= new mysqli($host, $username, $pw, $db);
if(!$conn){
    die("Database Connection Failed. Error:". $conn->error);
}
 
?>

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.

<?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">
    <title>Export Data and Zip in PHP</title>
 
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
 
    <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>
    <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">
        <a class="navbar-brand" href="https://sourcecodester.com" target="_blank">SourceCodester</a>
        </div>
    </nav>
 
    <div class="container">
        <h2 class="fw-bold">Export MySQL Data to CSV File and Download it as ZIP.</h2>
        <hr>
        <div class="card rounded-0">
            <div class="card-header">
                <div class="card-title h4 fw-bold">Sample Data</div>
            </div>
            <div class="card-body">
                <div class="container-fluid">
                    <div class="text-end mb-2">
                        <a class="btn btn-success btn-sm rounded-0" href="./export_data.php">Export Data</a>
                    </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">
                                <th class="p-1 text-center">#</th>
                                <th class="p-1 text-center">Name</th>
                                <th class="p-1 text-center">Phone #</th>
                                <th class="p-1 text-center">Email</th>
                                <th class="p-1 text-center">Address</th>
                            </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>
                                <td class="px-2 py-1 align-middle text-center"><?= $i++ ?></td>
                                <td class="px-2 py-1 align-middle"><?= $row['name'] ?></td>
                                <td class="px-2 py-1 align-middle"><?= $row['phone'] ?></td>
                                <td class="px-2 py-1 align-middle"><?= $row['email'] ?></td>
                                <td class="px-2 py-1 align-middle"><?= $row['full_address'] ?></td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</body>
</html>
<?php
if(isset($conn))
$conn->close();
?>

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.

<?php 
require_once('./db-connect.php');
 
// File Location
$filename = "sampleData.csv";
 
// 
$file = fopen($filename,'w');
 
// 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){
    die("An Error Occurred. Error: Fialed to open ".$filename);
}
 
foreach($data as $row){
    fputcsv($file, $row);
}
 
// Close the File
fclose($file);
 
 
// Zipping the file
$zip_fname = "SampleData.zip";
 
if(is_file($zip_fname))
    unlink($zip_fname);
 
    touch($zip_fname);
$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
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary"); 
header("Content-disposition: attachment; filename=\"" . basename($zip_fname) . "\"");
readfile($zip_fname); 
 
if(isset($conn))
$conn->close();
 
?>

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

2101 views