Text to Put in Place of a Line Feed

Could someone tell me how to add a new line in a text that I enter in a MySql table?

I tried using the '\n' in the line I entered with INSERT INTO statement but '\n' is shown as it is.

Actually I have created a table in MS Access with some data. MS Access adds new line with '\n'. I am converting MS Access table data into MySql . But when I convert, the '\n' is ignored and all the text is shown in one single line when I display it from MySql table on a PHP form.

Can anyone tell me how MySQL can add a new line in a text? Awaiting response, thanks!!

Alexis Wilke

17.8k 10 gold badges 77 silver badges 135 bronze badges

asked May 25, 2010 at 7:50

Muhammed UmerMuhammed Umer

1,041 1 gold badge 8 silver badges 6 bronze badges

1

If you're OK with a SQL command that spreads across multiple lines, then oedo's suggestion is the easiest:

                  INSERT INTO mytable (myfield) VALUES ('hi this is some text and this is a linefeed. and another');                                  

I just had a situation where it was preferable to have the SQL statement all on one line, so I found that a combination of CONCAT_WS() and CHAR() worked for me.

                  INSERT INTO mytable (myfield) VALUES (CONCAT_WS(CHAR(10 using utf8), 'hi this is some text', 'and this is a linefeed.', 'and another'));                                  

answered Feb 11, 2011 at 18:10

Don KirkbyDon Kirkby

49.7k 25 gold badges 197 silver badges 277 bronze badges

2

in an actual SQL query, you just add a newline

                INSERT INTO table (text) VALUES ('hi this is some text and this is a linefeed. and another');                              

answered May 25, 2010 at 7:54

chrischris

9,445 1 gold badge 25 silver badges 26 bronze badges

For the record, I wanted to add some line breaks into existing data and I got \n to work ok...

Sample data:

                  Sentence. Sentence. Sentence                                  

I did:

                  UPDATE table SET field = REPLACE(field, '. ', '.\r\n')                                  

However, it also worked with just \r and just \n.

bluish

25.3k 26 gold badges 117 silver badges 176 bronze badges

answered Dec 10, 2010 at 16:52

MattMatt

271 3 silver badges 2 bronze badges

6

                  INSERT INTO test VALUES('a line\nanother line');                                  

\n just works fine here

answered May 3, 2012 at 14:39

xtdsxtds

2,393 2 gold badges 18 silver badges 12 bronze badges

2

MySQL can record linebreaks just fine in most cases, but the problem is, you need <br /> tags in the actual string for your browser to show the breaks. Since you mentioned PHP, you can use the nl2br() function to convert a linebreak character ("\n") into HTML <br /> tag.

Just use it like this:

                  <?php echo nl2br("Hello, World!\n I hate you so much"); ?>                                  

Output (in HTML):

                  Hello, World!<br>I hate you so much                                  

Here's a link to the manual: http://php.net/manual/en/function.nl2br.php

answered Nov 30, 2014 at 11:27

starleaf1starleaf1

2,472 6 gold badges 39 silver badges 62 bronze badges

1

                  INSERT INTO myTable VALUES("First line\r\nSecond line\r\nThird line");                                  

JSW189

6,197 11 gold badges 42 silver badges 71 bronze badges

answered Apr 5, 2013 at 22:05

0

First of all, if you want it displayed on a PHP form, the medium is HTML and so a new line will be rendered with the <br /> tag. Check the source HTML of the page - you may possibly have the new line rendered just as a line break, in which case your problem is simply one of translating the text for output to a web browser.

answered May 25, 2010 at 7:53

David MDavid M

70.3k 13 gold badges 154 silver badges 184 bronze badges

In SQL or MySQL you can use the char or chr functions to enter in an ASCII 13 for carriage return line feed, the \n equivilent. But as @David M has stated, you are most likely looking to have the HTML show this break and a br is what will work.

bluish

25.3k 26 gold badges 117 silver badges 176 bronze badges

answered May 25, 2010 at 8:15

RandyMorrisRandyMorris

1,264 9 silver badges 17 bronze badges

1

1. You have to replace \n with <br/> before inset into database.

   $data = str_replace("\n", "<br/>", $data);

   In this case in database table you will see <br/> instead of new line.

   e.g.

   First Line
Second Line

   will look like:

   First Line<br/>Second Line

2. Another way to view data with new line. First read data from database. And then replace \n with <br/> e.g. :

   echo $data;
$data = str_replace("\n", "<br/>", $data);
echo "<br/><br/>" . $data;

   output:

   First Line Second Line

   First Line
Second Line

   
   You will find details about function str_replace() here: http://php.net/manual/en/function.str-replace.php

answered Jun 14, 2012 at 8:02

AirfulAirful

310 2 silver badges 12 bronze badges

7

use <pre> tag instead of <p> in html to show your \n in database

answered Apr 12, 2021 at 7:42

Adding to the answer given @DonKirby

                  INSERT INTO mytable (myfield) VALUES (CONCAT_WS(CHAR(10 using utf8), 'hi this is some text', 'and this is a linefeed.', 'and another'));                                  

is unnecessary

The CHAR() function doesn't accept the full set of utf8 values. It accepts only ASCII values.

See - https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char

Thus more appropriate would be to use CHAR(10 USING ASCII) in place of CHAR(10 USING utf8)

answered Oct 2, 2021 at 22:34

Payel SenapatiPayel Senapati

896 1 gold badge 10 silver badges 22 bronze badges

1

You can simply replace all \n with <br/> tag so that when page is displayed then it breaks line.

                UPDATE table SET field = REPLACE(field, '\n', '<br/>')                              

answered May 4, 2012 at 10:34

uttamuttam

589 2 gold badges 7 silver badges 33 bronze badges

adamssocidered.blogspot.com

Source: https://stackoverflow.com/questions/2902888/adding-a-line-break-in-mysql-insert-into-text