Jump to content

Php Export Data


yangkai9999
 Share

Recommended Posts

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;?>

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 by Deirdre's Dad
Link to comment
Share on other sites

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,
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...