Export MySQL table data as Excel file using PHP

Export MySQL table data as Excel file using PHP

Published Mar 22,2021 by Kailash Singh

0 Comment     3330 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">
  <thead>
    <tr class="btn-primary">
      <th>S.No.</th>
      <th>Name</th>
      <th>EMail</th>
      <th>Mobile</th>
      <th>Date</th>
    </tr>
  </thead>
  <tbody>
    <?php
      //Fetch user details in user table from the database
      $query = mysqli_query($con,'SELECT * FROM users');

      $i = 1;

      while($row = mysqli_fetch_array($query)) {
    ?>
    <tr>
      <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>
    </tr>
  <?php $i++; } ?>
  </tbody>
</table>

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

<?php 

  //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>';   
  } 
  $content.='</table>'; 

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

?>