Export query result as CSV through PHP


Last Update : 2023-09-22 UTC 09:10:15 AM

Answers of > Export query result as CSV through PHP

Output each row of the data, format line as w3coded php result CSV, and write to file pointer.,Specify the w3coded php result header columns and put data into the CSV w3coded php result file.,Create a file pointer using fopen() w3coded php result function.,Retrieve data from the MySQL w3coded php result database.

To store the data, a table needs to be created in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The dbConfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

<?php // Database configuration $dbHost     = "localhost"; $dbUsername = "root"; $dbPassword = "root"; $dbName     = "codexworld";  // Create database connection $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);  // Check connection if ($db->connect_error) {     die("Connection failed: " . $db->connect_error); }

  • An EXPORT button is placed at the top of the data list.
  • By clicking the Export button, the data is exported from the database and allow to download on a local drive as a CSV file.

<!-- Export link -->
<div class="col-md-12 head">
    <div class="float-right">
        <a href="exportData.php" class="btn btn-success"><i class="dwn"></i> Export</a>

<!-- Data list table --> 
<table class="table table-striped table-bordered">
    <thead class="thead-dark">
   <?php     // Fetch records from database     $result = $db->query("SELECT * FROM members ORDER BY id ASC");     if($result->num_rows > 0){         while($row = $result->fetch_assoc()){     ?>
            <td><?php echo $row['id']; ?></td>
            <td><?php echo $row['first_name'].' '.$row['last_name']; ?></td>
            <td><?php echo $row['email']; ?></td>
            <td><?php echo $row['gender']; ?></td>
            <td><?php echo $row['country']; ?></td>
            <td><?php echo $row['created']; ?></td>
            <td><?php echo ($row['status'] == 1)?'Active':'Inactive'; ?></td>
    <?php } }else{ ?>
        <tr><td colspan="7">No member(s) found...</td></tr>
    <?php } ?>

For this example script, the Bootstrap library is used to style the HTML table and buttons. So, include the Bootstrap CSS library and custom stylesheet file (if any).

<!-- Bootstrap library -->
<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">

<!-- Stylesheet file -->
<link rel="stylesheet" href="assets/css/style.css">

  • Retrieve data from the MySQL database.
  • Create a file pointer using fopen() function.
  • Specify the header columns and put data into the CSV file.
  • Output each row of the data, format line as CSV, and write to file pointer.
  • Set Content-Type and Content-Disposition to force the browser to download the file rather than display it.

<?php  // Load the database configuration file include_once 'dbConfig.php';  // Fetch records from database $query = $db->query("SELECT * FROM members ORDER BY id ASC");  if($query->num_rows > 0){     $delimiter = ",";     $filename = "members-data_" . date('Y-m-d') . ".csv";          // Create a file pointer     $f = fopen('php://memory', 'w');          // Set column headers     $fields = array('ID', 'FIRST NAME', 'LAST NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS');     fputcsv($f, $fields, $delimiter);          // Output each row of the data, format line as csv and write to file pointer     while($row = $query->fetch_assoc()){         $status = ($row['status'] == 1)?'Active':'Inactive';         $lineData = array($row['id'], $row['first_name'], $row['last_name'], $row['email'], $row['gender'], $row['country'], $row['created'], $status);         fputcsv($f, $lineData, $delimiter);     }          // Move back to beginning of file     fseek($f, 0);          // Set headers to download file rather than displayed     header('Content-Type: text/csv');     header('Content-Disposition: attachment; filename="' . $filename . '";');          //output all remaining data on a file pointer     fpassthru($f); } exit;  ?>

Current topics : Export query result as CSV through PHP

Newly Added Questions

Similar Questions

Questions :

How To Group Array Key Value

Last Update : 2023-09-22 UTC 11:54:38 AM

Questions :

PhpStorm Warning For React Attributes In Jsx File With SCSS File

Last Update : 2023-09-22 UTC 11:54:22 AM

Questions :

Why Is The File Not Showing Up In Request.files And In Request.forms Instead?

Last Update : 2023-09-22 UTC 11:54:09 AM

Questions :

Proxying Assets From React App Directory In Slim Framework?

Last Update : 2023-09-22 UTC 11:54:01 AM

Questions :

Laravel 5.4 Can't Run “php Artisan Preset React” Comand

Last Update : 2023-09-22 UTC 11:53:48 AM

Questions :

How To Update Session Values Without Signing Out?

Last Update : 2023-09-22 UTC 11:53:42 AM

Questions :

Array Is Not Visible

Last Update : 2023-09-22 UTC 11:53:25 AM

Questions :

React Routing For Login Using Symfony

Last Update : 2023-09-22 UTC 11:53:07 AM

Questions :

Sanctum With React SPA Returning 419 Page Expired

Last Update : 2023-09-22 UTC 11:53:02 AM

Questions :

How Do I Import An Input String Into Another Page

Last Update : 2023-09-22 UTC 11:52:50 AM

© 2023 W3CODED - All Rights Reserved.