Export MySQL data to Excel in PHP

Here below, in this article we will fetch records from database and export in into the excel format. Then excel will be auto downloaded. Follow the below steps:

Create First File – File Name: index.php

<?php

<a href="export-export.php">Export to excel</a>

?>

Create Second File – File Name: dbconnect.php

<?php session_start();

global $db;

$GLOBALS['db'] = mysqli_connect("localhost","ion8_advance","ION@4u$24)","ion8");

?>

Create Third File – File Name: excel-export.php

<?php
include("dbconnect.php"); //database connection

function exportInExcel($fileName, $headerRow, $data) {

ini_set('max_execution_time', 1600); //increase max_execution_time to 10 min if data set is very large

$fileContent = implode("\t ", $headerRow)."\n";

foreach($data as $result) {

$fileContent .=  implode("\t ", $result)."\n";

}

header('Content-type: application/ms-excel'); // you can set csv format

header('Content-Disposition: attachment; filename='.$fileName);

echo $fileContent;

exit;

}

function get_customers_lists(){

$qry = "SELECT * from users WHERE 1  order by id desc limit 0,300";

$product_query=mysqli_query($db,$qry) or die('Mysql Err1:'. mysqli_error($db));

$rec_list = array();

if($product_query)
{
    while($fetch_products=mysqli_fetch_array($product_query))
{
    $rec_list[] = $fetch_products;
}

}

return $rec_list;

}

$fileName = "excel_".date("d-m-y:h:s").".xls";

// $fileName = "report_".date("d-m-y:h:s").".csv";

?>

<?php

$fetch_list = get_customers_lists();

$res_data = array();

$headerRow = array('Uid','Name','Email','Mobile','City','State', 'Country','Zip Code','Status','Updated','Date Added');  

foreach($fetch_list as $fetch_detail){

$res_data['id'] = $fetch_detail['id'];

$res_data['name'] = $fetch_detail['name'];

$res_data['email'] = $fetch_detail['email'];

$res_data['mobile'] = $fetch_detail['mobile'];

$res_data['city'] = ucfirst($fetch_detail['city']);

$res_data['state'] = ucfirst($fetch_detail['state']);

$res_data['country'] = ucfirst($fetch_detail['country']);

$res_data['zipcode'] = ucfirst($fetch_detail['zipcode']);

$res_data['status'] = ucfirst($fetch_detail['status']);

$res_data['timestamp'] = ucfirst($fetch_detail['timestamp']);

$res_data['date_added'] = ucfirst($fetch_detail['date_added']);

$data[] = $res_data;

}

exportInExcel($fileName, $headerRow, $data);

?>

Leave a Reply

avatar
  Subscribe  
Notify of