Export MySQL table data as Excel file using PHP

Kailash Singh

Export MySQL table data as Excel file using PHP

Published Mar 22,2021 by Kailash Singh

0 Comment     5629 Views    

In this tutorial, we are providing you, Export MySQL table data as Excel file using PHP.


Step 1 : Create index.php file.  In this file we are fetching the user details from the database and show all the user details in the table using while loop.

//Create Database Connection
<?php $con = mysqli_connect('localhost','root','','elevenstech_youtube'); ?>

//Create Export Button and pass export file path so that we can create excel file after the click on export button
<a href="export.php" class="btn btn-success" style="float: left;">Export All</a>

//Here I am showing all users details
<table class="table table-striped table-bordered">
    <tr class="btn-primary">
      //Fetch user details in user table from the database
      $query = mysqli_query($con,'SELECT * FROM users');

      $i = 1;

      while($row = mysqli_fetch_array($query)) {
      <td><?php echo $i;  ?></td>
      <td><?php echo $row['name'];  ?></td>
      <td><?php echo $row['email'];  ?></td>
      <td><?php echo $row['mobile'];  ?></td>
      <td><?php echo date('d-M-Y',strtotime($row['created_at']));  ?></td>
  <?php $i++; } ?>


Step 2 : Create export.php file. In this file when we click on export button then all users details downloaded in the excel format.


  //Create Connection of Database
  $con = mysqli_connect('localhost','root','','elevenstech_youtube');
  // Fetch data from Users table
  $result = mysqli_query($con, "SELECT * FROM users");
  //Create header of excel
  $content = '<table><tr><th>Name</th><th>EMail</th><th>Mobile</th><th>Date</th></tr>'; 

  //fetch all data with the help of mysqli_fetch_array 
  while($row = mysqli_fetch_array($result))  
  	  $content .='<tr>';
	    $content .='<td>'.$row['name'].'</td>';
	    $content .='<td>'.$row['mobile'].'</td>';
	    $content .='<td>'.$row['email'].'</td>';
	    $content .='<td>'.date('d-M-Y',strtotime($row['created_at'])).'</td>';
	    $content .='<tr>';   

  //This code is use to create excel format
  header('Content-Disposition: attachment; filename=users.xls');
  echo $content;



Comments ( 0 )