Write custom column names to query result file

Joe Gazda

I'm a relative newbie to PHP, but have been able to put together some PHP
code to generate a CSV/XLS file from a Query result. Now, I would like to
include custom column names instead of the MySQL column table names. I know
that there are codes to generate tabs and carriage returns, but can't find
anything about including "commas" in a string to output to the file to
separate the custom field names. I'd appreciate some help with a line of
code to insert those custom column names like:

First Name | Last Name | DOB | etc.

Thanks,

Joe G.

The generated file is set to the company name + date.xls and output the data
correctly. My PHP code is:

<?
@session_start();
// Remove spaces from Company Name
$trim_name = str_replace(" ", "", $co_name);
$file_name=$trim_name.date("mdY").".xls";
echo $file_name;
// Connect to Database
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$pass);
@mysql_select_db($database) or die( "Unable to select database");
// Find the Number of Fields
$select = "SELECT * FROM employee WHERE co_name='$co_name'";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
// Extract Database Fields
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
// Extract your Data
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
// Default Message
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
$fp = fopen($file_name,"a"); // $fp is now the file pointer to file
$file_name
if($fp){
$fp = fopen($file_name,"a");
fwrite($fp,$data); // Write information to the file
fclose($fp); // Close the file
echo "File saved successfully";
} else {
echo "Error saving file!";
echo "Use your Browser Back Button to Return";
}
?>

Jul 17 '05 #1

NC

Joe Gazda wrote:

I'm a relative newbie to PHP, but have been able to put together some PHP code to generate a CSV/XLS file from a Query result. Now, I would like to include custom column names instead of the MySQL column table names. I know that there are codes to generate tabs and carriage returns, but can't find anything about including "commas" in a string to output to the file to separate the custom field names.
Just replace "\t" with ",". That's it.
I'd appreciate some help with a line of code to insert those custom
column names like:

First Name | Last Name | DOB | etc.
OK, right now you have:
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}

Just replace it with

$header = "First Name,Last Name,DOB,etc.";

Cheers,
NC

Jul 17 '05 #2

Joe Gazda

Substituting the code yields the field name string in the first cell of MS
Excel.
I've just decided to have the client "import" the data fields into an
existing Excel Spreadsheet with the Custom Field Names already in place.
Thanks for you help, anyway.

"NC" <nc@iname.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Joe Gazda wrote:

I'm a relative newbie to PHP, but have been able to put together some

PHP

code to generate a CSV/XLS file from a Query result. Now, I would

like to

include custom column names instead of the MySQL column table names.

I know

that there are codes to generate tabs and carriage returns, but can't

find

anything about including "commas" in a string to output to the file

to

separate the custom field names.

Just replace "\t" with ",". That's it.

I'd appreciate some help with a line of code to insert those custom
column names like:

First Name | Last Name | DOB | etc.

OK, right now you have:

for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}

Just replace it with

$header = "First Name,Last Name,DOB,etc.";

Cheers,
NC

Jul 17 '05 #3