Exporting SQL table to CSV, column names not appearing

0
votes

So here is the code I currently have for exporting a specific table to .csv format. It works perfectly so far, as in it downloads the file and formats the data how I would like it. However it doesn't display the Column names

ex: It currently shows

1 Jones Matt

2 Smith John

3 Doe Jane

I would like it to show:

ID Last_Name First_Name

1 Jones Matt

2 Smith John

3 Doe Jane

<?php
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=applications.csv");
header("Pragma: no-cache");
header("Expires: 0");

ini_set('display_errors',1);
$private=1;
error_reporting(E_ALL ^ E_NOTICE);

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());

$query = "SELECT * FROM applications";
$select_c = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
{
    $result.="{$row['ID']},";
    $result.="{$row['LAST_NAME']},";
    $result.="{$row['FIRST_NAME']},";
    $result.="{$row['ORGANIZATION']},";
    $result.="{$row['TITLE']},";
    $result.="\n";

}
    echo $result;
?>
4
phphtmlmysqlexcel

4 Answers

2
votes

Here's a quick and dirty way of adding the column names:

<?php
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=applications.csv");
header("Pragma: no-cache");
header("Expires: 0");

ini_set('display_errors',1);
$private=1;
error_reporting(E_ALL ^ E_NOTICE);

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());

$query = "SELECT * FROM applications";
$select_c = mysql_query($query) or die(mysql_error());

// -------------------- add the line below -----------------------------------
$result="ID,LAST_NAME,FIRST_NAME,ORGANIZATION,TITLE\n";
// -------------------- add the line above -----------------------------------

while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
{
    $result.="{$row['ID']},";
    $result.="{$row['LAST_NAME']},";
    $result.="{$row['FIRST_NAME']},";
    $result.="{$row['ORGANIZATION']},";
    $result.="{$row['TITLE']},";
    $result.="\n";

}
    echo $result;
?>
0
votes

Just add header row before your while cycle:

echo "ID,Last_Name,First_Name,ORGANIZATION,TITLE\n";
0
votes

I assume you also want to query the column names.

With a SELECT statement you will never get the column names in the result.

In MySQL you can use

SHOW COLUMNS FROM applications FROM db;

to get the column names in a result.

http://dev.mysql.com/doc/refman/5.7/en/show-columns.html

0
votes

An alternative implementation of this that would allow you to use the fputcsv to guard against commas within your data .

<?php
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=applications.csv");
header("Pragma: no-cache");
header("Expires: 0");

ini_set('display_errors',1);
$private=1;
error_reporting(E_ALL ^ E_NOTICE);

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());

$query = "SELECT ID, LAST_NAME, FIRST_NAME, ORGANIZATION, TITLE FROM applications";
$select_c = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($select_c, MYSQL_ASSOC);
$fp = fopen("php://output", "w");
fputcsv($fp, array_keys($row));
do {
    fputcsv($fp, $rows);
} while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC));

?>