MYSQL to excel....problem with text areas and paragraphs.

[davidcriniti]

Hi there,

 

I got some good advice from Harristweed the other day about how to export from Mysql to excel, so that each field appears in a different column in excel.

 

I expanded the code Harristweed gave me to cover all fields in my database, and all went well until the 2nd last field, when I hit a brick wall. Unlike previous fields, which were based on info inputted via radio buttons, dropdown lists, or text boxes, the field causing problems was based on data that came from a text area on the web form.

 

A few tests revealed that the problem was related to when a user pressed the "Enter" key while filling in that field, to begin a new paragraph. When this happens, it would go into the database fine. However, when I used the code to export it to excel, anything after the "Enter" key had been pressed would appear in a new cell on a new row, thereby putting the table out of alignment.

 

I could get around this by rewording the question into a few questions which each use a text box rather than a text area box.

 

However, if there is a way to get around this by tweaking the php code, I'd love to know. The field with which I'm having the problem is the one called "experience".

 

Thanks for your time,

 

Dave

 

 

<?PHP


$db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD");

mysql_select_db("MYDATABASE",$db);

$query="SELECT * FROM applications2010";
$result=mysql_query($query);

$header=" date\t firstname\t lastname\t sex\t dobday\t dobmonth\t dobyear\t streetaddress\t suburb\t state\t postcode\t country\t preferredphone\t secondphone\t thirdphone\t emailaddress\t emconname\t emconphone\t experience\t ";

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

if(empty($row[date]))$row[date]=" ";   
if(empty($row[firstname]))$row[firstname]=" ";   
if(empty($row[lastname]))$row[lastname]=" ";   
if(empty($row[sex]))$row[sex]=" ";   
if(empty($row[dobday]))$row[dobday]=" ";   
if(empty($row[dobmonth]))$row[dobmonth]=" ";   
if(empty($row[dobyear]))$row[dobyear]=" ";   

if(empty($row[streetaddress]))$row[streetaddress]=" ";   
if(empty($row[suburb]))$row[suburb]=" ";   
if(empty($row[state]))$row[state]=" ";   
if(empty($row[postcode]))$row[postcode]=" ";   
if(empty($row[country]))$row[country]=" ";   
if(empty($row[preferredphone]))$row[preferredphone]=" ";   
if(empty($row[secondphone]))$row[secondphone]=" ";   
if(empty($row[thirdphone]))$row[thirdphone]=" ";   

if(empty($row[emailaddress]))$row[emailaddress]=" ";   

if(empty($row[emconname]))$row[emconname]=" ";   
if(empty($row[emconphone]))$row[emconphone]=" ";   
if(empty($row[experience]))$row[experience]=" ";   




$line = '';   
$line .= "$row[date]\t $row[firstname]\t $row[lastname]\t $row[sex]\t $row[dobday]\t $row[dobmonth]\t $row[dobyear]\t $row[streetaddress]\t  $row[suburb]\t  $row[state]\t  $row[postcode]\t $row[country]\t $row[preferredphone]\t $row[secondphone]\t $row[thirdphone]\t $row[emailaddress]\t $row[emconname]\t $row[emconphone]\t $row[experience]\t ";   

$data .= trim($line)."\n"; }
$data = str_replace("\r","",$data); 
if ($data == "") 
{    
$data = "\n(0) Records Found!\n";                    
}

header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=filename=".date("d-m-Y")."-export.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data";exit;



?>

[harristweed]

not certain but try:

if(empty($row[experience])){
$row[experience]=" "; 
}else{
$row[experience]=str_replace("\r","",$row[experience);  
}