Friday 7 October 2016

Export Mysql Table Data to CSV file in PHP



Hello friends in this post we are going to discuss how can we create csv file from mysql table data by using php script. Now a days comma separated values file data is most commonly supported file format for export tabular records between web applications. Exporting of Mysql table data to csv format file is useful feature in your web application and it will become increasingly common in all type of web based application. This is because this type of file consume more data and gain less space. We can store more data in csv file format and it consume less web space as compare to excel file format. So, in this video we are going to learn how can we export mysql table data to csv file. So here we have write php script that make csv file from mysql table data. In php script first we have set the http header for define content type for csv file and we have also set header for download file as attachment and we have also define the name of file, then after we have open file from php output stream and then after we have fetch data from mysql table and by using file put csv function we have write mysql table data into csv file line by line. This way we can export mysql table data to csv file by using php script.




Source Code


index.php



 <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $query ="SELECT * FROM tbl_employee ORDER BY id desc";  
 $result = mysqli_query($connect, $query);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Export Mysql Table Data to CSV file in PHP</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container" style="width:900px;">  
                <h2 align="center">Export Mysql Table Data to CSV file in PHP</h2>  
                <h3 align="center">Employee Data</h3>                 
                <br />  
                <form method="post" action="export.php" align="center">  
                     <input type="submit" name="export" value="CSV Export" class="btn btn-success" />  
                </form>  
                <br />  
                <div class="table-responsive" id="employee_table">  
                     <table class="table table-bordered">  
                          <tr>  
                               <th width="5%">ID</th>  
                               <th width="25%">Name</th>  
                               <th width="35%">Address</th>  
                               <th width="10%">Gender</th>  
                               <th width="20%">Designation</th>  
                               <th width="5%">Age</th>  
                          </tr>  
                     <?php  
                     while($row = mysqli_fetch_array($result))  
                     {  
                     ?>  
                          <tr>  
                               <td><?php echo $row["id"]; ?></td>  
                               <td><?php echo $row["name"]; ?></td>  
                               <td><?php echo $row["address"]; ?></td>  
                               <td><?php echo $row["gender"]; ?></td>  
                               <td><?php echo $row["designation"]; ?></td>  
                               <td><?php echo $row["age"]; ?></td>  
                          </tr>  
                     <?php       
                     }  
                     ?>  
                     </table>  
                </div>  
           </div>  
      </body>  
 </html>  


export.php



 <?php  
      //export.php  
 if(isset($_POST["export"]))  
 {  
      $connect = mysqli_connect("localhost", "root", "", "testing");  
      header('Content-Type: text/csv; charset=utf-8');  
      header('Content-Disposition: attachment; filename=data.csv');  
      $output = fopen("php://output", "w");  
      fputcsv($output, array('ID', 'Name', 'Address', 'Gender', 'Designation', 'Age'));  
      $query = "SELECT * from tbl_employee ORDER BY id DESC";  
      $result = mysqli_query($connect, $query);  
      while($row = mysqli_fetch_assoc($result))  
      {  
           fputcsv($output, $row);  
      }  
      fclose($output);  
 }  
 ?>  

21 comments:

  1. Its really very helpful. Thanks u guy.

    ReplyDelete
  2. well broken link but thanks lol

    ReplyDelete
  3. in my case only csv file download but htere is no data what i do

    ReplyDelete
  4. hello dear webslesson..
    the file is not available anymore in zippyshare...
    pls upload on your google drive & share...
    thats which simple & elegant....
    thanks for all your efforts...

    ReplyDelete
  5. Great tutorial even though i couldn't download source code...

    ReplyDelete
  6. Hi,
    Thank you for your clean and clear code. But I have one problem, When I hit the export button It printed the output to the screen and didn't download any csv file!
    What do you think my problem is?

    Thank you much in advance :)

    ReplyDelete
  7. Hii How can i get the date in csv file it's giving me ####

    ReplyDelete
    Replies
    1. hey. The ### means your field is to small too show data. That was my problem.

      Delete
  8. Hello is there any solution with date
    how can i export mysql table date into CSV file

    ReplyDelete
  9. I liked it. Have put it into practice. Do you have a tutorial to do the opposite? In other words, if I have a CSV file and want to export it with PHP to my table.

    ReplyDelete
  10. Amazing! Awesome and all other good stuff there is ... very helpful! Thankyou!!!

    ReplyDelete
  11. it says cannot modify headers

    ReplyDelete
  12. Very Helpful... Thanks a lot...

    ReplyDelete
  13. if i want print data from 1 tables so can print data

    ReplyDelete
  14. This helped alot.

    THANK YOU SO SO MUCH. Keep up the good work. You are simply awesome.

    ReplyDelete
  15. the exported file showed a blank line at the top before the headers.. why is that?

    ReplyDelete
  16. Hello, Your code works perfectly! thanks for that.. But I'm facing one issue when I;m opening the exported CSV file the prompt window comes up and it says its a SYLK file. Can you help out in that?

    ReplyDelete
  17. Thank you sir for the explain this and sharing code

    ReplyDelete