HTML in MySQL Database -- Best Practices

Question

I'm beginning to create a system whereby I (as the only user at present) will be loading a dynamically created PHP page which has a <table> in it. I then will be grabbing the HTML of that <table> and saving it to be displayed to other users in a separate area of the website.

My Questions: What are some best practices to follow for this kind of thing? Saving the HTML as TEXT or LONGTEXT in MySQL? BLOB? Maybe even saving this data as a .txt file which uses PHP include() to include it into the file which displays it to users?

I'm not really sure of the best approach for this kind of thing, and thus the barrage of questions. I'm also not very familiar with creating databases and therefore, I'm not knowledgeable as to their strengths/weaknesses. It seems like using a MySQL database for this is the way to go, but I'm not married to it.

Note #1: The HTML must be preserved in it's entirety. So something like <div>Let's use blue for this.</div> can't end up coming out as <div>Let\'s use blue for this.</div>.

Note #2: The table I'm saving from gets randomly generated (including number of rows/columns) each time. So, I need all data within the table, including all <tr>'s and <td>'s.

Answer 1

Both MySQL and HTML files can work. Your choice should depend on how simple the data is, and how much you are storing.

Some considerations:

1. Speed. The HTML files and include() approach is going to be faster. The file system is the fastest, simplest form of data persistence.

2. Horizontal scalability. If you adopt the file system approach, you are more or less tied to the disk on that machine. With a separate database engine, you have the future option of running the database on separate cluster servers on the network.

3. Meta Data. Do you need to store things like time of creation, which user created the HTML, how many times it has been viewed by other users? If so, you probably only have one realistic choice - a "proper" database. This can be MySQL or perhaps one of the NoSQL solutions.

4. Data Consumption. Do you show the table in its entirety to other users? Or do you show selected parts of it? Possibly even different parts to different users? This impacts how you store data - the entire table as ONE entity, or each row as an entity, or each individual cell.

5. TEXT or LONGTEXT? Of course only applicable if you're going with SQL. The only way to answer this is to know how many bytes you are expecting to store per "HTML fragment". Note that your character encoding also impacts the number of bytes stored. Refer to: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

Also note that in MySQL, each TEXT/LONGTEXT may also result in an I/O to the disk.

As for the concern:

The HTML must be preserved in its entirety.

As long as you don't escape the HTML at any point, you should be fine. At first glance, this violates a security best practice, but if you think about it, "not escaping HTML" is exactly what you want to do. The practice of escaping HTML output only helps to eliminate HTML syntax being parsed as HTML tags (potential malicious), but in your case, you don't want HTML syntax eliminated at all - you intentionally want <td> to be parsed into an actual HTML table cell. So do not escape anything and the example you gave should never occur.

Just note: although you do not HTML-escape the output, you still should filter your inputs. In essence, this means: before writing to your DB, check that the user input is indeed HTML. To enhance your application's security, it may also be wise to define rules for what can be stored in those table cells - perhaps no <iframe> or <a> allowed, no style attributes allowed etc. Also, watch out for SQL injection - use PDO and prepared statements if you're going PHP + MySQL.

Answer 2

Live by this rule: PHP is for formatting; MySQL is for data.

PHP is for building HTML. MySQL is useful as a persistent store for data from which PHP can build HTML.

I have built several systems with Apache+PHP+MySQL. Each page is PHP-generated HTML. I do not store <html tags> in MySQL, only data.

Some of my pages perform one SELECT; many pages perform a dozen or more. The performance is just fine. I use PHP subroutines to build <table>s. I construct style variations based on the data.

Let's use blue for this might be in some database table; PHP code would add the <div> tags around it.

Answer 3

Someone is going to come up with the php to do this correctly, but here's an idea.

1. Learning a database is a separate exercise that should not be mixed with learning anything else. Leave MySQL out of this. If you want to go back and redo the project with it as a learning exercise, after you've spent some time learning the basics, go ahead.
2. What you are going to want to do is to generate your table as a separate .php fragment, and then save it to the disk under a unique name that you can use to find it again later (maybe it is a date/time, maybe a number that keeps increasing, maybe a different way). After it is saved, you can then include it into your page as well as the other pages you want to include it. Getting that unique name may be a challenge, but that is a different question (and if you look around, probably already has an answer somewhere).
3. If you know .php enough, the rest should be self explanatory. If you don't, then now is an excellent time to learn. If you have further questions, you can search stack overflow or google for the answers. Chances are, the answers are out there.
4. Since I do know databases, there could be an interesting discussion that the other posters are trying to have with you about how to store the data. However, that discussion will only be really useful to you once you understand what we are trying to talk about. Besides, I bet it has already been asked at least once.

So try out that plan and come back and post how you did it for future readers. Happy coding.

Answer 4

What you want to do is absolutely not Three-tier architecture like said Rick James, you are mixing data and presentation. But like in everything, it is up what you want to do and your strategy.

• Let say, if you are looking to build an application with specific mould of page with a specific space for an image with a specific data at this place and you want to maintain this same architecture for years, you should separate data and presentation. Most of the time, big companies, big applications are looking for this approch. Even if I know some big companies application, we have stored some HTML code in database.

• Now, if you want to be free to change every page design quickly, you can have HTML tag in your database. For example, Wordpress has a lot of HTML tag in its table for years and it is still maintained and used by a lot of people. Saving HTML in database? According to your needs/strategy, it does not schock me.

• And for your information, in Wordpress a HTML post is saved as LONGTEXT.

• In this approach, like Wordpress, you will not have a strong database model, it will be messy and not really relationnal.

^^

Answer 5

I would recommend thinking of your table as a kind of object. SQL tables are meant to store relational data. As such, why not represent the table in that fashion? Below is a really basic example of how you could store a table of People data in SQL.

<table>
<thead>
    <th>People ID</th>
    <th>First Name</th>
    <th>Last Name</th>
</thead>
<tbody>
    <tr>
        <td>1</td>
        <td>John</td>
        <td>Doe</td>
    </tr>
    <tr>
        <td>2</td>
        <td>Jane</td>
        <td>Doe</td>
    </tr>
</tbody>

And then your SQL Table would store the encrypted string as follows (using JS):

function utf8_to_b64( str ) {
  return window.btoa(unescape(encodeURIComponent( str )));
}

function b64_to_utf8( str ) {
  return decodeURIComponent(escape(window.atob( str )));
}

var my_table = $('#table').html();

// Store this
var encrypted_string = utf8_to_b64(my_table);

// Decode as so
var table = b64_to_utf8(encrypted_string);

B64 encoding retrieved from here: https://developer.mozilla.org/en-US/docs/Web/API/WindowBase64/Base64_encoding_and_decoding

Answer 6

I don't know how many tables are you planning to store, but if you don't need to make complicated operations like sorting or filtering you could just store them in files in a folder structure, that will consume less resources than MySQL and the entire file is preserved.

If you need many tables then don't store the HTML table, store a json with the info like this:

[
    [
    { 
      "content": "Let's use blue for this.",
      "color": "blue"
    },
    { 
      "content": "Let's use red for this.",
      "color": "red"
    }
    ],
    [
     { 
      "content": "Another row.",
      "color": "blue"
    },
     { 
      "content": "Another row.",
      "color": "red"
    }
  ]
]

And then rebuild it when you call it. Will use less space and will be faster to transfer.

If your tables are in a fixed format, then make a table with the fields you need.