PHP replace newlines stored in MySQL DB

Question

I have a MySQL table with 2 columns:

text: stores a multi-line text which could have line breaks as \n, \r, \r\n.... The text is inserted via a webform and could have been inserted by any browser on any OS.
line_break: represents what type of line break this particular text will use (could be \n, <br>, <br />, ...). This is also inserted via a webform by a user.

In PHP I then do the replacement:

$text = preg_replace ("/(\r\n|\r|\n)/", $row['line_break'], $row['text']);

Interestingly, if the line break is stored in the DB as \n I will actually show \n instead of the newline. On the other hand, the following regexp will work correctly:

$text = preg_replace ("/(\r\n|\r|\n)/", "\n", $row['text']);

How do I need to store the newline in the DB for it to "work" correctly?

@YourCommonSense: the word MySQL is present in the title, in the text and actually this question is about MySQL as the user wants to now How do I need to store the newline in the DB for it to "work" correctly?. — Helmut
– Helmut, Commented Aug 9, 2012 at 11:31

GolezTrol · Accepted Answer · 2011-10-01 08:39:45Z

4

In php, "\n" is a special notation for writing that special character that means 'line break'. But if you store the actual text \n in your database, you have just that text, and not the line break character. You have what you get when you write '\n' or "\\n" in PHP. Make sure you store the actual line break character in your database and not the literal \n text.

answered Oct 1, 2011 at 8:39

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adrien Hingert Over a year ago

@GolezTirol, how do I make the user insert the actual linebreak character? To add the second slash I have tried with addslashes, but it doesn't seem to work.

GolezTrol Over a year ago

Just let them type an enter in a text-area, or use str_replace to replace '\n' with "\n".

Collectives™ on Stack Overflow

PHP replace newlines stored in MySQL DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related