Export MySQL Database into CSV File using PHP/MySQLi

February 1, 2021
PHP
export mysql database into csv file

Tutorial: Export MySQL Database into CSV File using PHP/MySQLi with Source Code

Getting Started on How to Export MySQL Database into CSV File

This is a tutorial on how to Export MySQL Database into CSV File. I’ve used bootstrap to improve the design of the presentation of this tutorial. This bootstrap is included in the downloadable of this tutorial but, if you want, you may download bootstrap using this link.

Related Tutorials: Fetch Data from MySQL Database using Vuejs in PHP, How to Fetch MySQL Data Using AngularJS in PHP

Creating a Database

Next, we create our MySQL database that we are going to export into CSV file.

I’ve included a .sql file in the downloadable of this tutorial which is a MySQL database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.

You should be able to create a database with tables named database.

export mysql database into csv file

Creating a Link to Export

Next, we are going to create a link that directs to our export code. We can do this by creating our index file named index.php. Also, I’ve included a table that shows the data that we are going to export.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Export MySQL Database into CSV File using PHP/MySQLi</title>
    <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <h1 class="page-header text-center">Export MySQL Database into CSV File</h1>
    <div class="row">
        <div class="col-sm-8 col-sm-offset-2">
            <div class="row">
                <div class="col-sm-1">
                    <a href="export.php" class="btn btn-primary btn-sm">Export</a>
                </div>
                <div class="col-sm-11">
                    <?php
                        session_start();
 
                        if(isset($_SESSION['message'])){
                            echo $_SESSION['message'];
 
                            unset($_SESSION['message']);
                        }
                    ?>
                </div>
            </div>
            <table class="table table-bordered table-striped" style="margin-top:20px;">
                <thead>
                    <th>UserID</th>
                    <th>Firstname</th>
                    <th>Lastname</th>
                    <th>Address</th>
                </thead>
                <tbody>
                    <?php
                    //connection
                    $conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
                    $sql = "SELECT * FROM members";
                    $query = $conn->query($sql);
 
                    while($row = $query->fetch_array()){
                        ?>
                        <tr>
                            <td><?php echo $row['id']; ?></td>
                            <td><?php echo $row['firstname']; ?></td>
                            <td><?php echo $row['lastname']; ?></td>
                            <td><?php echo $row['address']; ?></td>
                        </tr>
                        <?php
                    }
 
                    ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

Creating an Export Script

Lastly, we create our export script by creating a new file, name it as export.php and paste the below code.

<?php
    session_start();
    //connection
    $conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
    $sql = "SELECT * FROM members";
    $query = $conn->query($sql);
 
    if($query->num_rows > 0){
        $delimiter = ',';
        //create a download filename
        $filename = 'members.csv';
 
        $f = fopen('php://memory', 'w');
 
        $headers = array('Id', 'Firstname', 'Lastname', 'Address');
    	fputcsv($f, $headers, $delimiter);
 
    	while($row = $query->fetch_array()){
            $lines = array($row['id'], $row['firstname'], $row['lastname'], $row['address']);
            fputcsv($f, $lines, $delimiter);
        }
 
        fseek($f, 0);
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '";');
        fpassthru($f);
        exit;
    }
    else{
        $_SESSION['message'] = 'Cannot export. Data empty';
        header('location:index.php');
    }
?>

That ends this tutorial. Happy Coding!

Facebook Comments

Leave a Reply

Your email address will not be published.