Page 1 of 1

producing a csv file with large text

Posted: Mon May 14, 2007 5:42 am
by hame22
Hi I have a discussion forum on my site and I am trying to add a facility to create a csv file of all the forums threads.

I am using the following code:

Code: Select all

function threads_csv()
{
	db_connect();
	
	$result = mysql_query("Select * from hrd order by thread_date DESC");
	
	while($row = mysql_fetch_array($result))
	{
		$thread_id		= $row['thread_id'];
		$parent_id		= $row['parent_id'];
		$order			= $row['thread_order'];
		$title			= $row['thread_title'];
		$body			= strip_tags($row['thread_body']);
		$body			= stripslashes($body);
		$user			= $row['thread_user'];
		$date			= $row['thread_date'];
	
		$data .= "$thread_id, $parent_id, $order, $title, $user, $date, $body\n";
	}
	
	$filename = "training_journal_threads.csv";
header ('Content-type: text/csv');
header ('Content-Disposition: attachment; filename='.$filename);
print "Thread ID, Parent Thread, Order, Title,User, Date, Body\n";
echo $data; 
exit();
}

the csv is created. But due to the amount of text in the body variable the body field is all over the csv file and messes up how the columsn are structured. I have stripped tags and slahes but this has not fixed the problem.

Is there a certain thing that needs to be done to fix this?

All suggestions welcome.

Thanks in advance

Posted: Mon May 14, 2007 7:06 am
by kaszu
browser bug, ignore this :)

Posted: Mon May 14, 2007 7:07 am
by Begby
This depends on your CSV structure. You are probably going to also need to remove all the commas from the body along with any line returns. As soon as your CSV parser hits a comma it thinks its a new column, and as soon as it hits an EOL it thinks its on a new line.

Another way to do it might be to escape all the double quotations in the body, then surround body with double quotes in the CSV output.

Posted: Mon May 14, 2007 7:09 am
by kaszu
Because "\n" new line character can be a part of the body (and other fields too) text is split into multiple lines.
In CSV each field which contains comma or newline symbol must be inside double quotes.
Also double quotes inside the text should be escaped too, I don't have time to check now.

Posted: Mon May 14, 2007 7:13 am
by kyberfabrikken
Why would you run stripslashes() on the data?
Try addslashes(), which will convert your linebreaks into \n

Posted: Mon May 14, 2007 8:41 am
by DrTom
You don't need to run stripslashes or striptags. Those won't break the formatting. You need to use addslashes as previously mentioned. You also need to escape all commas some kind of a way or use quotes around each field. Also the space after each comma is technically not part of file format and will be prepended to each string unless you specifically do something about it. Another is with large data, string concatenation will run out of memory quickly. It'll be both faster and memory nice to open the file, loop through data and write to file. Third check out http://us.php.net/manual/en/function.fputcsv.php. All of this has been done for you already.