Breaking
Coding Birds Online
Tag

how to export table data to csv file in php gui

Browsing
How to Do

Export data from MySQL table to Excel file in PHP

By
coding-birds-online-export-data-from-mysql-table-to-excel-file-in-php-featured-image

Hello, beginners and coders, are you looking for the PHP script to export data from MySQL table to Excel file in PHP, then you are right place. Coding Birds Online is a website that provides 100% working source code. You can check this demo.

Importance & Need for this?

Exporting table data to excel is simply a reporting feature that every web application or web project needs it. Suppose you are using a web application that registers hundreds of employees a day. But unfortunately, you might not now, your application may crash or any bug can occur.

So it is always a good practice to give an option to export the complete detail of registred employees to the excel sheet so that we can have a backup. This is what we are going to code this feature in PHP. So what are you waiting for? let’s get started.

So here are the files and folders that you need to code this feature.

coding-birds-online-export-data-from-mysql-table-to-excel-file-in-php-files-and-folders
  • index.php file is the main file that shows the records from the database and has an export button.
  • export-table-script.php, this is the actual logic file that export data to excel file.
  • Common.php is the class file that fetches all the records from the database.
  • config.php is the connection file to the database.
  • bird_records.sql is the SQL table in the database, I will provide it.

index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Export table data to csv file in php - Coding Birds Online</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script src="validation.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
    <link rel="icon" href="https://codingbirdsonline.com/wp-content/uploads/2019/12/cropped-coding-birds-favicon-2-1-192x192.png" type="image/x-icon">
    <style>
        #thead>tr>th{ color: white; }
    </style>
</head>
<body>
<div style="margin-top: 20px;padding-bottom: 20px;">
    <center>
        <img width="100" src="https://codingbirdsonline.com/wp-content/uploads/2019/12/cropped-coding-birds-favicon-2-1-192x192.png"/>
        <h3>Export table data to csv file in php</h3>
    </center>
</div>
<div class="container">
    <form action="export-table-script.php" method="post">
        <table class="table table-bordered table-condensed">
            <thead id="thead" style="background-color:#ff195a">
            <tr>
                <th>Sr.No</th>
                <th>Name</th>
                <th>Class</th>
                <th>Total Markes</th>
                <th>Gender</th>
            </tr>
            </thead>
            <tbody>
            <?php
            include "config.php";
            include_once "Common.php";
            $common = new Common();
            $records = $common->getAllRecords($connection);
            if ($records->num_rows>0){
                $sr = 1;
                while ($record = $records->fetch_object()) {
                    $recordId = $record->id;
                    $name = $record->name;
                    $class = $record->class;
                    $marks = $record->marks;
                    $gender = $record->gender;?>
                    <tr>
                        <td><?php echo $sr;?></td>
                        <td><?php echo $name;?></td>
                        <td><?php echo $class;?></td>
                        <td><?php echo $marks;?></td>
                        <td><?php echo $gender;?></td>
                    </tr>
                    <?php
                    $sr++;
                }
            }
            ?>
            </tbody>
        </table>
        <button type="submit" name="exportBtn" id="exportBtn" class="btn btn-primary"style="float: right">Export Data</button>
    </form>
</div>
</body>
</html>

export-table-script.php

<?php
include "config.php";
include_once "Common.php";
$common = new Common();
$records = $common->getAllRecords($connection);
$dataTable = '';
$dataTable .='<table class="table">
                   <thead>
                       <tr>
                            <th>Sr.No</th>
                            <th>Name</th>
                            <th>Class</th>
                            <th>Total Markes</th>
                            <th>Gender</th>
                        </tr>
                    </thead>
                    <tbody>';
    if ($records->num_rows>0) {
        $sr = 1;
        while ($record = $records->fetch_object()) {
            $recordId = $record->id;
            $name = $record->name;
            $class = $record->class;
            $marks = $record->marks;
            $gender = $record->gender;
            $dataTable .='
            <tr>
                <td>'.$sr.'</td>
                <td>'.$name.'</td>
                <td>'.$class.'</td>
                <td>'.$marks.'</td>
                <td>'.$gender.'</td>
            </tr>';

            $sr++;
        }
    }
    $dataTable .= '  </tbody>
                    </table>';
    $filename = "exported-data-".date('d-m-Y H:i:s').".xls";
    header('Content-Type: application/xls');
    header('Content-Disposition: attachment; filename='.$filename);
    echo $dataTable;

config.php

<?php
$hostName = "localhost"; // host name
$username = "root";  // database username
$password = ""; // database password
$databaseName = "codingbirds"; // database name

$connection = new mysqli($hostName,$username,$password,$databaseName);
if (!$connection) {
    die("Error in database connection". $connection->connect_error);
}
?>

Common.php

<?php
class Common
{
    public function getAllRecords($connection) {
        $query = "SELECT * FROM bird_records";
        $result = $connection->query($query) or die("Error in query1".$connection->error);
        return $result;
    }
}

If you run this code you will get following out, the main home page and exported out also.

coding-birds-online-export-data-from-mysql-table-to-excel-file-in-php-screen-output

When you click the export button then the file will be download in excel format as like this.

coding-birds-online-export-data-from-mysql-table-to-excel-file-in-php-generated-output

Source Code

To download the full working source code download it from here.

Conclusion

I hope you learned everything I explained above, If you have any suggestions, are appreciated. And if you have any error comment here. If you want to download the source code then click here.

Keep Reading
By