Php Code for Export Data Excel and Mysql

file structure for this example is following.

Step1 : Create MySQL Database and Table

CREATE TABLE `developers` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`skills` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`designation` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`image` varchar(255) NOT NULL

Step2 : Write code to Get MySQL Data

$sql_query = “SELECT name, gender, address, designation, age FROM developers LIMIT 10”;
$resultset = mysqli_query($conn, $sql_query) or die(“database error:”. mysqli_error($conn));
$developer_records = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
$developer_records[] = $rows;

Step3 : Create HTML Display Recored and Export Button (index.php)

<div class=”container”>
<h2>Export Data to Excel with PHP and MySQL</h2>
<div class=”well-sm col-sm-12″>
<div class=”btn-group pull-right”>
<form action=”<?php echo $_SERVER[“PHP_SELF”]; ?>” method=”post”>
<button type=”submit” id=”export_data” name=’export_data’ value=”Export to excel” class=”btn btn-info”>Export to excel</button>
<table id=”” class=”table table-striped table-bordered”>
<?php foreach($developer_records as $developer) { ?>
<td><?php echo $developer [‘name’]; ?></td>
<td><?php echo $developer [‘gender’]; ?></td>
<td><?php echo $developer [‘age’]; ?></td>
<td><?php echo $developer [‘designation’]; ?></td>
<td><?php echo $developer [‘address’]; ?></td>
<?php } ?>

Step4: Export Data to Excel (export_data.php)

if(isset($_POST[“export_data”])) {
$filename = “phpzag_data_export_”.date(‘Ymd’) . “.xls”;
header(“Content-Type: application/”);
header(“Content-Disposition: attachment; filename=\”$filename\””);
$show_coloumn = false;
if(!empty($developer_records)) {
foreach($developer_records as $record) {
if(!$show_coloumn) {
// display field/column names in first row
echo implode(“\t”, array_keys($record)) . “\n”;
$show_coloumn = true;
echo implode(“\t”, array_values($record)) . “\n”;

Spread the love

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.