Adding a line break in MySQL INSERT INTO text

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!!

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'));

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');

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.

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

\n just works fine here

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

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

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.

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.

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

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

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)

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/>')