[SOLVED] New line dramas

[Milani]

For the benefit of others my original question was terrible because I was trying to describe my problem without using code tags. Thus the first 2 responses are to this question before it was updated with code tags. This was a pretty poor attempt at describing the problem anyway - so save time and scroll down to my next reply. Ta

 

Hi. I am trying to adapt a php script that imports a csv file into a mysql database.

 

To keep things simple I'll leave out fair bit of background. The program that created the csv inputs

<br>

into textarea fields for formatting reasons. When I import the records, my PHP interface to mysql just displays

<br>

literally instead of converting to new lines. I've worked out that if I can replace

"<br>" with "\r\n"

I'm in business.

 

Problem is that I can't work out how to do it without splitting my records, seeing as \n defines the end of a record.

 

Basically the script does this (lamans code - not the real thing), creates a line and appends "\n" at the end...

 

$queries .= $query . "\n";

 

I want to do something like...

 

$query = str_replace("<br>","\r\n",$query);

 

That doesn't work obviously, but I hope you get the idea. How can I literally write "\r\n" into the string without it having a code effect?? Please help - I'm stumped. Ta

[mike1313]

$query = str_replace("\n"," ","$query"); 
$query = str_replace("\r"," ","$query");

[kenrbnsn]

If I understand you correctly, you want to use the trim() function:

<?php
$query = trim($query);
?>

 

Ken

[Milani]

Here is a better description of my problem...

 

I need to use this script to import a csv file into a mySql database. 3 of my csv fields have the html new line tag br, in them for formatting reasons. When the database comes across to mySql I don't have new lines in my fields when they are shown as textareas. mySql wants to see the string with /r/n, rather than br to show a new line.

 

Here's an example of the csv format

1stField|I want a<br>new line|next<br>please
1stField|Look<br>down there|press<br>return

 

Left untouched, the script routine that I'll quote later will insert those lines into a format ready for mysql...

insert into table1 values('1stField','I want a<br>new line','next<br>please');
insert into table1 values('1stField','Look<br>down there','press<br>return');

 

I'm trying to get my script to output "/r/n" wherever it finds br tag. If I add something basic like:

$line = str_replace("<br>","\r\n",$line);

I get a mess in my database output because \n defines the end of a record line...

insert into table1 values('1stField','I want a
new line','next
please');
insert into table1 values('1stField','Look
down there','press
return');

 

How can I use str_replace or otherwise to produce...

insert into table1 values('1stField','I want a\r\nnew line','next\r\nplease');
insert into table1 values('1stField','Look\r\ndown there','press\r\nreturn');

 

Here is the routine...

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line," \t");

$line = str_replace("\r","",$line);

/*$line = str_replace("<br>","\r\n",$line); NOT WORKING*/


/************************************************************************************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************************************************************************************/
$line = str_replace("'","\'",$line);
/***********************************************************************************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode("','",$linearray);

if($addauto)
	$query = "insert into $databasetable values('','$linemysql');";
else
	$query = "insert into $databasetable values('$linemysql');";

$queries .= $query . "\n";

@mysql_query($query);

 

So how do I un-escape an escape and return \r\n literally? Please help I'm still stuck. Thanks

 

 

[Milani]

Figured it out... This works:

 

*$line = str_replace("<br>","\\r\\n",$line); 

 

My thanks to kenrbnsn and mike1313 who were so quick to post help. Milani

[kenrbnsn]

A better way would be to use the mysql_real_escape_string() when you insert the data into the database. Keep the original str_replace().

 

<?php
$line = str_replace("<br>","\r\n",$line);
$q = "insert into table1 values ('some field','" . mysql_real_escape_string($line) . "'";
$rs = mysql_query($q);
?>

 

Ken

[Milani]

Hi Ken. Because the "insert into table1..." line is preceeded by $linemysql = implode("','",$linearray); if I use mysql_real_escape_string() where you suggest all of my field containers get escaped. Producing a line like this:

insert into table1 values('some field','I want a\r\nnew line\',\'next\r\nplease\');

To fix this I found it was better to implement it this way:

$line = str_replace("<br>","\r\n",$line);
$line = mysql_real_escape_string($line);
$linearray = explode($fieldseparator,$line);
$linemysql = implode("','",$linearray);

Great suggestion - thanks for the advice.

Milani