Blog

MyAppBuilder Tutorial: Export Data to Excel with PHP and MySQL
PHP, Tutorial

MyAppBuilder Tutorial: Export Data to Excel with PHP and MySQL

//MyAppBuilder Tutorial: Export Data to Excel with PHP and MySQL
 
//This Tutorial Explains Export Data to Excel file with PHP and MySQL
/* Activity Files Used
a) index.php
b) export_data.php

Activity Files Used*/
//Steps1: Create MySQL Database Table
First we will create MySQL database table developer using below query to display developers records and export data into excel file.
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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
//Steps2: Get MySQL Data
We will get records from MySQL database table developers and store into an array to display records and export records into excel file.
include_once(“db_connect.php”);
$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;
}
//Steps3: Create HTML and Display Records with Export Button
In index.php, we will create HTML and display records using records array. We will also create Form with export button to export data on form submit.
<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>
</form>
</div>
</div>
<table id=”” class=”table table-striped table-bordered”>
<tr>
<th>Name</th>
<th>Gender</th>
<th>Age</th>
<th>Designation</th>
<th>Address</th>
</tr>
<tbody>
<?php foreach($developer_records as $developer) { ?>
<tr>
<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>
</tr>
<?php } ?>
</tbody>
</table>
</div>
//Steps4: Export Data to Excel
Now finally in export_data.php we will implement functionality to export data to excel with php. We will check for FORM submit POST value to export data.
if(isset($_POST[“export_data”])) {
$filename = “phpzag_data_export_”.date(‘Ymd’) . “.xls”;
header(“Content-Type: application/vnd.ms-excel”);
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”;
}
}
exit;
}
// End of the Source