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.7k10 gold badges77 silver badges134 bronze badges
asked May 25, 2010 at 7:50
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.6k25 gold badges197 silver badges276 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,4351 gold badge25 silver badges26 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.2k26 gold badges116 silver badges175 bronze badges
answered Dec 10, 2010 at 16:52
MattMatt
2713 silver badges2 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,3932 gold badges18 silver badges12 bronze badges
2
MySQL can record linebreaks just fine in most cases, but the problem is, you need
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
tag.
Just use it like this:
Output [in HTML]:
Hello, World!
I hate you so much
Here's a link to the manual: //php.net/manual/en/function.nl2br.php
answered Nov 30, 2014 at 11:27
starleaf1starleaf1
2,4426 gold badges39 silver badges61 bronze badges
1
INSERT INTO myTable VALUES["First line\r\nSecond line\r\nThird line"];
JSW189
6,18711 gold badges42 silver badges71 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
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.3k13 gold badges154 silver badges184 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.2k26 gold badges116 silver badges175 bronze badges
answered May 25, 2010 at 8:15
RandyMorrisRandyMorris
1,2649 silver badges17 bronze badges
1
You have to replace
\n
with
before inset into database.$data = str_replace["\n", "
", $data];In this case in database table you will see
instead of new line.e.g.
First Line
Second Line
will look like:
First Line
Second LineAnother way to view data with new line. First read data from database. And then replace
\n
with
e.g. :echo $data;
$data = str_replace["\n", "
", $data];echo "
" . $data;output:
First Line Second Line
First Line
Second Line
You will find details about function str_replace[] here: //php.net/manual/en/function.str-replace.php
answered Jun 14, 2012 at 8:02
AirfulAirful
3102 silver badges12 bronze badges
7
use tag instead of
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 - //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
8961 gold badge10 silver badges22 bronze badges
1
You can
simply replace all \n
with
tag so that when page is displayed then it breaks line.
UPDATE table SET field = REPLACE[field, '\n', '
']
answered May 4, 2012 at 10:34
uttamuttam
5892 gold badges7 silver badges32 bronze badges