CODEWITHSUNDEEP
phpmysqlcsvpdo
Jeremy Standridge
Jeremy Standridge

Reputation: 43

Outputting Column titles in CSV Export

I have this query that exports to a csv file. It works fine the only thing i can't figure out is i need to export the column titles as well, and have them display as Full Name, UserName, Flag and Reason. Below is the code and it exports all the rows fine but I'm not sure how to export the column titles above the respected rows.

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=blackflag_bidders.csv");
header("Pragma: no-cache");
header("Expires: 0");




//SQL Query for Data
$sql = "SELECT ui.first_name, ui.last_name, u.username,
    if(u.flag=1,'BLK', if(u.flag=2,'NAA','')) flag,
    if(u.flag!=0, IFNULL(ui.note,''),'') reason
FROM user u
LEFT JOIN user_info ui ON ui.user_id=u.id
WHERE u.flag!=0;";

//Prepare Query, Bind Parameters, Excute Query
$STH = $sam_db->prepare($sql);
$STH->execute();



//Export to .CSV
$fp = fopen('php://output', 'w');
//fputcsv($fp);
while ($row = $STH->fetch(PDO::FETCH_NUM)) fputcsv($fp,$row);
fclose($fp);

Upvotes: 3

Views: 1791

Answers (3)

Ashwani Goyal
Ashwani Goyal

Reputation: 616

You can get a column in CSV by simply displaying your results in Tabular form here in the page using <table> tag of HTML.

$result = "<table>";
    while ($row = $STH->fetch(PDO::FETCH_NUM)){
    $result .= "<tr><td>$row1</td><td>$row2</td><td>$row3</td></tr>";
}
$result .= "</table>";
fputcsv($fp, $result);

By $row1, $row2, I mean the values you get in your resultset

Upvotes: 0

Bob Aleena
Bob Aleena

Reputation: 460

The answer to this will depend upon whether you already know the column names or not. It seems like you do (e.g. you are already calling 'Select ui.firstname...')

If you do not, you can get the names by looking at this thread: What is the Select statement to return the column names in a table

Once you have the names, you simply need to create a single row with the names and add them to file by modifying your code as:

//Export to .CSV
$columnNamesRow = "FirstName, LastName, UserName";
$fp = fopen('php://output', 'w');
fputcsv($fp, $columnNamesRow);

//fputcsv($fp);
while ($row = $STH->fetch(PDO::FETCH_NUM)) fputcsv($fp,$row);
fclose($fp);

Upvotes: 0

Kevin
Kevin

Reputation: 41893

One way would be to fetch the first result by associative, those associative indices are columns anyway. Apply array_keys to get those, then first add the headers, then the first fetched row, then loop the rest.

// first set
$first_row = $STH->fetch(PDO::FETCH_ASSOC);
$headers = array_keys($first_row);
// $headers = array_map('ucfirst', $headers); // optional, capitalize first letter of headers
fputcsv($fp, $headers); // put the headers
fputcsv($fp, array_values($first_row)); // put the first row

while ($row = $STH->fetch(PDO::FETCH_NUM))  {
    fputcsv($fp,$row); // push the rest
}
fclose($fp);

Upvotes: 3

Related Questions