To rename some fields in results

philweb

The script below will access the database table and download the results of that table as a csv file to your computer, so would it be easier to adjust the script to change the column titles of the table in the results or would it be best to adjust the column names of the table? The latter would be extremely hard to do (but not impossible) as the tables form part of the Zen-Cart programme.

Any Ideas please?

Thanks,

Phil

$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$table = 'db_table';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not 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("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

NogDog

Another option would be to do it in the SQL:

SELECT col_1 AS new_name_1, col_2 AS new_name_2 FROM . . .

philweb

Hi NogDog

Thanks for the reply, what I am trying to do is

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

change the name of the columns that are in the database to what I want them to be in the csv file. Forgive me if that is what you just told me but this is quite complicated, for me anyway!!

Can you re-explain please,

Thanks

Phil

NogDog

Sorry, I just saw that you were doing a "SELECT * . . ." and figured that's where you wanted to rename some column names.

I guess the question is, what do you want to change the names to, and perhaps also, why?

philweb

I am using Zen-Cart as an online shop, I also use QuickBooks which is an accounting software.

So, the plan is to retrieve the customers names and addresses from the database and save the file as a csv, I can then import the customers data as a file into QuickBooks instead of individually putting them in. I could change the column titles myself, but I thought I'd ask an expert in PHP if it is possible to adapt the script.

What do you think?

Thanks,

Phil

PS I want to change the names of the columns from entry_firstname to First Name and entry_surname to Last Name etc

NogDog

You could just make a search/replace array and use that with str_replace():

$replace = array(
   'entry_firstname' => 'First Name',
   'entry_surname' => 'Last Name'
);
while ($row = mysql_fetch_assoc($result)) {
   $csv_output .= str_replace(array_keys($replace), $replace, $row['Field'])."; ";
}

philweb

Thanks very much for that, this should help me a lot when I get it all changed. Could you tell me exactly where I should put this piece of code please?

Thanks,

Phil

NogDog

The "while(...)" line is the same as that already in your code sample. All you need to do is define the replacement array before that, and then add the str_replace() call to the first line within that while loop.

philweb

That doesn't make much sense to me, would you mind showing me your answer?

Thanks,

Phil

PS Forgot to say, I'm still on version 4.4.8 PHP

NogDog

Search your code for this line:

while ($row = mysql_fetch_assoc($result)) {

Right before it, add the array definition for the $replace array.

Right after it, replace this line:

$csv_output .= $row['Field']."; ";

...with the line from my code that starts the same but has the str_replace() in it.

(I'm trying not to just feed you the completed code, but hopefully get you to sort of understand how it all fits it -- a "give a fish/teach how to fish" sort of thing. 😉 )

philweb

Ah yes I see, very good. I can now go on and edit the other columns now. Just one last thing, the columns are separated with a ; is there a way of removing those please?

Thanks,

Phil

PS Thanks for the other post re Parse error, that worked fine, I will mark it as resolved. Thanks

philweb

I sorted the ; problem but have just realised that the column titles are all in one cell and not in individual cells like the database results are. Could I ask you one last time please?

Thanks,

Phil