How to export MySQL Data into a CSV File PHP Source Code

How to export MySQL Data into a CSV File PHP Source Code

In this post you will learn about How to export MySQL Data into a CSV File PHP Source Code. Read this below article carefully and understand this to put impact in real life.

This article will show you how to produce a CSV file from MySQL data. We may build a CSV file with data using several built-in PHP functions as PHP fopen() and fputcsv(). This guide will come in handy for any future tasks that require CSV exportation.

We’ll make a small PHP application with dummy MySQL data and an anchor/button that causes the data to be exported to a CSV file.

The First Steps

To execute our PHP script, install XAMPP as your local web server. Open the XAMPP Control Panel after installing the virtual server and start the Apache Server and MySQL.

For the UI design of the app that we’ll be making, download Bootstrap v5. Then, on your end, copy the library directory to the location where you’ll keep the source code.

Developing a Database

Open a new tab in your browser and go to the PHPMyAdmin section of XAMPP. Create a new database called dummy_db next. Then, navigate to the SQL Tab/Page and paste the SQL Script below into the text space given. Finally, press the Go Button to run the script.

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

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.

$host     = 'localhost';
$username = 'root';
$password = '';
$dbname   ='dummy_db';
$conn = new mysqli($host, $username, $password, $dbname);
    die("Cannot connect to the database.". $conn->error);

Designing the User Interface

The Main Page’s Script, which contains the HTML Code for the items to be displayed to end-users, is shown below. It also has a link/button for exporting the data to CSV. This file should be saved as index.php.

<?php require_once('db-connect.php') ?>
<!DOCTYPE html>
<html lang="en">
    <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 to CSV</title>
    <link rel="stylesheet" href="./Font-Awesome-master/css/all.min.css">
    <link rel="stylesheet" href="./css/bootstrap.min.css">
    <script src="./js/bootstrap.min.js"></script>
        :root {
            --bs-success-rgb: 71, 222, 152 !important;
        body {
            height: 100%;
            width: 100%;
            font-family: Apple Chancery, cursive;
        .btn-info.text-light:focus {
            background: #000;
<body class="bg-light">
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark bg-gradient" id="topNavBar">
        <div class="container">
            <a class="navbar-brand" href="">
                <b class="text-light">Export Data to CSV</b>
    <div class="container py-5" id="page-container">
        <h4 class="text-center"><b>List of Authors</b></h4>
        <div class="row justify-content-end my-2">
            <div class="col-md-4 text-end">
                <a href="export.php" class="btn btn-primary rounded-0 shadow" target="_blank">Export to CSV</a>
        <!-- Authors Table -->
        <table class="table table-striped table-bordered">
                <col width="5%">
                <col width="20%">
                <col width="20%">
                <col width="20%">
                <col width="20%">
                <col width="15%">
                <tr class="bg-gradient bg-primary text-light">
                    <th class="text-center">ID</th>
                    <th class="text-center">Firstname</th>
                    <th class="text-center">Lastname</th>
                    <th class="text-center">Email</th>
                    <th class="text-center">Birthday</th>
                    <th class="text-center">Date Added</th>
                // Selecting all authors in the database
                $qry = $conn->query("SELECT * FROM `authors` order by id asc ");
                // looping list of authos into a table rows
                while($row = $qry->fetch_assoc()):
                    <td class="text-center"><?= $row['id'] ?></td>
                    <td><?= ucwords($row['first_name']) ?></td>
                    <td><?= ucwords($row['last_name']) ?></td>
                    <td><?= $row['email'] ?></td>
                    <td><?= date("M d, Y",strtotime($row['birthdate'])) ?></td>
                    <td><?= date("Y-m-d H:i",strtotime($row['added'])) ?></td>
                <?php endwhile; ?>
        <!-- Authors Table -->
if(isset($conn)) $conn->close();

Developing The Main Function

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

// Load the database connection
//CSV Filename
$fname = 'author_list.csv';
// Header Row Data: Array
$header = ["ID","First Name", "Last Name", "Email", "Birthday", "Date Added"];
// Selecting all authors query
$qry = $conn->query("SELECT * FROM `authors` order by `id` asc");
if($qry->num_rows <= 0){
    echo "<script> alert('No data has fetched.'); location.href = './'; </script>";
//Open a File
$file = fopen("php://memory","w");
// Attach Header
fputcsv($file, $header,',');
// Loop the authors and put it into the CSV file
while($row = $qry->fetch_assoc()){
    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']))],',');
// Add headers to download the file
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$fname.'";');
// Read File 


Final Words

In this post you learnt about How to export MySQL Data into a CSV File PHP Source Code. If you like this article please share with your friends and family to support our website. We wrote this article by researching on internet to find best content for you. You can find more articles like this on our website for free. We provided you some important tips regarding this topic by following them you can easily understand. If you need more information regarding How to export MySQL Data into a CSV File PHP Source Code you can ask in comments or you can contact us via contact us form available on this website. Thanks for reading and sharing.

2 Comments on “How to export MySQL Data into a CSV File PHP Source Code”

  1. I was very happy to discover this website. I want to to thank you for your time due to this fantastic read!! I definitely liked every little bit of it and i also have you book marked to check out new things on your blog.

Leave a Reply

Your email address will not be published. Required fields are marked *