CODEWITHSUNDEEP
phppdo
odd
odd

Reputation: 449

PHP PDO stmt to CSV download - row headings not working

Hi wonder if someone is able to help me, i found the below code and modified it to suit my needs, i am trying to download all the data from the SELECT statement into a csv file with column headings as per the column name, but its throwing an error in the export (the csv file) for the column headings and i am not sure why.

i am getting the correct data but its not bringing back the column names.

this is the error:

Notice: Undefined index: Field in....

here is the code modified without the correct SELECTS:

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    $stmt = $dbh->prepare("SELECT * FROM users");
    $stmt->execute();
    $fields = array();
    $csv = array();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        array_push($fields, $row['Field']);
    }

    array_push($csv, $fields);
    $stmt = $dbh->prepare("SELECT * FROM users");
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        array_push($csv, $row);
    }

    $fp = fopen('file.csv', 'w');
    foreach($csv as $row) {
        fputcsv($fp, $row);
    }

    fclose($fp);
    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=export.csv");
    header("Pragma: no-cache");
    header("Expires: 0");
    readfile('file.csv');
    $dbh = null;
} catch(PDOException $e) {
    echo $e->getMessage();
}

Upvotes: 0

Views: 182

Answers (1)

Plamen Nikolov
Plamen Nikolov

Reputation: 2733

You can get the column name from the array key of the $row when fetching with PDO::FETCH_ASSOC

I've modified your script to demonstrate. Also you do not need two SQL queries to achieve the desired result:

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    $stmt = $dbh->prepare("SELECT * FROM cities");
    $stmt->execute();
    $fields = array();
    $csv = array();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        if (empty($fields))
        {
            $fields = array_keys($row);
            array_push($csv, $fields);
        }

        array_push($csv, $row);
    }

    $fp = fopen('file.csv', 'w');

    foreach ($csv as $row)
    {
        fputcsv($fp, $row);
    }

    fclose($fp);
    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=export.csv");
    header("Pragma: no-cache");
    header("Expires: 0");
    readfile('file.csv');
    $dbh = null;
}
catch (PDOException $e)
{
    echo $e->getMessage();
}

Upvotes: 3

Related Questions