Every one know to export MySQL table data as excel sheet from the back end in server.
But we are going to learn Export excel from mysql table data using php code.
Step 1:
create index.php file
simple form action
<style> .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; } </style> <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> </form>
step 2: Create export.php file for action. All the code and actions comes under this file
<?php 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"; } else { $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
<?php $con = mysql_connect('localhost','root',''); mysql_select_db('your_database_name',$con) ?>
Result files export with excel sheet
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
How to export multi table data with single excel sheet, Simple replace this
$SQL = "SELECT * from data"; // change this code $SQL = "SELECT data.id, data.data, data.weight, products.qid, products.serial, products.name, products.description FROM `data` AS data INNER JOIN `products` AS products ON data.id = products.serial";
Then the result will be
Thats it enjoy the code download code here