How to export MySQL Data to CSV file using PHP

In PHP  |  Last Updated on Fri, 01 Jul 2016

Our previous post was How to Import CSV File into MySQL Database using PHP and this post is about How to export MySQL Data to CSV file using PHP. This is easy but important tutorial. In many purpose your client need to download the data from MySQL database. But all the time they do not prefer web page for view. Instead of that they prefer CSV format, developers need to programming.

So using PHP we have done this programming and export MySQL data to CSV file.

Read More How to Import CSV File into MySQL Database using PHP

index.php

Lets say, this is home page of your project. You can see the table of your data. And also you can see the download link at the bottom.
 

<div style="width:300px; margin:0 auto; margin-top:200px;">

<table width="100%" border="0" cellspacing="0" cellpadding="5" style="text-align:center">
  <tr bgcolor="#000000" style="color:#FFFFFF;">
       <td>Name</td>
       <td>Phone</td>
       <td>Email</td>
  </tr>

  <?php
  $exp_data = mysql_query("select * from export_data");
  while($fet_exp_data = mysql_fetch_assoc($exp_data))
  {
  ?>

  <tr>
    <td><?php echo $fet_exp_data['name']; ?></td>
    <td><?php echo $fet_exp_data['phone']; ?></td>
    <td><?php echo $fet_exp_data['email']; ?></td>
  </tr>

  <?php } ?>

</table>

 <br  />
 <a href="dloadIn.php" style=" text-decoration:none;">
   <span style="background:#FF0066; color:#FFFFFF; padding:5px; cursor:pointer;">Download</span>
 </a>

</div>

dloadIn.php

Using this PHP page actually main CSV file is being generated. You can write your file name instead your_filename.csv inside header function. Inside this variable $outstr you can store your column title. And all the data of your MySQL table, store in an array variable. Which using join() function put the data into separated places using comma ( , )  inside CSV file.

The join() function returns a string from the elements of an array.
 

<?php

$table = 'export_data';
$outstr = NULL;

header("Content-Type: application/csv");
header("Content-Disposition: attachment;Filename=your_filename.csv");

$conn = mysql_connect("localhost", "root", "");
mysql_select_db("export",$conn);

// Query database to get column names  
$result = mysql_query("show columns from $table",$conn);
// Write column names
 
 $outstr = 'Name,Phone,Email'."\n";

  $exp_data = mysql_query("select * from export_data");
  while($fetexp_data = mysql_fetch_array($exp_data))
   {
     
       $arr = array($fetexp_data['name'],$fetexp_data['phone'],$fetexp_data['email']);
       $outstr.= join(',', $arr)."\n";
      
   }
    echo $outstr;
    mysql_close($conn);
 
?>

conn.php

Database connectivity.  
 

<?php 
    error_reporting(E_ALL && ~E_NOTICE);

    $conn = mysql_connect("localhost", "root", "");
    if(!$conn) die("Failed to connect to database!");
    $status = mysql_select_db('import', $conn);
    if(!$status) die("Failed to select database!");
?>