Export data into CSV file in PHP

You might have already used any package to download data in csv file. If you are using lots of packages in your project, it might  sometimes causes version issue. So, it is better to create some functionality without package.

In this article, we will go through export data into CSV file. If you want the data that we have used in example, you can always run the following query in your MySQL command.

CREATE TABLE `visitors` (
  `id` int NOT NULL,
  `browser` varchar(255) NOT NULL,
  `percentage` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `visitors` (`id`, `browser`, `percentage`) VALUES
(1, 'Chrome', '69.28'),
(2, 'Edge', '7.75'),
(3, 'Firefox', '7.48'),
(4, 'Internet Explorer', '5.21'),
(5, 'Safari', '3.73'),
(6, 'Opera', '1.12'),
(7, 'Others', '5.43');

ALTER TABLE `visitors`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `visitors`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

In the first step, we will connect PHP with database. So create file config.php and put the below code in it.

<?php

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "chart";

// create connection
$connection = new mysqli($servername, $username, $password, $dbname);

// check connection
if ($connection->connect_error) {
    die($connection->connect_error);
}

Now create a second file data.php which will get data from sql server. Create data.php file and insert below code in it.

<?php

include 'config.php';

$sql = "SELECT browser, percentage FROM visitors";

$result = $connection->query($sql);

if ($result->num_rows > 0) {

    $data[0][0] = 'Browser';
    $data[0][1] = 'Percentage';
    $x = 1;
    
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $data[$x][0] = $row["browser"];
        $data[$x][1] = (float)$row["percentage"];
        $x++;
    }
} else {
    die("No records");
}

Now the important part of article, is to create csv file and put data into it. Create index.php file and insert below code into it.

<?php

include 'data.php';

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="browser.csv"');

$fp = fopen('php://output', 'wb');

foreach ($data as $line) {
    fputcsv($fp, $line);
}

fclose($fp);

In the above code, we send header of csv file. Then open the csv file with fopen() command. The second parameter in fopen() function describes write(w) mode of binary(b) data into file. Then fputcsv() function put array data into csv file. In the end we close file fclose() function.

I hope you liked this article. Thank you for giving time in reading the article.

Author : Harsukh Makwana