Export Data from MySQL Database to CSV File Using PHP

Downloading data from the database in a user-friendly format is important to manage the information efficiently. This guide explains how to retrieve data from a MySQL database and download it as a CSV file using PHP. The process involves a straightforward HTML form and a PHP script, making it easy to implement for any beginner or experienced developer. Whether you’re handling customer data, managing product information, or storing user feedback, downloading this data in CSV format can simplify your workflow.

What is a CSV File?

CSV (Comma-Separated Values) files are simple text files that store data in a tabular format. Each line of the file represents a record, and commas separate data fields. This format is widely used for data exchange and is compatible with many database systems and spreadsheet programs such as Microsoft Excel, Google Sheets, and OpenOffice Calc.

By converting your database information to a CSV file, you can manipulate and analyze the data in spreadsheet software, making it easier to share and manage.

Step-by-Step Process to Create the Download Feature

Below is a breakdown of the key elements used in the index.php and get-data.php files to create a download button that retrieves data from a MySQL database.

HTML Form with Download Button

The HTML structure consists of a basic form with a styled button that triggers a download when clicked. Take a closer look at the code here:

				
					<!DOCTYPE html>
<html>
<head>
    <title>Download CSV Data from MySQL Database</title>
    
</head>
<body>
    <div>
        <form onclick="Myfunction()">
            <h1>Click to Download</h1>
            <img decoding="async" src="download-button-on-transparent-background-free-png.webp" class="images">
        </form>
    </div>

    
<script src="https://codingtutorials.in/wp-content/cache/min/1/c117fae932a6f4ba77fc2dadc313f24d.js" data-minify="1"></script></body>
</html>

style.css

				
					* {
        margin: 0;
        padding: 0;
        align-items: center;
        justify-content: center;
    }
    body {
        background-color: wheat;
    }
    div {
        position: absolute;
        left: 35%;
        top: 25%;
    }
    form {
        background-color: orange;
        width: 300px;
        padding: 45px;
        border-radius: 15px;
        border: 5px solid black;
    }
    h1 {
        color: white;
        text-align: center;
    }
    .images {
        width: 150px;
        height: auto;
        margin-top: 20px;
        margin-left: 23%;
    }

script.js

				
					function Myfunction() {
    var conf = confirm("Do you want to download?");
    if (conf) {
        window.location.href = "http://localhost/javascript-graph/data-excel/get-data.php";
    } else {
        alert("Please try again.");
    }
}

This HTML file uses CSS for styling, creating an attractive look that contains a clickable image. When the user clicks on the form, the JavaScript function Myfunction() is triggered, asking for confirmation to proceed with the download.

PHP Script to Generate the CSV File

The get-data.php file is a PHP script that connects to the MySQL database, retrieves data, and exports it as a CSV file. Here’s a detailed explanation of the PHP code:

				
					<?php
function createExcelFile() {
    $conn = mysqli_connect("localhost", "root", "", "registrationform");
    
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    // Query to fetch data
    $sql = "SELECT * FROM inform";
    $result = mysqli_query($conn, $sql);
    
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment;filename="sample.csv"');
    header('Cache-Control: max-age=0');

    $output = fopen('php://output', 'w');

    fputcsv($output, ['ID', 'Name', 'Email', 'Password']);

    while ($row = mysqli_fetch_assoc($result)) {
    
        fputcsv($output, [$row["id"], $row["name"], $row["email"], $row["password"]]);
    }

    fclose($output);
    
    mysqli_close($conn);
    exit();
}

createExcelFile();
?>

The script first establishes a connection to the database using mysqli_connect(). After the connection is successful, it runs a query to fetch data from the inform table. The fputcsv() function is used to write the data into a CSV file format. Headers are sent to the browser to force the file download when accessed.

Benefits of Using PHP for CSV Downloads

Simple Implementation: PHP offers a straightforward method for exporting data, making it suitable for any developer.
Flexible Data Manipulation: You can easily modify the query to fetch specific data or change the file format.
Scalability: PHP scripts can handle large datasets, allowing you to manage and download substantial amounts of information.

Conclusion

Creating a download feature using PHP and MySQL is an effective way to manage and share data. By applying this tutorial, you can easily set up a button that triggers a CSV file download, enabling better data organization and accessibility. This feature is a great addition to any web application where data export is necessary.

Feel free to customize the code and style to suit your specific needs. This guide provides a solid foundation for anyone wishing to extend the functionality of their web applications with PHP.