Exporting database data to excel using PHP

Many database clients like PhpMyAdmin, SQLYog contain the export option to extract the data or structure into a file. Implementing database data export in PHP is very simple.

The database data can be exported into various file formats like CSV, Excel, SQL. PHP contains inbuilt functions to write data into a CSV file. In this tutorial, we are going to see a PHP example to export database data to an excel file. We can export the database result in multiple excel files.

In this example, I have used the product database. The database table results are fetched from PHP and displayed in the UI with an export option. The export option will be provided by using an HTML button control. By clicking the export button the database results are written into an Excel file and downloaded to the browser with the exported data.

- Advertisement -

On triggering the export action, the PHP code will be called to perform the database data export. In this code, the header function is used to make the excel file to be downloaded to the browser by setting the content and attachment specification.

Database Data with Export Control

The below code shows the HTML table structure to display the database data in a tabular format. Product database results are fetched and stored into a PHP array. This array will be iterated to create the table rows with dynamic data.

Below the database data set, a button element will be shown to trigger the export action. The export button will submit the action to the PHP. There are various popular libraries to export tabular data to a backup file. For example, the Datatables library has the option to export tabular data into different formats like CSV, Excel, PDF, and more.

<div id="table-container">
    <table id="tab">
                <th width="35%">Product Name</th>
                <th width="20%">Price</th>
                <th width="25%">Category</th>
                <th width="20%">Average Rating</th>
            $query = $db_handle->runQuery("select * from tbl_products");
            if (! empty($productResult)) {
                foreach ($productResult as $key => $value) {
                <td><?php echo $productResult[$key]["name"]; ?></td>
                <td><?php echo $productResult[$key]["price"]; ?></td>
                <td><?php echo $productResult[$key]["category"]; ?></td>
                <td><?php echo $productResult[$key]["average_rating"]; ?></td>
    <div class="btn">
        <form action="" method="post">
            <button type="submit" id="btnExport" name='export'
                value="Export to Excel" class="btn btn-info">Export to

And the styles used in this example are shown below. These are some basic styles to make the tabular view presentable for the users.

body {
    font-size: 0.95em;
    font-family: arial;
    color: #212121;
th {
    background: #E6E6E6;
    border-bottom: 1px solid #000000;
#table-container {
    width: 850px;
    margin: 50px auto;
table#tab {
    border-collapse: collapse;
    width: 100%;
table#tab th, table#tab td {
    border: 1px solid #E0E0E0;
    padding: 8px 15px;
    text-align: left;
    font-size: 0.95em;
.btn {
    padding: 8px 4px 8px 1px;
#btnExport {
    padding: 10px 40px;
    background: #499a49;
    border: #499249 1px solid;
    color: #ffffff;
    font-size: 0.9em;
    cursor: pointer;

PHP Code to Handle Database Data Export to Excel

In the PHP code, the if condition checks if the export action triggered. By clicking the export button, the action parameter will be posted to the PHP using the POST request method via an HTML form. This request parameter is checked with a conditional statement. If the statement returns true then the PHP code for exporting database data will be executed.

First, the database column names are written to the excel file by accessing the key array using PHP array_keys(). This will be executed only on the first iteration. From the next iteration onwards a boolean variable set to stop writing the excel file header.

After writing the header, the row data will be written to the file on each iteration. After exporting all the data from the database the excel file will be downloaded to the browser. This is done by specifying the content and attachment option with the PHP header() function.

include 'DBController.php';
$db_handle = new DBController();
$productResult = $db_handle->runQuery("select * from tbl_products");
if (isset($_POST["export"])) {
    $filename = "Export_excel.xls";
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    $isPrintHeader = false;
    if (! empty($productResult)) {
        foreach ($productResult as $row) {
            if (! $isPrintHeader) {
                echo implode("\t", array_keys($row)) . "\n";
                $isPrintHeader = true;
            echo implode("\t", array_values($row)) . "\n";

MYSQL Database script

This database script contains the structure and data of the tbl_product table. Import this script into your local database and change the database configuration in the DBController.php. Then, you can run this example in your local PHP environment.

CREATE TABLE `tbl_products` (
  `id` int(8) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` double(10,2) NOT NULL,
  `category` varchar(255) NOT NULL,
  `product_image` text NOT NULL,
  `average_rating` float(3,1) NOT NULL
-- Dumping data for table `tbl_products`
INSERT INTO `tbl_products` (`id`, `name`, `price`, `category`, `product_image`, `average_rating`) VALUES
(1, 'Tiny Handbags', 100.00, 'Fashion', 'gallery/handbag.jpeg', 5.0),
(2, 'Men\'s Watch', 300.00, 'Generic', 'gallery/watch.jpeg', 4.0),
(3, 'Trendy Watch', 550.00, 'Generic', 'gallery/trendy-watch.jpeg', 4.0),
(4, 'Travel Bag', 820.00, 'Travel', 'gallery/travel-bag.jpeg', 5.0),
(5, 'Plastic Ducklings', 200.00, 'Toys', 'gallery/ducklings.jpeg', 4.0),
(6, 'Wooden Dolls', 290.00, 'Toys', 'gallery/wooden-dolls.jpeg', 5.0),
(7, 'Advanced Camera', 600.00, 'Gadget', 'gallery/camera.jpeg', 4.0),
(8, 'Jewel Box', 180.00, 'Fashion', 'gallery/jewel-box.jpeg', 5.0),
(9, 'Perl Jewellery', 940.00, 'Fashion', 'gallery/perls.jpeg', 5.0);
-- Indexes for table `tbl_products`
ALTER TABLE `tbl_products`
ALTER TABLE `tbl_products`


This is the PHP class used to handle the database connection establishment and execute the select query to get the database results. The runQuery() function fetches the product database data and store into a PHP array.

class DBController {
    private $host = "localhost";
    private $user = "root";
    private $password = "test";
    private $database = "blog_samples";
    private $conn;
        function __construct() {
        $this->conn = $this->connectDB();
    function connectDB() {
        $conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
        return $conn;
        function runQuery($query) {
                $result = mysqli_query($this->conn,$query);
                while($row=mysqli_fetch_assoc($result)) {
                $resultset[] = $row;
                return $resultset;

Export database results to excel- Output

This is the output screenshot which is displaying the tbl_product data in the tabular form.

As shown in the figure the Excel file is downloaded to the browser with the exported data by clicking the submit button below the table. Even though Excel has various use cases, spreadsheets can easily cause inconveniences due to a few minor mistakes. In order to avoid their drawbacks, you can always convert an Excel spreadsheet to web application.
Database Data Export to Excel File using PHP Output

Popular Categories

Popular Read

Samuel Jimhttps://foxstate.co/
Samuel Jim Nnamdi is the CTO of Foxstate, a platform that powers digital infrastructures for Real estate financing globally. He has over 8 years of Software Engineering and CyberSecurity expertise.

Related Articles


Please enter your comment!
Please enter your name here