How to Export MySQL Database into CSV File using PHP/MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 301 times.

Getting Started

I've used bootstrap to improve the design of presentation of this tutorial. This bootstrap is included in the downloadable of this tutorial but, if you want, you may download bootstrap using this link.

Creating our Database

Next, we create our MySQL database that we are going to export into CSV file.

I've included a .sql file in the downloadable of this tutorial which is a mysql database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.

You should be able to create a database with tables named mydatabase.

Creating our Link to Export

Next, we are going to create a link that directs to our export code. We can do this by creating our index file named index.php. Also, I've included the table that shows the data that we are going to export.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>Export MySQL Database into CSV File using PHP/MySQLi</title>
  6. <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
  7. </head>
  8. <body>
  9. <div class="container">
  10. <h1 class="page-header text-center">Export MySQL Database into CSV File</h1>
  11. <div class="row">
  12. <div class="col-sm-8 col-sm-offset-2">
  13. <div class="row">
  14. <div class="col-sm-1">
  15. <a href="export.php" class="btn btn-primary btn-sm">Export</a>
  16. </div>
  17. <div class="col-sm-11">
  18. <?php
  19.  
  20. if(isset($_SESSION['message'])){
  21. echo $_SESSION['message'];
  22.  
  23. unset($_SESSION['message']);
  24. }
  25. ?>
  26. </div>
  27. </div>
  28. <table class="table table-bordered table-striped" style="margin-top:20px;">
  29. <thead>
  30. <th>UserID</th>
  31. <th>Firstname</th>
  32. <th>Lastname</th>
  33. <th>Address</th>
  34. </thead>
  35. <tbody>
  36. <?php
  37. //connection
  38. $conn = new mysqli('localhost', 'root', '', 'mydatabase');
  39.  
  40. $sql = "SELECT * FROM members";
  41. $query = $conn->query($sql);
  42.  
  43. while($row = $query->fetch_array()){
  44. ?>
  45. <tr>
  46. <td><?php echo $row['id']; ?></td>
  47. <td><?php echo $row['firstname']; ?></td>
  48. <td><?php echo $row['lastname']; ?></td>
  49. <td><?php echo $row['address']; ?></td>
  50. </tr>
  51. <?php
  52. }
  53.  
  54. ?>
  55. </tbody>
  56. </table>
  57. </div>
  58. </div>
  59. </div>
  60. </body>
  61. </html>

Creating our Export Script

Lastly, we create our export script by creating a new file, name it as export.php and paste the below code.

  1. <?php
  2. //connection
  3. $conn = new mysqli('localhost', 'root', '', 'mydatabase');
  4.  
  5. $sql = "SELECT * FROM members";
  6. $query = $conn->query($sql);
  7.  
  8. if($query->num_rows > 0){
  9. $delimiter = ',';
  10. //create a download filename
  11. $filename = 'members.csv';
  12.  
  13. $f = fopen('php://memory', 'w');
  14.  
  15. $headers = array('Id', 'Firstname', 'Lastname', 'Address');
  16. fputcsv($f, $headers, $delimiter);
  17.  
  18. while($row = $query->fetch_array()){
  19. $lines = array($row['id'], $row['firstname'], $row['lastname'], $row['address']);
  20. fputcsv($f, $lines, $delimiter);
  21. }
  22.  
  23. fseek($f, 0);
  24. header('Content-Type: text/csv');
  25. header('Content-Disposition: attachment; filename="' . $filename . '";');
  26. fpassthru($f);
  27. }
  28. else{
  29. $_SESSION['message'] = 'Cannot export. Data empty';
  30. header('location:index.php');
  31. }
  32. ?>

That ends this tutorial. Happy Cpding :)


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.