PHP code to convert a MySQL query to CSV

What is the most efficient way to convert a MySQL query to CSV in PHP please?

It would be best to avoid temp files as this reduces portability (dir paths and setting file-system permissions required).

The CSV should also include one top line of field names.

 Answers

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

(the documentation for this is here: http://dev.mysql.com/doc/refman/5.0/en/select.html)

or:

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

---

Look at the documentation regarding the SELECT ... INTO OUTFILE syntax.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

---

If you'd like the download to be offered as a download that can be opened directly in Excel, this may work for you: (copied from an old unreleased project of mine)

These functions setup the headers:

function setExcelContentType() {
    if(headers_sent())
        return false;

    header('Content-type: application/vnd.ms-excel');
    return true;
}

function setDownloadAsHeader($filename) {
    if(headers_sent())
        return false;

    header('Content-disposition: attachment; filename=' . $filename);
    return true;
}

This one sends a CSV to a stream using a mysql result

function csvFromResult($stream, $result, $showColumnHeaders = true) {
    if($showColumnHeaders) {
        $columnHeaders = array();
        $nfields = mysql_num_fields($result);
        for($i = 0; $i < $nfields; $i++) {
            $field = mysql_fetch_field($result, $i);
            $columnHeaders[] = $field->name;
        }
        fputcsv($stream, $columnHeaders);
    }

    $nrows = 0;
    while($row = mysql_fetch_row($result)) {
        fputcsv($stream, $row);
        $nrows++;
    }

    return $nrows;
}

This one uses the above function to write a CSV to a file, given by $filename

function csvFileFromResult($filename, $result, $showColumnHeaders = true) {
    $fp = fopen($filename, 'w');
    $rc = csvFromResult($fp, $result, $showColumnHeaders);
    fclose($fp);
    return $rc;
}

And this is where the magic happens ;)

function csvToExcelDownloadFromResult($result, $showColumnHeaders = true, $asFilename = 'data.csv') {
    setExcelContentType();
    setDownloadAsHeader($asFilename);
    return csvFileFromResult('php://output', $result, $showColumnHeaders);
}

For example:

$result = mysql_query("SELECT foo, bar, shazbot FROM baz WHERE boo = 'foo'");
csvToExcelDownloadFromResult($result);

Write data into CSV file using PHP

Sometimes we need to generate CSV file containing data from database table. So, in this tutorial, we are going to learn how to write data into CSV file using PHP. PHP has a default function fputcsv(), through which we can write data into CSV file. In this code, we will fetch data from MySQL table and generate a CSV file.

Database – Write data into CSV file

1. CREATE TABLE IF NOT EXISTS `user_details` (
2. `id` int(11) NOT NULL AUTO_INCREMENT,
3. `name` varchar(50) NOT NULL,
4. `mobile` bigint(10) NOT NULL,
5. `country` varchar(50) NOT NULL,
6. PRIMARY KEY (`id`)
7. );
10. INSERT INTO `user_details` (`id`, `name`, `mobile`, `country`) VALUES
11. (1, 'Aritra Samanta', 9999999991, 'India'),
12. (2, 'Rina Saha', 9999999992, 'Australia'),
13. (3, 'Anil Das', 9999999993, 'London'),
14. (4, 'Akash Samanta', 9999999994, 'America'),
15. (5, 'Niharika Roy', 9999999995, 'Bulgeria');

Create the above table into the database and insert the example data into that table.

1. <?php
2. define('HOSTNAME','localhost');
3. define('DB_USERNAME','database_username');
4. define('DB_PASSWORD','database_password');
5. define('DB_NAME', 'database_name');
7. $con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
8. //Check connection
9. if(mysqli_connect_errno($con)) echo "Failed to connect MySQL: " .mysqli_connect_error();
10. ?>

Create a PHP file “db.php” in the root folder and write the above database connection code into it. Change the database credentials as yours.

HTML code – Write data into CSV file

1. <?php
2. include("db.php");
3. $sql = "select * from `user_details`";
4. $res = mysqli_query($con, $sql);
5. if(mysqli_num_rows($res) > 0) {
6. ?>
7. <form action="csv.php" method="post">
8. <input type="submit" name="submitBtn" id="submitBtn" value="Generate CSV" />
9. <table id="100%" cellpadding="5" cellspacing="1">
10. <tbody>
11. <tr>
12. <th>SL.</th>
13. <th>NAME</th>
14. <th>MOBILE</th>
15. <th>COUNTRY</th>
16. </tr>
17. <?php
18. while($row = mysqli_fetch_array($res)) {
19. ?>
20. <tr>
21. <td align="center"><?php echo $row['id']; ?>.</td>
22. <td><?php echo $row['name']; ?></td>
23. <td align="center"><?php echo $row['mobile']; ?></td>
24. <td><?php echo $row['country']; ?></td>
25. </tr>
26. <?php
27. }
28. ?>
29. </tbody>
30. </table>
31. </form>
32. <?php
33. }
34. ?>

Create a PHP file “index.php” in the root folder and write the above contents in it. This page simply displays the contents of the table in a tabular form and a submit button which generates a CSV file based on the table data.

PHP code – Write data into CSV file

1. <?php
2. include("db.php");
3. if(isset($_POST['submitBtn'])) {
5. $filename = date('d-m-Y_H-i-s').".csv";
6. $file = fopen("php://output","w");
7. header('Content-type: application/csv');
8. header('Content-Disposition: attachment; filename='.$filename);
10. //Here we fetch the column names of the table and write these into the CSV file
11. $query = "select column_name from information_schema.columns where table_schema='database_name' and table_name='user_details'"
12. $result = mysqli_query($con, $query);
13. while ($row = mysqli_fetch_row($result)) {
14. $column_header[] = $row[0];
15. }
16. fputcsv($file, $column_header);
19. //Here we fetch the data from the table and write these data into the CSV file row wise.
20. $sql = "select * from `user_details`";
21. $res = mysqli_query($con, $sql);
22. if(mysqli_num_rows($res) > 0) {
23. while($row = mysqli_fetch_assoc($res)) {
24. fputcsv($file, $row);
25. }
26. }
27. }
28. ?>

Create another PHP file “csv.php” in the root folder and write the above contents in it.

1. $filename = date('d-m-Y_H-i-s').".csv";
2. $file = fopen("php://output","w");
3. header('Content-type: application/csv');
4. header('Content-Disposition: attachment; filename='.$filename);

The filename is dynamic, a combination of date and time. The second, third and fourth line forced to download the file as a CSV file instead of open the page in the browser.

1. $query = "select column_name from information_schema.columns where table_schema='database_name' and table_name='user_details'";
2. $result = mysqli_query($con, $query);
3. while ($row = mysqli_fetch_row($result)) {
4. $column_header[] = $row[0];
5. }
6. fputcsv($file, $column_header);

The above code performs a simple query and fetches the column names of the table and stores in an array variable “$column_header“. fputcsv($file, $column_header) writes the column names in the CSV file as the first row.

1. $sql = "select * from `user_details`";
2. $res = mysqli_query($con, $sql);
3. if(mysqli_num_rows($res) > 0) {
4. while($row = mysqli_fetch_assoc($res)) {
5. fputcsv($file, $row);
6. }
7. }

Fetch all data from the table and writes them one by one into the CSV file.

Download the full source code from the below download link and please like and share this tutorial link to others.

Download Source Code