Categories: CSVMySqlPHP

Export Data from MySQL to CSV Using PHP

Export MySQL data to CSV

A step-by-step guide to export data to CSV from MySQL using PHP

It is a basic task for any application that needs a reporting feature to CSV; here I am going to explain how to generate a CSV file from the MySQL records.

Let’s get started:

Step 1 – Create Sample Database and Required Table along with the data:

Create Database table with sample records, here I am going to use a `users` table, which is going to have users details, go ahead and use following SQL statements to create table and insert the records. (Note: you can skip this step if you have your existing database tables)

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(40) NOT NULL,
  `last_name` varchar(40) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`) VALUES
(1, 'Jerry', 'McKenny', 'jerrymckenny@gmail.com'),
(2, 'Lillian', 'Evans', 'lillianevans@gmail.com'),
(3, 'Eva', 'Osborne', 'evaosborne@gmail.com'),
(4, 'Darlene', 'Edwards', 'darleneedwards@gmail.com'),
(5, 'Bill', 'White', 'billwhite@gmail.com');
Users Table Structure
Existing Mysql Records to Export

Step 2 Create PHP to MySQL Database Connection Script:

Create Database configuration file, which is going help us to connect to MySQL from PHP file.

<?php
/*
* iTech Empires:  Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: DB Connection
*/
// Connection variables
$host = "localhost"; // MySQL host name eg. localhost
$user = "root"; // MySQL user. eg. root ( if your on localserver)
$password = ""; // MySQL user password  (if password is not set for your root user then keep it empty )
$database = "test"; // MySQL Database name

// Connect to MySQL Database
$con = new mysqli($host, $user, $password, $database);

// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}
?>

Step 3 – Add Sample Index Page to List out the Records:

Create `index.php` file and list records.

<?php
/*
* iTech Empires:  Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: Index
*/
// Database Connection
require("db_connection.php");

// List Users
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
    exit(mysqli_error($con));
}

if (mysqli_num_rows($result) > 0) {
    $number = 1;
    $users = '<table class="table table-bordered">
        <tr>
            <th>No.</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
        </tr>
    ';
    while ($row = mysqli_fetch_assoc($result)) {
        $users .= '<tr>
            <td>'.$number.'</td>
            <td>'.$row['first_name'].'</td>
            <td>'.$row['last_name'].'</td>
            <td>'.$row['email'].'</td>
        </tr>';
        $number++;
    }
    $users .= '</table>';
}

?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Export Data from MySQL to CSV Tutorial | iTech Empires</title>
    <!-- Bootstrap CSS File  -->
    <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
    <!--  Header  -->
    <div class="row">
        <div class="col-md-12">
            <h2>Export Data from MySQL to CSV</h2>
        </div>
    </div>
    <!--  /Header  -->

    <!--  Content   -->
    <div class="form-group">
        <?php echo $users ?>
    </div>
    <div class="form-group">
        <button >
Demo Index Page to List Records

Step 4- Exporting MySQL Data to CSV file PHP Script:

Create `export.php` file to export data from Mysql to CSV.

<?php
/*
* iTech Empires:  Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: Export
*/
// Database Connection
require("db_connection.php");

// get Users
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
    exit(mysqli_error($con));
}

$users = array();
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $users[] = $row;
    }
}

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=Users.csv');
$output = fopen('php://output', 'w');
fputcsv($output, array('No', 'First Name', 'Last Name', 'Email'));

if (count($users) > 0) {
    foreach ($users as $row) {
        fputcsv($output, $row);
    }
}
?>
Export data from mysql to csv in php

Click OK to export the data to CSV and your file get’s downloaded

CSV file downloaded

If you check download file, you get records listed, as showing below:

checkout Records from CSV files

We are done, you can download the complete source for reference use following links to download or checkout live demo. Live Demo

Next you can learn how to import data from CSV to MySQL here – Import Data from CSV File to MySQL Using PHP

Yogesh Koli

Software engineer & Blogger lives in India, has 6+ years of experience working with the front-end and back-end web app development.

View Comments

  • Great one. Works all fine with me. I am just not able to get the file downloaded. The data is displayed properly but download is an issue

  • when i'm use online then it's not download the file
    just show me
    No,"First Name","Last Name",Email,"Phone Num",Address,College/university,"Seminar date","Seminar shift",Message 5,sommer,sfasdjfh,asdf@gmail.com,12344587098,fhaljkh,lkasjdhf,"Sep 15, 2018",morning,asdf

  • Thanks for nice tutorial Mysql to csv, I have problem, I don't want to export all mysql data, I just want to export first Name and email from MYSQL to CSV, How i can do that ?

    • Simply replace the SQL Query from `SELECT * FROM users` to `SELECT first_name, email from users` and update fputcsv($output, array('No', 'First Name', 'Last Name', 'Email')); to fputcsv($output, array('No', 'First Name', 'Email'));

      and your done!

Recent Posts

Complete guide of using Laravel 6 Eloquent Subquery Enhancements

Learn How to use laravel frameworks new improved feature called Eloquent Subquery and get example of using Eloquent Subqueries and…

4 months ago

3 Useful examples of using Array Map function in PHP – Best Practices

Learn how to use php array map function with easy and essential tutorial to modify arrays in php here I…

4 months ago

Working with PHP Array Filter Function – Best Practices

Learn how to use php array filter function with easy and essential tutorial to filter array in php here I…

4 months ago

How to add Access Modifiers with Constructor Parameters in TypeScript

Want to know how to refactor your Typescript class, Learn here utilising Typescript of the best and essential feature that…

4 months ago

What is Access Modifiers and how to use Access Modifiers in TypeScript ?

What is Access Modifiers in typescript, how to use Access Modifiers, when to use them, what are the advantage of…

5 months ago

Top 10 Super Useful Packages to Improve Laravel applications in 2019

This tutorial provide ultimate list of package those are top 10 on packagist and super useful to optimize your laravel…

5 months ago