PHP 7 Script to Export MySQL Table Data to CSV File & Download it as Attachment in Browser

Welcome folks today in this blog post we will be exporting the mysql table data to csv file and download it as an attachment in browser using php. All the full source code of the application is given below.

Get Started

In order to get started you need to start the mysql server using the xammp control panel as shown below.

And now you need to create the table of users and it will contain the four columns as shown below

And now you can insert the sample data inside the table as shown below

Now you need to make an index.php file inside the root directory and copy paste the following code

index.php

<?php

// Connect to the database
$host = "localhost";
$user = "root";
$password = "";
$dbname = "exceldb";

$conn = mysqli_connect($host, $user, $password, $dbname);

if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

<?php

// Connect to the database

$host = "localhost";

$user = "root";

$password = "";

$dbname = "exceldb";

$conn = mysqli_connect($host, $user, $password, $dbname);

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

As you can see we are connecting to the database using the php code and after that we will be fetching all the data using the select statement as shown below

// Query to get the data
$query = "SELECT * FROM users";
$result = mysqli_query($conn, $query);

// Query to get the data

$query = "SELECT * FROM users";

$result = mysqli_query($conn, $query);

Exporting to CSV and Download it in Browser

And now we will be writing the php code which will actually convert the mysql data to the csv file and download it as an attachment

// Check if there are any results
if (mysqli_num_rows($result) > 0) {

  // Set the filename
  $filename = "export.csv";

  // Open the file
  $fp = fopen('php://output', 'w');

  // Add the headers
  $headers = array();
  while ($row = mysqli_fetch_assoc($result)) {
    $headers = array_keys($row);
    break;
  }
  fputcsv($fp, $headers);

  // Add the data
  while ($row = mysqli_fetch_assoc($result)) {
    fputcsv($fp, $row);
  }

  // Close the file
  fclose($fp);

  // Set the headers to download the file
  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="' . $filename . '"');

  // Send the data to the browser
  readfile('php://output');
}

// Close the connection
mysqli_close($conn);

?>

// Check if there are any results

if (mysqli_num_rows($result) > 0) {

// Set the filename

$filename = "export.csv";

// Open the file

$fp = fopen('php://output', 'w');

// Add the headers

$headers = array();

while ($row = mysqli_fetch_assoc($result)) {

$headers = array_keys($row);

break;

}

fputcsv($fp, $headers);

// Add the data

while ($row = mysqli_fetch_assoc($result)) {

fputcsv($fp, $row);

}

// Close the file

fclose($fp);

// Set the headers to download the file

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename="' . $filename . '"');

// Send the data to the browser

readfile('php://output');

}

// Close the connection

mysqli_close($conn);

As you can see we are using the while loop to insert all the data into a single csv file using the fputcsv() method and then we are setting the headers to download the csv file as an attachment in the browser. If you now open the browser you will see the below output