Database Data Export to Excel File using PHP

Last modified on November 3rd, 2018 by Vincy.

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. Previously, we have seen more examples in PHP for database import and export.

The database data can be exported into various file format 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 into multiple excel file. If you want to see how to split the excel export into multiple files, the linked article will be useful for you.

Database Data Export to Excel File using PHP

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 a 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.

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. In a previous article, we have seen how to create and save image file programmatically while learning PHP captcha.

Database Data with Export Control

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 format like CSV, Excel, PDF and more. 

<div id="table-container">
    <table id="tab">
        <thead>
            <tr>
                <th width="35%">Product Name</th>
                <th width="20%">Price</th>
                <th width="25%">Category</th>
                <th width="20%">Average Rating</th>
            </tr>
        </thead>
        <tbody>
 
            <?php
            $query = $db_handle->runQuery("select * from tbl_products");
            if (! empty($productResult)) {
                foreach ($productResult as $key => $value) {
                    ?>
                 
                     <tr>
                <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>
            </tr>
             <?php
                }
            }
            ?>
      </tbody>
    </table>

    <div class="btn">
        <form action="" method="post">
            <button type="submit" id="btnExport" name='export'
                value="Export to Excel" class="btn btn-info">Export to
                excel</button>
        </form>
    </div>
</div>

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

<style>
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;
}
</style>

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 POST request method via 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.

<?php
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";
        }
    }
    exit();
}
?>

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`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tbl_products`
  MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;

DBController.php

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.

<?php
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;
                }		
                if(!empty($resultset))
                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.

Database Data Export to Excel File using PHP Output

Download

This PHP code tutorial was published on November 3, 2018.

↑ Back to Top

Share this Article