Home » How to Export MySQL Database data to Excel in PHP MySQLi

How to Export MySQL Database data to Excel in PHP MySQLi

Today, We want to share with you export to excel in php.In this post we will show you create excel file in php source code, hear for export html table data to excel in php we will give you demo and example for implement.In this post, we will learn about php export to excel xlsx with an example.

Database Data Export to Excel File using PHP

Learn to export to excel in php Database data export is a popular CRUD operation for many of us. here simple you can open MySQL phpMyAdmin is the go to choice for a database client in server side PHP. It supported database administration platforms as well as also allows exporting the product, users, employees or many more data. The exported all the data can be in different formats like as a SQL, CSV, text as selected.

When you need a PHP dynamics script, that can export database data (rows) to an excel file, then this article will help you. It has complete information step by step description with example full source code. It is compact with lightweight source code for php download excel file from server that can be latest module integrated in your PHP MySQL web applications.

Read Also:  JavaScript Objects - methods array constructor and new object

We have already seen several example source code for implementing database export using PHP.

How to export MySQL data to excel sheet in PHP

index.php

<?php  
$connect = new mysqli('localhost', 'root', '');  
mysqli_select_db($connect, 'love_shops');  
$sql = "SELECT `productid`,`product_name`,`product_desc` FROM `products`";  
$setRec = mysqli_query($connect, $sql);  
$result = '';  
$result = "Product Id" . "\t" . "Product Name" . "\t" . "Product Desc" . "\t";  
$resultsetData = '';  
  while ($rec = mysqli_fetch_row($setRec)) {  
    $row = '';  
    foreach ($rec as $data) {  
        $data = '"' . $data . '"' . "\t";  
        $row .= $data;  
    }  
    $resultsetData .= trim($row) . "\n";  
}  
  
header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment; filename=Product_Detail.xls");  
header("Pragma: no-cache");  
header("Expires: 0");  

  echo ucwords($result) . "\n" . $resultsetData . "\n";  
 ?> 
 

Export MySQL data to Excel in PHP

In this simple bellow example I have learn step by step regarding how to export data to Excel in PHP. If you have developed any large web based project then that web application you have to need this service like Exporting Data to Excel, CSV or PDF Sheet. So I have developed this best Articles, in which I have create a simple PHP with MySQLi Script for Export Data from mysql database to Excel.
index.php

<?php
$conn = mysqli_connect("localhost", "root", "", "love_shops");
$sql = "SELECT * FROM tbl_products";  
$setRec = mysqli_query($conn, $sql);
?>
<html>  
 <head>  
  <title>Export MySQL data to Excel in PHP - www.pakainfo.com</title>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
 </head>  
 <body>  
  <div class="container">  
   <br />  
   <br />  
   <br />  
   <div class="table-responsive">  
    <h2 align="center">free code - Export MySQL data to Excel in PHP - www.pakainfo.com</h2><br /> 
    <table class="table table-bordered">
     <tr>  
                         <th>ProductName</th>  
                         <th>descrptions</th>  
                         <th>brand</th>  
       <th>p Code</th>
       <th>price</th>
                    </tr>
     <?php
     while($rowData = mysqli_fetch_array($setRec))  
     {  
        echo '  
       <tr>  
         <td>'.$rowData["ProductName"].'</td>  
         <td>'.$rowData["descrptions"].'</td>  
         <td>'.$rowData["brand"].'</td>  
         <td>'.$rowData["pcode"].'</td>  
         <td>'.$rowData["price"].'</td>
       </tr>  
        ';  
     }
     ?>
    </table>
    <br />
    <form method="post" action="export-to-excel.php">
     <input type="submit" name="export" class="btn btn-success" value="Export" />
    </form>
   </div>  
  </div>  
 </body>  
</html>

export-to-excel.php

<?php  
//export-to-excel.php  
$conn = mysqli_connect("localhost", "root", "", "love_shops");
$result = '';
if(isset($_POST["export"]))
{
 $query = "SELECT * FROM tbl_products";
 $setRec = mysqli_query($conn, $query);
 if(mysqli_num_rows($setRec) > 0)
 {
  $result .= '
   <table class="table" bordered="1">  
                    <tr>  
                         <th>Name</th>  
                         <th>descrptions</th>  
                         <th>brand</th>  
       <th>p Code</th>
       <th>price</th>
                    </tr>
  ';
  while($rowData = mysqli_fetch_array($setRec))
  {
   $result .= '
    <tr>  
                         <td>'.$rowData["ProductName"].'</td>  
                         <td>'.$rowData["descrptions"].'</td>  
                         <td>'.$rowData["brand"].'</td>  
       <td>'.$rowData["pcode"].'</td>  
       <td>'.$rowData["price"].'</td>
                    </tr>
   ';
  }
  $result .= '</table>';
  header('Content-Type: application/xls');
  header('Content-Disposition: attachment; filename=download.xls');
  echo $result;
 }
}
?>

Web Programming Tutorials Example with Demo

Read :

Read Also:  PHP Convert An Array To An Object Example

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about export to excel in php.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.


Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  PHP Codeigniter Insert Multiple Records Into MySQL
  2. Read Also:  Laravel 6 Global Scope Tutorial Example
  3. Read Also:  AngularJS Form Validation
  4. Read Also:  Laravel 6 Traits Example Tutorial - The PHP Framework
  5. Read Also:  How to exit a for loop or skip iteration in PHP
  6. Read Also:  PHP addslashes Function with Examples
  7. Read Also:  Send Forgot password by mail or message in PHP
  8. Read Also:  Multidimensional array Difference in php
  9. Read Also:  PHP Split Export convert Multiple Excel Sheet Files
  10. Read Also:  PHP Split Export convert Multiple Excel Sheet Files

Leave a Comment

Your email address will not be published. Required fields are marked *