Need Table headings added to exported CSV file

Question

I have a script that takes a SQL Statement and puts the query result into a CSV file. Right now it only the rows of the table, I want it to Put the headings of the table at the top. How would I do that with this current script?

Script...

<?php

include 'connect.php'; 

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment;filename="export.csv"');
header('Cache-Control: max-age=0');

$fpcsv = fopen('php://output', "a+");

$sqlstatement = $_GET['sqlstatement'];
$exportcsv_q = mysql_query($sqlstatement);
if (@mysql_num_rows($exportcsv_q) > 0) {
    $campos = mysql_num_fields($exportcsv_q);
    while ($exportcsv_r = mysql_fetch_row($exportcsv_q)) {
         fputcsv($fpcsv, $exportcsv_r);
    }
}
exit;
?>

If anyone out there has a mysqli version of this I would love to be able to switch this over.

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

First, the answer to your question: output the headers before your while loop:

if (@mysql_num_rows($exportcsv_q) > 0) {
    // output headers here
    fwrite($fpcsv, "header1,header2,foo,bar,..."); // <-- like this
    // if you want, you could do it this way:
    // fputcsv($fpcsv, array("header1", "header2", ...));

    $campos = mysql_num_fields($exportcsv_q);
    while ($exportcsv_r = mysql_fetch_row($exportcsv_q)) {
         fputcsv($fpcsv, $exportcsv_r);
    }
}

If you want to have the headers even if there is no data, just move that line outside your if block, as well.

Second, yes, you should stop using mysql_*; the mysql_* functions are outdated, deprecated, and insecure. Use MySQLi or PDO instead. How to do that is too broad for a single question here. If you get stuck on some aspect of it, ask a new question about that.

Third, swallowing/suppressing errors with @ is considered bad practice and can lead to unexpected results. Use a proper try { ... } catch (...) {...} block instead so you can handle/log/report the error as needed.

edited May 23 '17 at 12:07

Community

1
1

answered Feb 26 '16 at 19:08

elixenide

44,308
16
74
100

With that i get an error saying `fputcsv() expects parameter 2 to be array`. – Robert Holden Feb 26 '16 at 19:19
Also is there a way to automatically populate the headers? – Robert Holden Feb 26 '16 at 19:20
@RobertHolden Oops. My example should have used `fwrite`, not `fputcsv`. I've edited the answer and provided sample code for both functions. – elixenide Feb 26 '16 at 19:21
@RobertHolden To get the column names and output them, you would need to build an array (or string) using [mysql_field_name()](http://php.net/manual/en/function.mysql-field-name.php), then output the names using `fputcsv` (or `fwrite` for a string). If you need help with that, you should ask a new question specifically about that. – elixenide Feb 26 '16 at 19:23
1

@RobertHolden In conjunction with Ed's answer about switching to the MySQLi API. You need to remember that `mysqli_query($con, $query)` along with other functions require that the database connection variable needs to be passed as the first parameter. All the syntax is listed in there to show you how to use them. Another function being `mysqli_real_escape_string($con, $var)`. Keep that in mind ;-) Sidenote: Not all functions need the db connection though. – Funk Forty Niner Feb 26 '16 at 19:37

Need Table headings added to exported CSV file

1 Answers1