How to store formatted text in MySQL table?

Question

[My EDITED New Question]

I am taking text input in HTML form as <textarea>. Suppose the user entered the following text:

1. Hello     World
2. Hi World

3. Hola

My PHP code is inserting into the table as: 1. Hello World\r\n2. Hi World\r\n\r\n3. Hola

I am displaying this text into a DIV element by using the below method (assume that $text is retrieved from database):

<div><?php echo $text ?></div>

The output I am getting is: 1. Hello World 2. Hi World 3. Hola

How to get the exact output as user entered? Only importance to me right now is spaces, tabs and new line characters. As mentioned in the below answers, nl2br() is not suggested. Any other way?

[My Old Question] I want to store formatted text into a mysql table. By formatted, I mean to preserve proper bold characters, italics, underline, spaces, tabs, punctuation marks, newline characters etc.

If the above is not possible, if I can preserve the following formatting then also my requirement is fulfilled:

spaces and tabs
punctuation marks and newline characters

Is there any data type which can store such data? What about VARCHAR, TEXT and CHAR data types? Please help!

For example: If I type the following text:

Hi!

Hello there!

then it should NOT print like

Hi! Hello there!

Not HTML, simple text. I have edited my question. Pls check. Thanks!! — sumit, Jul 04 '11 at 17:55
I want my customers to add reviews about some items (like mobile phones etc). I want the review to appear as it is as entered by the reviewer. I am using PHP with Mysql. Pls let me know if more details are required. Thanks!! — sumit, Jul 04 '11 at 17:58
I am not using any edit control. I am just providing a text box to the user in which he enters some text (with newline and other characters). This text I am storing into database and displaying it back on my website. — sumit, Jul 04 '11 at 18:08
I did try using pre tags, as the answer of "JB Nizet" says and it works — user3150566, Jan 01 '14 at 03:36

JB Nizet · Answer 1 · 2011-07-04T18:20:59.760

Raw text include characters only, not formatting like bold, italics or underline. Tabs, punctuation marks and newline are characters, so a simple varchar will do if all you really need is this.

But you have to decide on a formatting protocol if you want bold, italics and underlined text: HTML, wiki syntax, RTF, etc. If this format is textual, a varchar will do. If it's binary, you'll need a blob.

If you have newlines in your text and it's displayed on a single line, it's probably because you output it in a HTML page, where sequences of space characters (tabs, spaces, newlines, etc.) are converted to a simple space. Use a <pre>your HTML-escaped text here</pre> section, and it will display the newlines, tabs and multiple spaces correctly.

score 3 · Answer 2 · edited Dec 29 '15 at 11:49

3

I think you can store with utf_encode(). and get it with utf_decode(). You can see the n12br function of PHP.

-- EDIT --

I encrypt using base64_encode() and to get I use base64_decode(). Tabs, spaces and special characters are preserved.

edited Dec 29 '15 at 11:49

Shahzad Barkati

2,532
6
25
33

answered Jul 04 '11 at 17:57

Bruno Alano

643
1
11
21

2

And what does base64 encoding have to do with the question? – JB Nizet Jul 04 '11 at 18:08
With base64 encode you can preserve tabs, spaces, special characters and more details of a original text. This function is easy to encrypt and decrypt. – Bruno Alano Jul 04 '11 at 18:23
@Bruno Alano: What you're saying makes no sense. Base64 is useful for storing binary as ASCII, but it doesn't preserve anything more than raw text. And it is NOT encryption: it's just encoding. Anyone can decode base64-encoded data. To decrypt encrypted data, you need to have a secret key. – JB Nizet Jul 04 '11 at 19:16

score 3 · Answer 3 · answered Jul 04 '11 at 18:01

3

Spaces and punctuation are preserved.

If you're not seeing them, then you're probably outputting the text in an HTML document and forgetting to mark it up as HTML: remember, web browsers ignore newlines and consecutive spaces in plaintext.

As for formatting like bold, you need to come up with some way of marking-up your text to store the formatting information. You might consider HTML or Markdown.

answered Jul 04 '11 at 18:01

Lightness Races in Orbit

378,754
76
643
1,055

Thanks Tomalak. Can you please tell how can I preserve newline characters? Bold and italic characters are not of much importance to me right now. – sumit Jul 04 '11 at 18:04
@iSumitG: As the first line of my answer says, in bold lettering, they _are_ preserved. – Lightness Races in Orbit Jul 04 '11 at 18:06
2

`nl2br()` will convert `\n` to `
`, but this is for display purposes only. Doing such a transformation fundamentally alters the text, so you won't get back what you'd put in. – Marc B Jul 04 '11 at 18:18
@MarcB: I did not recommend `nl2br`. – Lightness Races in Orbit Jul 04 '11 at 18:21
Sory, should've @isumitg'd on that. – Marc B Jul 04 '11 at 18:21
you have to store using tags html like Marc B sayed – macwadu Jul 04 '11 at 18:25
@macwadu: No, you don't. And Marc didn't say that at all. – Lightness Races in Orbit Jul 04 '11 at 18:27
@macwadu: You _can_ store HTML tags, and use these as representation for formatting. I said that you don't have to, and that Marc didn't say you have to. You may pick any formatting scheme you like. – Lightness Races in Orbit Jul 05 '11 at 09:39
@TomalakGeret'kal Obvious you can pick any formatting scheme you like. My intent was to say that in html it works if it is the best way to do it only each one in is project can know that. Sorry for the confusion. – macwadu Jul 05 '11 at 09:54

How to store formatted text in MySQL table?

3 Answers3

Linked