Export MySQL data to Excel in PHP
0 7487
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","root","","ion8"); ?>
Create Third File - File Name: excel-export.php
<?php include("dbconnect.php"); //database connection function exportInExcel(Name, $headerRow, $data) { ini_set('max_execution_time', 1600); //increase max_execution_time to 10 min if data set is very large Content = implode("\t ", $headerRow)."\n"; foreach($data as $result) { Content .= implode("\t ", $result)."\n"; } header('Content-type: application/ms-excel'); // you can set csv format header('Content-Disposition: attachment; filename='.Name); echo Content; 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; } Name = "excel_".date("d-m-y:h:s").".xls"; // Name = "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(Name, $headerRow, $data); ?>
Do you think there are other concepts that I am include in this blog? Share with me through the comment box!
I would love to hear from you!
Also, don't forget to like this post and share it with your peers!
How to get time from MySQL Database in PHP
Share:
Comments
Waiting for your comments