How to Export MySQL Data to CSV Using PHP?

Exporting data to a CSV (Comma Separated Values) file is a common task for data management. CSV files are easy to use for offline storage and data transfer between different programs. In this guide, we will walk you through the steps to export MySQL data to a CSV file using PHP. This tutorial will help you effortlessly manage your data export needs.

Step 1 : Create the Database Table :

First, you need a MySQL database with a table containing the data you want to export. For this example, we will use a users table.

				
					CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  created DATETIME NOT NULL,
  status ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2 : Create the Database Config File :

Create a dbconfig.php file to handle the database connection.

				
					<?php
// Database configuration
const DB_HOST     = "localhost";
const DB_USERNAME = "root";
const DB_PASSWORD = "";
const DB_NAME     = "DemoTutorials";

// Create database connection
$db = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

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

Step 3 : Create the Export Script :

Create a file named exportcsvfile.php to handle the export functionality.

				
					<?php 
// Include the database configuration file
require_once 'dbconfig.php'; 

// Define the CSV file name
$filename = "users_" . date('Y-m-d') . ".csv"; 

// Set the content type and headers
header('Content-Type: text/csv'); 
header('Content-Disposition: attachment; filename="' . $filename . '";'); 

// Open a file in write mode
$output = fopen('php://output', 'w'); 

// Define the columns of the CSV file
$columns = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status'); 
fputcsv($output, $columns); 

// Fetch the data from the database
$query = $db->query("SELECT * FROM users ORDER BY id DESC"); 

// If the query returns rows
if ($query->num_rows > 0) { 
    // Loop through the rows and write them to the CSV file
    while ($row = $query->fetch_assoc()) { 
        fputcsv($output, $row); 
    } 
} 

// Close the file
fclose($output); 
exit();
?>

Step 4 : Create the Main File to Trigger Export :

Create an index.php file to provide a user interface for triggering the export.

				
					<!DOCTYPE html>
<html lang="en-US">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Export MySQL Data to CSV using PHP</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-md-12 mt-5">
                <div class="float-right">
                    <a href="exportcsvfile.php" class="btn btn-primary">Export Data to CSV</a>
                </div>
                <h2 class="mt-5">User List</h2>
                <table class="table table-striped table-bordered">
                    <thead class="thead-dark">
                        <tr>
                            <th>#ID</th>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Phone</th>
                            <th>Created</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php
                    // Include the database configuration file
                    require_once 'dbconfig.php';

                    // Fetch data from the database
                    $query = $db->query("SELECT * FROM users ORDER BY id DESC");
                    if ($query->num_rows > 0) {
                        while ($row = $query->fetch_assoc()) {
                    ?>
                        <tr>
                            <td><?= $row['id'] ?></td>
                            <td><?= $row['name'] ?></td>
                            <td><?= $row['email'] ?></td>
                            <td><?= $row['phone'] ?></td>
                            <td><?= $row['created'] ?></td>
                            <td><?= $row['status'] ?></td>
                        </tr>
                    <?php } } else { ?>
                        <tr><td colspan="6">No records found...</td></tr>
                    <?php } ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</body>
</html>

Conclusion :

Exporting MySQL data to a CSV file using PHP is straightforward. This guide covers the entire process, from setting up the database to writing the export script and creating a user interface. By following these steps, you can efficiently export data and provide it for download in a widely used format. Happy coding!

How to Export MySQL Data to CSV Using PHP?

How to Export MySQL Data to CSV Using PHP?