Best practice how to store HTML in a database column

Question

I have an application that modifies a table dynamically, think spreadsheet), then upon saving the form (which the table is part of) ,I store that changed table (with user modifications) in a database column named html_Spreadhseet,along with the rest of the form data. right now I'm just storing the html in a plain text format with basic escaping of characters...

I'm aware that this could be stored as a separate file, the source table (html_workseeet) already is. But from a data handling perspective its easier to save the changed html table to and from a column so as to avoid having to come up with a file management strategy (which folder will this live in, now must include folder in backups, security issues now need to apply to files, how to sync db security with file system etc.), so to minimize these issues I'm only storing the ... part in the database column.

My question is should I gzip the HTML , maybe use JSON, or some other format to easily store and retrieve the HTML from the database column, what is the best practice to store HTML content in a datbase? Or just store it as I currently am as an escaped text column?

Answer 1

If what you are trying to do is save the HTML for redisplay, what's wrong with saving it as is, then just retrieving it via a stored proc, and re-displaying it for them when needed?

Say you have an HTML page, which can select some kind of ID from a list, either on a ThickBox page, or from a select option.

Normally for this kind of situation, you would probably query the DB via $Ajax possibly JSon, or not.

Then the result sent back to the $Ajax call will be your resultant data.

Then you replace the Div which holds your SpreadSheet with the DB SpreadSheet.

So, in answer to your original question, you could store the SpreadSheet with some sort of ID, storing it as the HTML of the Div.

When retrieved, you merely replace the Div HTML, with what you have stored.

Answer 2

You probably should bit the bullet and parse the table (using an HTML parser perhaps), as otherwise you risk the user storing damaging JavaScript in the table data. (This means the cell contents should be parsed as well). The data can still be stored as a blob (maybe compressed CSV or JSON), but you need to make certain it is not damaging.

Answer 3

It depends on the size of the HTML. You could store it as a binary BLOB after zipping it. Most of the time it is just best to store the data directly after escaping the SQL characters that may cause problems.
As asked many times why are you storing the view instead of the model ?