Export MySQL data to CSV using PHP

To export MySQL data to CSV using PHP, we need to follow the below process:

1. Suppose you have a database with a table (let’s say a ‘user‘ table for an example).
2. Create your project folder(e.g. ‘ExportCSV‘). If you are working on local not server then you can use XAMPP for windows and LAMP for Ubuntu. In XAMPP you will have a folder ‘htdocs‘, you need to place your project folder ‘ExportCSV‘ under ‘htdocs‘.
3. Create three PHP files, one for database configurations i.e. ‘db_connection.php‘, second is ‘index.php‘ for initial page with fetched data and third is for exporting data into CSV i.e. ‘export.php‘. Put all these files under the folder ‘ExportCSV‘.

https://youtu.be/s9wa2pj3sfE

Step 1 : ‘User‘ table must have few data. Please see the example of table below:

ID	FullName	Email
1	John Doe 1	johndoe1@thetechnicaldiaries.com
2	John Doe 2	johndoe2@thetechnicaldiaries.com

Step 2 : Open file ‘db_connection.php’ in code editor and write below configuration code:

<?php 

// Database configuration 

//$db_host : database host
//$db_username : database username
//$db_password : database password
//$db_name : database name

$db_host= "localhost"; 
$db_username= "root"; 
$db_password= ""; 
$db_name     = "demo"; 
 
// 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 : Open file ‘index.php‘ in code editor and first include the ‘db_connection.php‘ file at the top, to include db-connection. And then write code to fetch data from ‘user‘ table to show in tabular format.

<?php

// include the database configuration file 
include_once 'db_connection.php';

?>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">

<!-- Export CSV link -->
<a href="export.php">Export CSV</a>

<!-- Data list in tabular format --> 

<table class="table table-striped table-bordered">
    <thead>
        <tr>
            <th>ID</th>
            <th>FullName</th>
            <th>Email</th>
        </tr>
    </thead>
    <tbody>
   <?php 
    // Fetch records from user table
    $result = $db->query("SELECT * FROM user"); 
    if($result->num_rows > 0){ 
        while($row = $result->fetch_assoc()){ 
    ?>
        <tr>
            <td><?php echo $row['ID']; ?></td>
            <td><?php echo $row['FullName']; ?></td>
            <td><?php echo $row['Email']; ?></td>
        </tr>
    <?php } }else{ ?>
        <tr><td colspan="3">No user...</td></tr>
    <?php } ?>
    </tbody>
</table>

Step 4 : Open file ‘export.php‘ and write following codes to export the data into csv:

<?php 
 
// include the database configuration file 
include_once 'db_connection.php'; 
 
// Fetch records from user table
$query = $db->query("SELECT * FROM user"); 
 
if($query->num_rows > 0){ 
    $delimiter = ","; 
    $filename = "usercsv_" . date('Y-m-d') . ".csv"; 
     
    // Create a file pointer 
    $f = fopen('php://memory', 'w'); 
     
    // Set column headers 
    $fields = array('ID', 'FullName', 'EMAIL'); 
    fputcsv($f, $fields, $delimiter); 
     
    while($row = $query->fetch_assoc()){ 

        $data = array($row['ID'], $row['FullName'], $row['email']); 
        fputcsv($f, $data, $delimiter); 
    } 
     
    fseek($f, 0); 
     
    // Set headers to download file 
    header('Content-Type: text/csv'); 
    header('Content-Disposition: attachment; filename="' . $filename . '";'); 
    fpassthru($f); 
} 
 
?>

In the above example, you will be able to export csv. You can customize it according to your logic and designing.

Download Source Code