How can I replace a line feed with a br tag in a MySql sql statement from php?

[SLSCoder]

I'm creating a sql statement in php:
The value of TextField is "We chartered a \n fishing trip".

$sql = "SELECT REPLACE(TextField, '\n', '<br>') AS TextField FROM mytable WHERE ID = 2";

I've tried every combination I can think of like 2 backslashes, 3, 4 . . .
I tried using chr(92) instead of the backslash.
I can't make it work.

How can I get it to return the field (ex. TextField) with a br tag in it instead of a line feed?

[Barand]

Why would you need to?

If you want to output it to an html page showing the line breaks, use nl2br() EG

echo nl2br( $row['TextField'] );

 

[SLSCoder]

Thanks Barand but I know I can do that.
I've got a lot of records in a record set and I'd really rather not have to loop through them all to fix line feeds.

 

[Barand]

4 hours ago, SLSCoder said:

I've got a lot of records in a record set and I'd really rather not have to loop through them all to fix line feeds

Same question - why would you need to?

[SLSCoder]

Am I missing something?
If I understand your solution clearly, you're asking me why I don't want to loop through records to fix any values that include line breaks.

My answer to that would obviously be that I think to fix the values in the query would be significantly more efficient than to loop through and fix each record after the query.

If you don't want to answer my question please just say so.

I'm beginning to think that asking questions on this forum is a bad idea.
 

[gizmola]

9 hours ago, SLSCoder said:

Thanks Barand but I know I can do that.
I've got a lot of records in a record set and I'd really rather not have to loop through them all to fix line feeds.

 

 

Are you outputting the lines?  Because if you are outputting these lines, then there is no need to loop through them more than you already are.

As for the REPLACE, that should work so there is something wrong with your assumption.  In some cases the field might have a '\r' or '\r\n'.  

Or you could have escaped the slash so that it is not a newline character, but rather the "\n" string.

I would suggest running SELECT HEX(TextField) AS TextField FROM mytable WHERE ID = 2 and verifying the contents of the field.

[SLSCoder]

gizmola thank you.

I don't write each record to html on the server or use in line <?php ... ?> tags.
I pass the whole thing (with more than just the recordset) to the client and write the html in Javascript.

When I look at the example record having a line feed in the database (HeidiSQL) I see \n in the text: ie: "We chartered a \n fishing trip".
No \r or \r\n exist in any records, only \n. It's a Linux server and no records are saved with \r in them.

I tried your HEX suggestion. I get long numbers but don't understand what they indicate.
The response I got for that record was this:
text:  we chartered a \n fishing trip.
hex: 7765206368617274657265642061205C6E2066697368696E6720747269702E

When I use this: $sql = "SELECT REPLACE(TextField, '\n', '<br>') AS TextField FROM mytable WHERE ID = 2";
The line feed breaks the line in php (I see in view-source) which makes me think mysql doesn't see it. Php runs it anyway and MySql ignores it.
I break sql lines in PHP all the time just so I can read the sql. Mysql ignores them.
I tried a double slash to escape it in php. It seemed to ignore that too and gave me the same result:
ie: "We chartered a \n fishing trip". That's what I see in the browser.

I changed the value in javascript val = val.replaceAll("\n", "<br>") and that didn't work either.
I see in the database: we chartered a \n fishing trip
I see in the browser: we chartered a \n fishing trip
Now I'm confused

Edited July 19 by SLSCoder

[Barand]

It looks as though it is two separate characters and not a linefeed character (linefeed = 0A)

w  e     c  h  a  r  t  e  r  e  d     a     \  n     f  i  s  h  i  n  g     t  r  i  p  .
77 65 20 63 68 61 72 74 65 72 65 64 20 61 20 5C 6E 20 66 69 73 68 69 6E 67 20 74 72 69 70 2E 
                                             ^^ ^^

 

[Barand]

You need to escape the backslash

REPLACE(TextField, '\\n', '<br>')

Example

SELECT title FROM project WHERE id = 2;
+----------------------+
| title                |
+----------------------+
| Project \n  number 2 |
+----------------------+


SELECT REPLACE(title, '\\n', '<br>') FROM project WHERE id = 2;
+-------------------------------+
| REPLACE(title, '\\n', '<br>') |
+-------------------------------+
| Project <br>  number 2        |
+-------------------------------+

Now you can worry about why your update process is writing two separate characters instead of a single linefeed.

[SLSCoder]

Barand thank you.

It looks like to read HEX I just need to recognize that every 2 numbers represents 1 character in HTML - the percent sign??
I did try using the double backslash but I'll try it again and let you know what I come up with.

[Barand]

1 hour ago, SLSCoder said:

- the percent sign??

25

echo dechex(ord('%'));        // 25

echo chr(0x25);               // %

 

[SLSCoder]

"update process is writing two separate characters": That's curious. I'll have to figure that out. I'm thinking when it's written into the db it gets escaped.
It should show up as 2 lines yes? I'll find it and fix it. Thanks.

"- the percent sign??": Sorry, I meant the HEX response looks like HTML codes without the percent sign.

dechex(ord('%'));  Thanks for that. I looked for how to do this for over 2 hours and never did find it.