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:

1. spaces and tabs
2. 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!

Answer 1

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.