Php Export Data

[yangkai9999]

Hello,I wrote a PHP script to export a CSV file from a MySQL table.the script worked. But it add one empty row for each record.How to earse the empty row?Thank you,the code is:<?php$host = 'localhost';$user = 'root';$pass = 'Openit4me';$db = 'mytest';$table = 'p1_select';$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;?>

[boen_robot]

I'm not completely familiar with the CSV format, but... in what cases you need a new line here:

$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";}

?If you remove that part, you should remove that new line, but if there are cases where there must be a new line after a record, you'll have to address them instead of blindly removing this.

[yangkai9999]

I'm not completely familiar with the CSV format, but... in what cases you need a new line here:?If you remove that part, you should remove that new line, but if there are cases where there must be a new line after a record, you'll have to address them instead of blindly removing this.

I try that. it could remove empty lines, but for each line will has an extrac comma at begining.

[jeffman]

This line adds a comma after every field: $csv_output .= $rowr[$j].", ";So it adds a comma after the LAST field. The comma creates the empty field.Since you also add a "\n" character after the loop, it would be most efficient to replace the last comma with the "\n". For this you can use substr_replace().(If anyone is wondering, you can use the same technique to finish off a JSON string built in the same manner. Different character, but same procedure.)

Edited December 2, 2009 by Deirdre's Dad

[yangkai9999]

This line adds a comma after every field: $csv_output .= $rowr[$j].", ";So it adds a comma after the LAST field. The comma creates the empty field.Since you also add a "\n" character after the loop, it would be most efficient to replace the last comma with the "\n". For this you can use substr_replace().(If anyone is wondering, you can use the same technique to finish off a JSON string built in the same manner. Different character, but same procedure.)

Would you please show me how to use the function. I'm a new one to use PHP.Thank you,

[jeffman]

Try this.

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

This says we will replace characters in $csv_output beginning 2 characters from the end (-2). I'm using the number 2 because you are adding a space after your comma. We want to remove both the comma and the space. We will replace those 2 characters with "\n".(Are you sure you need that space? A spreadsheet will understand csv without the extra space.)You'll need to use this technique for your first loop also, because it has the same problem.

[yangkai9999]

Try this.

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

This says we will replace characters in $csv_output beginning 2 characters from the end (-2). I'm using the number 2 because you are adding a space after your comma. We want to remove both the comma and the space. We will replace those 2 characters with "\n".(Are you sure you need that space? A spreadsheet will understand csv without the extra space.)You'll need to use this technique for your first loop also, because it has the same problem.

thank you, I'll try.