[RESOLVED] HTML TextArea and SQL TEXT data type (Multiline problem)

[Napoleon]

Hi guys,
I'm using a textarea to allow entry of multiline comments, and I save them to a MySQL table into a field that is of type "TEXT".

When I wish to display (using ECHO) the contents of that field in a different page, it seems to drop all the carriage returns and linefeeds. Thus it's all displayed on a single line.

Could someone please direct me to some code that shows how to do this correctly?

Cheers,
Nap

[penagate]

If you're displaying the text as HTML, verify that they're there in the source code, not the rendered HTML. You'll need to convert \n to <br> or enclose the text with <pre>.

If you're displaying it as plain text, then that's a different issue.

[Napoleon]

Penagate,
Thnx 4 ur response.

I've been hunting around other forums and testing things, and found that this is what I need to do BEFORE I save it to the database:

$Action_Text = nl2br(htmlspecialchars($_POST[comments]));

Reteiving it from the database and displaying it is a straight forward echo.

Cheers,
Nap

[visualAd]

http://www.php.net/nl2br

[penagate]

I've been hunting around other forums and testing things, and found that this is what I need to do BEFORE I save it to the database:

Noooooooooo.

That's not what a database is for.

That's like making a pile of physical cheques rather than jotting down deposit figures.

Don't mix data formatted for presentation with raw data. What happens when you want to change your output format from HTML to XML? More drastic, what if you want to change it to a PDF? What if you want to use the database with a different program altogether? You'd have all this HTML in your database and it would be of no use, until you spent ages writing a script to go through all your records removing the HTML that shouldn't have been there in the first place.

Always store raw data in the database, and apply the formatting after you've retrieved the record(s).

It may not make much difference in a small script, but get into the habit now and it'll save you tons of time when you work on something more complex. It might even save you a job interview.

[stangelanda]

That's not what a database is for...Don't mix data formatted for presentation with raw data.

I can see where you are coming from. You're right that raw data is a useful thing to store in a database. I can understand how someone might conceptually have problems with other uses of a database. However, limiting yourself to raw data is constraining yourself unnecessarily.

Databases are useful for caching data as while. Sure you can eat the preformance hit from a couple of extra calls to convert ampersands to &amp; but what happens when you do something more complex. We sometimes convert text URLs to hyperlinks. For the link text we crawl the linked site, and grab the title of the page we are linking to. Or in your own example converting to a PDF can take minutes for large enough files. Or what if they are using a WYSIWYG editor. How are you going to let administrators use tools to combine presentation and content then store them separately.

What happens when you want to change your output format from HTML to XML?

You're completely right, you would have to convert it all or throw it away. In this case the function calls are pretty much reversible. A couple of simple scripts could return the data in plain text, very close to its original form. But you know that....

It may not make much difference in a small script, but get into the habit now and it'll save you tons of time when you work on something more complex.

That word habit is so important, and I can't agree more. It can save you tons of time on complex projects if you incorrectly predict that the format of this code will never change. (Something you can't know in most cases.)

However, the solution is most certainly not as you say

Always store raw data in the database, and apply the formatting after you've retrieved the record(s).

When your formatting gets more complex, your server admin is going to hunt you down. Storing both is a good choice. It's not uncommon to see raw and cached columns in my database. The wasted space is cheaper than the wasted CPU cycles. Or store them in separate tables if you can't handle the concept of cached and raw data sharing the same table. If you have to choose just one I'd save the cached version and make a note in the specifications that this data cannot be easily changed to another format. This is all you can do in cases like WYSIWYG editors.

[penagate]

You're completely correct, of course; in a heavy load situation, one would apply the formatting once and cache the result in the database. However, I wouldn't say that the solution is not what I said - caching doesn't affect the core philosophy of database use. It's simply a special case.

Welcome to the forums, by the way.

[visualAd]

Cached data IMO is raw data. The application using the cache should check regularly for updates to that data refresh it when required. Its format or context has no relevance.

It is always sensible to cache the results of transformation processes, especially those that manipulate XML and HTML data as these are usually quite intensive. Static data / templates should always however reside in a separate file.

IMO using a database to store a message/comment is appropriate. Using it to store it in HTML format is also appropriate where for the purposes of efficiency you do not want to be transforming it on the fly. It would however also be advisable to have the original text stored too.

Without a doubt deciding how data is stored and retrieved and hitting the medium between, speed, efficiency and flexibility is no easy task. Often refinements will be necessary after going live that were not foreseen previously.

One word of advice however: accessing a file on the local file system is typically 60% faster than connecting to a database server, executing a query and fetching the result. If at all possible, you want to be looking at caching the data from queries within text files on the local system and even better, within the user session. PHP provides the functions serialize() and unserialize() which convert PHP variables into file safe data strings.