Changing field name when MySQL information is dumped into a CSV

[skateme]

Hi,

 

I have a script that dumps the information from one of my MySQL tables into a CSV. The script works great, but in the result, the names of the MySQL columns are shown as-is. In other words, I have a column named "field4" that shows up as "field4" in the CSV. How can I modify the columns to more descriptive names without modifying the database itself?

 

Here's my code:

<?php
$host = "*********";
$user = "*********";
$pass = "*********";
$db = "database_name";
$table = "table";
$file = "member_information";

$link = mysql_connect($host, $user, $pass) or die("Cannot connect." . mysql_error());
mysql_select_db($db) or die("Cannot connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].",";
$i++;}
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}

$filename = $file."_".date("m.d.y");

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("m.d.y") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

print $csv_output;

exit;
?>

 

The CSV it spits out looks like this:

field1    field2     field3    field 4
info      info        info       info

 

I want it to look like this:

Name      Email      Location      Age
info         info          info             info

 

Thanks in advance!

[PeoMachine]

Why not modify the database table? ...

[skateme]

The table columns are called by a forum that will be too much of a hassle to update.

[PFMaBiSmAd]

A) Your database columns should already be descriptive,

 

B) You can use alias names in the query (i.e. SELECT field1 as Name, ...),

 

C) You can use a lookup array in your php code to map the column names to your display name.

[skateme]

Hi thanks for the reply. I think option B is best, however the output still appears the same. My updated code:

 

<?php
$host = "*********";
$user = "*********";
$pass = "*********";
$db = "database_name";
$table = "table";
$file = "member_information";

$link = mysql_connect($host, $user, $pass) or die("Cannot connect." . mysql_error());
mysql_select_db($db) or die("Cannot connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].",";
$i++;}
}
$csv_output .= "\n";
$values = mysql_query("SELECT userid AS \"UserID\",temp as \"\",field1 AS \"Name\",field2 AS \"HighSchool\",field3 AS \"HSGraduation\",field4 AS \"College\",field5 AS \"CollegeGraduation\",field6 AS \"Major\", field7 AS \"InternshipSite\",field8 AS \"Email\" FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}

$filename = $file."_".date("m.d.y");

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("m.d.y") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

print $csv_output;

exit;
?>

 

Any advice? Thanks!

[PFMaBiSmAd]

The alias names only exist in the result for the query that contains the alias names. You could use mysql_field_name to get those names after you execute that query (there's no need for a separate SHOW COLUMNS ... query) -

 

<?php

$values = mysql_query( ... ... );

$i = 0;
while ($i < mysql_num_fields($values)) {
$names[] = mysql_field_name($values,$i);
$i++;
}
$csv_output = implode(',',$names) . "\n"; // implode the names

// your while(){} loop to retrieve the data from the query would go here...
while( ... ... ){

}