producing a csv file with large text

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

producing a csv file with large text

Post 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
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post by kaszu »

browser bug, ignore this :)
Last edited by kaszu on Mon May 14, 2007 7:11 am, edited 1 time in total.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post 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.
User avatar
kyberfabrikken
Forum Commoner
Posts: 84
Joined: Tue Jul 20, 2004 10:27 am

Post by kyberfabrikken »

Why would you run stripslashes() on the data?
Try addslashes(), which will convert your linebreaks into \n
DrTom
Forum Commoner
Posts: 60
Joined: Wed Aug 02, 2006 8:40 am
Location: Las Vegas

Post 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.
Post Reply