Export excel from mysql table data using php code

Google+ Pinterest LinkedIn Tumblr

Every one know to export MySQL table data as excel sheet from the back end in server.

Export excel from mysql table data using php code

But we are going to learn Export excel from mysql table data using php code.

Step 1:
create index.php file
simple form action

.btn {
  background: #3498db;
  background-image: -webkit-linear-gradient(top, #3498db, #2980b9);
  background-image: -moz-linear-gradient(top, #3498db, #2980b9);
  background-image: -ms-linear-gradient(top, #3498db, #2980b9);
  background-image: -o-linear-gradient(top, #3498db, #2980b9);
  background-image: linear-gradient(to bottom, #3498db, #2980b9);
  -webkit-border-radius: 28;
  -moz-border-radius: 28;
  border-radius: 28px;
  font-family: Arial;
  color: #ffffff;
  font-size: 20px;
  padding: 10px 20px 10px 20px;
  text-decoration: none;

<form action="export.php" method="post" name="export_excel">
<button type="submit" id="export" name="export" class="btn btn-primary button-loading" data-loading-text="Loading...">Export CSV/Excel File</button>

step 2: Create export.php file for action. All the code and actions comes under this file

include 'config.php';    // database configuration
$SQL = "SELECT  * from data";   // select table

$header = '';
$result ='';
$exportTable = mysql_query ($SQL ) or die ( "Sql error : " . mysql_error( ) );  // export query
$rows = mysql_num_fields ( $exportTable );    // select all fields from the table
for ( $i = 0; $i < $rows; $i++ )       // check all the fields using for loop 
    $header .= mysql_field_name( $exportTable , $i ) . "\t";
while( $row = mysql_fetch_row( $exportTable ) )     // fetching all rows from the tables
    $line = '';
    foreach( $row as $value )
        if ( ( !isset( $value ) ) || ( $value == "" ) )
            $value = "\t";
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        $line .= $value;
    $result .= trim( $line ) . "\n";
$result = str_replace( "\r" , "" , $result );
if ( $result == "" )
    $result = "\nNo Record(s) Found!\n";                        
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$result";

Step 3: create config.php for database configuration

$con = mysql_connect('localhost','root','');

Result files export with excel sheet
Export excel from mysql table data using php
yes some of you thought that y should i use this code, just we can try it from back end right, you are right this is for single mysql table for export

But for multi table mysql export using joins or unions, This code is used for this kind of purpose

check this two mysql tablesExport excel from mysql table data php code

Export excel from mysql table data php
How to export multi table data with single excel sheet, Simple replace this

$SQL = "SELECT  * from data";  // change this code
$SQL = "SELECT,, data.weight, products.qid, products.serial,, products.description FROM `data` AS data INNER JOIN `products` AS products ON = products.serial";

Then the result will be
Export excel from mysql table data
Thats it enjoy the code   download code here


I'm Rajasekar - Web developer, Freelancer, Blogger and Owner of DeveloperDesks. From India lives in Bahrain. I love to do coding, Creating websites and trying different with code and designs. You Can Hire Me