Export data from MySQL to excel using php


We all deal with data and numbers while designing the web applications but what if your client asks you to extract the data from the database and export it to MS Excel file for later review?

In this tutorial, we are going to learn how we can export our data from MySQL database to MS Excel file.

Export data from MySQL to excel using PHP

Let’s begin with creating a simple database ‘Pet_Store’.

Creating the database

Now create a table ‘products’

Table products

Add data to the table created above.

Data View

Next, create two PHP files: petfood.php and petstore_ac.php

petfood.php will display data in form of a table with a link ‘Export to Excel’

<!DOCTYPE html>

<html>
<head>
  <link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<style>
body{font-family: 'Open Sans', sans-serif; color:#333; font-size:14px;}
#book_form{padding:50px;}
label{display:inline-block; width:140px; }
th, td{width:120px;}
table{width:50%; text-align:left;}

</style>

</head>
<body>
  <?php
  echo "<table>";
  $conn = mysqli_connect("localhost","root","","pet_store");
  if(!$conn){
      echo "Error: Unable to connect to MySql";
      die();
  }
  echo "<table>
    <thead>
      <tr>
        <th>Product ID</th>
        <th>Name</th>
        <th>Weight</th>
        <th>price</th>
      </tr>
    </thead>
    <tbody>";
  $query = "select * from products";
  $result = mysqli_query($conn, $query);
  while($row = mysqli_fetch_array($result)){
    ?>
    <tr>
      <td><?php echo $row['prod_id']; ?></td>
      <td><?php echo $row['name']; ?></td>
      <td><?php echo $row['weight']; ?></td>
      <td><?php echo $row['price']; ?></td>

    </tr>
    <?php
  }
  echo "</tbody></table>";
  echo "<a href=\"petstore_ac.php\"> Export To Excel </a>";
?>
</body>
</html>

Now, execute petfood.php

Products List

Create the file Petstore_ac.php. This file contains the actual code behind the ‘Export to Excel’ action link.

<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=pet_store.xls");
header("Pragma: no-cache");
header("Expires: 0");

$conn = mysqli_connect("localhost","root","","pet_store");
if(!$conn){
    echo "Error: Unable to connect to MySql";
    die();
}
$heading = array("Product ID", "Name", "Weight", "Price");
$table_heading = implode("\t", $heading);


$setData = '';
$query = "select * from products";
$result  = mysqli_query($conn, $query);
while ($row = mysqli_fetch_row($result)) {
    $rowData = '';
    foreach ($row as $value) {
        $value = '"' . $value . '"' . "\t";
        $rowData .= $value;
    }
    $setData .= trim($rowData) . "\n";
}

echo ucwords($table_heading) . "\n" . $setData . "\n";

?>

Explanation

header(“Content-type: application/vnd.ms-excel”);

The Content-type indicates the media type of the resource – which is Microsoft Excel in this case.

header(“Content-Disposition: attachment; filename=file_name.xls”);

The Content-Disposition is a header indicating if the content is expected to be displayed inline or as a downloadable attachment. In this case, we need data to be downloadable while exporting it to file_name.xls.

Related Articles

Export data from MySQL to JSON using PHP

Insert Input values into database using PHP

File Handling in PHP

Tutorials Panel

About Tutorials Panel

programming tutorial and source code for Software Engineers and developers. https://twitter.com/TutorialsPanel

View all posts by Tutorials Panel →

Leave a Reply