Export query result as CSV through PHP

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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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>
    </div>
</div>

<!-- Data list table --> 
<table class="table table-striped table-bordered">
    <thead class="thead-dark">
        <tr>
            <th>#ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>Country</th>
            <th>Created</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody>
   <?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()){     ?>
        <tr>
            <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>
        </tr>
    <?php } }else{ ?>
        <tr><td colspan="7">No member(s) found...</td></tr>
    <?php } ?>
    </tbody>
</table>

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