Strange things can happen when you retrieve text from a MySQL database and display it on a webpage. Specifically, line breaks and spaces disappear. Here's some more information about this exciting topic.

Disappearing line breaks

Any line breaks are turned into spaces when displayed on a webpage.

Example

For example, here's an entry in a MySQL database that contains two lines:

The new line disappears when displayed on a webpage:

Solution

The solution is to use the value pre-line for the css property white-space:

white-space: pre-line

When applied to an HTML element, pre-line preserves any line breaks inside the element:

To learn more about pre-line, I recommend reading this webpage.

Disappearing spaces

Disappearing line breaks aren't the only problem that can occur when retrieving text from a MySQL database. Another problem occurs with spaces. Specifically, leading spaces, trailing spaces and multiple spaces are all removed when text from a database is displayed on a webpage.

As Louis Lazaris at impressivewebs.com says:

In (X)HTML, anytime you string together a bunch of spaces in your code, the actual page output in your browser will, by default, trim (or collapse) all those spaces down to a single space.

In other words, HTML doesn't like odd spaces lying around so it removes them.

Example

Here's a string in a database that contains five consecutive spaces:

When displayed on a webpage, HTML trims the five spaces down to just one space:

Solution #1

One solution is to put   where you want your spaces to be:

This works because HTML interprets   as a non-collapsable space. Here's the result on the webpage:

However, putting $nbsp; everywhere can be quite annoying. Luckily, there's another solution.

Solution #2

Another solution is to use the value pre-wrap for white-space.

white-space: pre-wrap

pre-wrap is like pre-line, except it preserves spaces as well as line breaks.

There are also other values for white-space like pre, but the advantage of pre-wrap over pre is that it allows the text to wrap naturally on the webpage, which means you don't get long lines of text running off the side of the page.

Try it yourself

Finally, here's an opportunity to discover the problems discussed above for yourself.

Here are a textbox and an editable div that I've rigged together. Whatever you type into one is instantly copied into the other. Feel free to try it out:

Things you can try in the textarea:

• Type a double space in the textarea and it will only appear as a single space in the div.
• Type a leading space or a trailing space in the textarea and it won't appear in the div at all.
• Type a new line in the textarea and it will become a space in the div...
• Type raw HTML in the textarea (such as <b>bold text</b>) and it will be rendered in the div.

Things you can try in the div:

• Type a new line in the div and it will produce <div><br></div> in the textarea.
• Type & in the div and it will appear as $amp; in the textarea.
• Type <> in the div and it will appear as &lt;&gt; in the textarea.

You might wonder what the point of this exercise is, but it turns out that sending text from a textarea to the div creates exactly the same problems as sending text from a database to a webpage. So it's a good way to learn about what problems happen when you display text from a database on a webpage.