Write MySQL query to CSV in php

Question

I'm trying to write the results of a MYSQL query to a CSV and having issues. The query works, and when I run in powershell it prints the perfect CSV data in powershell, but the file is nowhere to be found on my CPU.

How can I physically write to a CSV?

$result = $conn2->query(
                   "SELECT firstn
                , lastn
                , extension
                , Recieved
                , RecievedKnown
                , Outbound
                , outboundKnown
                , Missed
                , MissedKnown
                , CallingNumber
                , CalledNumber
                , starttime
                , endtime
                , duration
                , HOLDTIMESECS
                , TERMINATIONREASONCODE

            FROM (
                  SELECT u.firstn
                , u.lastn")
               //Long query, removing unnecessary code
             ); 




            if (!$result) die('Couldn\'t fetch records');
            $num_fields = mysql_num_fields($result);
            $headers = array();
            while ($fieldinfo = mysqli_fetch_field($result)) {
                $headers[] = $fieldinfo->name;
            }
            $fp = fopen('php://output', 'w');
            if ($fp && $result) {
                header('Content-Type: text/csv');
                header('Content-Disposition: attachment; filename="dailyReportTestPHP.csv"');
                header('Pragma: no-cache');
                header('Expires: 0');
                fputcsv($fp, $headers);
                while ($row = $result->fetch_array(MYSQLI_NUM)) {
                    fputcsv($fp, array_values($row));
                }
                die;
            }

You have to write the csv to a file, not$fp = fopen('php://output', 'w'); — Jay Blanchard
– Jay Blanchard, Commented Oct 3, 2017 at 20:32
you realize that your post contains a syntax error. Syntax highlighting is showing that. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 3, 2017 at 20:33
Sorry guys, issue with my comment in the query. Fixed it now — Geoff_S
– Geoff_S, Commented Oct 3, 2017 at 20:35
@tadman He's using mysqli, not the obsolete mysql extension, except he mistakenly used mysql_num_fields — Barmar
– Barmar, Commented Oct 3, 2017 at 20:45

soft87 · Accepted Answer · 2017-10-03 20:55:07Z

1

You need to delete headers and edit the line to add the path to the file to it:

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

For example:

$ fp = fopen ('c:\\folder\\resource.txt', 'w');

FOR HTTP:

if ($result) {
    header("Content-type: text/csv");
    header("Content-Disposition: attachment; filename=dailyReportTestPHP.csv");
    header("Pragma: no-cache");
    header("Expires: 0");

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

    fputcsv($fp, $headers);
    while ($row = $result->fetch_row()) {
        fputcsv($fp, $row);
    }
    fclose($fp);
    die;
}

edited Oct 3, 2017 at 20:55

answered Oct 3, 2017 at 20:34

soft87

5112 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cmorrissey · Accepted Answer · 2017-10-03 20:47:49Z

0

if you don't want to change your script, you need to pipe the output.

Since you are using powershell it would look like this

php myscript.php | Out-File c:\output.csv

if you are on linux or OS X it would look like this

php myscript.php > output.csv

answered Oct 3, 2017 at 20:47

cmorrissey

8,5912 gold badges26 silver badges28 bronze badges

Collectives™ on Stack Overflow

Write MySQL query to CSV in php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related