Write data into CSV file using PHP

  •  
  •  
  •  
  •  
  •  
  •  

Sometimes we need to generate CSV file containing data from database table. So, in this tutorial, we are going to learn how to write data into CSV file using PHP. PHP has a default function fputcsv(), through which we can write data into CSV file. In this code, we will fetch data from MySQL table and generate a CSV file.

Write data into CSV file using PHP

Database – Write data into CSV file

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `mobile` bigint(10) NOT NULL,
  `country` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO `user_details` (`id`, `name`, `mobile`, `country`) VALUES
(1, 'Aritra Samanta', 9999999991, 'India'),
(2, 'Rina Saha', 9999999992, 'Australia'),
(3, 'Anil Das', 9999999993, 'London'),
(4, 'Akash Samanta', 9999999994, 'America'),
(5, 'Niharika Roy', 9999999995, 'Bulgeria');

Create the above table into the database and insert the example data into that table.

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','database_username');
define('DB_PASSWORD','database_password');
define('DB_NAME', 'database_name');

$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
//Check connection
if(mysqli_connect_errno($con))	echo "Failed to connect MySQL: " .mysqli_connect_error();
?>

Create a PHP file “db.php” in the root folder and write the above database connection code into it. Change the database credentials as yours.

HTML code – Write data into CSV file

<?php
include("db.php");
$sql = "select * from `user_details`";
$res = mysqli_query($con, $sql);
if(mysqli_num_rows($res) > 0) {
  ?>
  <form action="csv.php" method="post">
    <input type="submit" name="submitBtn" id="submitBtn" value="Generate CSV" />
    <table id="100%" cellpadding="5" cellspacing="1">
      <tbody>
        <tr>
          <th>SL.</th>
          <th>NAME</th>
          <th>MOBILE</th>
          <th>COUNTRY</th>
        </tr>
    <?php
    while($row = mysqli_fetch_array($res)) {
      ?>
        <tr>
          <td align="center"><?php echo $row['id']; ?>.</td>
          <td><?php echo $row['name']; ?></td>
          <td align="center"><?php echo $row['mobile']; ?></td>
          <td><?php echo $row['country']; ?></td>
        </tr>
      <?php
    }
    ?>
      </tbody>
    </table>
  </form>
  <?php
}
?>

Create a PHP file “index.php” in the root folder and write the above contents in it. This page simply displays the contents of the table in a tabular form and a submit button which generates a CSV file based on the table data.

Write data into CSV file using PHP

PHP code – Write data into CSV file

<?php
include("db.php");
if(isset($_POST['submitBtn'])) {

  $filename = date('d-m-Y_H-i-s').".csv";
  $file = fopen("php://output","w");
  header('Content-type: application/csv');
  header('Content-Disposition: attachment; filename='.$filename);
  
  //Here we fetch the column names of the table and write these into the CSV file
  $query = "select column_name from information_schema.columns where table_schema='database_name' and table_name='user_details'"
  $result = mysqli_query($con, $query);
  while ($row = mysqli_fetch_row($result)) {
    $column_header[] = $row[0];
  }
  fputcsv($file, $column_header);


  //Here we fetch the data from the table and write these data into the CSV file row wise.
  $sql = "select * from `user_details`";
  $res = mysqli_query($con, $sql);
  if(mysqli_num_rows($res) > 0) {		
    while($row = mysqli_fetch_assoc($res)) {
      fputcsv($file, $row);
    }
  }
}
?>

Create another PHP file “csv.php” in the root folder and write the above contents in it.

$filename = date('d-m-Y_H-i-s').".csv";
$file = fopen("php://output","w");
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

The filename is dynamic, a combination of date and time. The second, third and fourth line forced to download the file as a CSV file instead of open the page in the browser.

$query = "select column_name from information_schema.columns where table_schema='database_name' and table_name='user_details'";
$result = mysqli_query($con, $query);
while ($row = mysqli_fetch_row($result)) {
  $column_header[] = $row[0];
}
fputcsv($file, $column_header);

The above code performs a simple query and fetches the column names of the table and stores in an array variable “$column_header“. fputcsv($file, $column_header) writes the column names in the CSV file as the first row.

$sql = "select * from `user_details`";
$res = mysqli_query($con, $sql);
if(mysqli_num_rows($res) > 0) {
  while($row = mysqli_fetch_assoc($res)) {
    fputcsv($file, $row);		
  }
}

Fetch all data from the table and writes them one by one into the CSV file.

Download the full source code from the below download link and please like and share this tutorial link to others.

About Mitrajit

Leave a Reply

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

Prove you are a human *