3 Steps To Store & Retrieve HTML Code With PHP MySQL

Welcome to a quick tutorial on how to store and retrieve HTML code with PHP and MySQL. Are you trying to create a custom content management system (CMS), a template system, or maybe you want to accept code snippet contributions from users?

HTML code can be stored in a regular database text field. For example – INSERT INTO `contents` (`id`, `text`) VALUES (123, '<p>Hello</p>')
To retrieve HTML code from the database, run a SELECT query as usual. For example – SELECT `text` FROM `contents` WHERE `id` = 123

Yes, it’s no secret. HTML is literally plain text with some formatting. No need to think too deeply, there are no “special processes” to store and retrieve HTML code in a database. Read on if you need a detailed example!

P.S. If you are looking for “how to save an HTML form into the database” – Check out this tutorial instead.

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

STORE & RETRIEVE HTML CODE

All right, let us now get into the example of how to store and retrieve HTML code in the database.

PART 1) CONTENTS DATABASE TABLE

1-contents-table.sql

CREATE TABLE `web_contents` (
  `id` bigint(20) NOT NULL,
  `contents` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `web_contents`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `web_contents`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

Field	Description
id	Primary key, auto-increment.
contents	Text field to store HTML code.

First, we start with creating a simple database table to hold the HTML code or snippet. Look no further, a regular text or varchar field is all we need. Since HTML is literally plain text, we can store it just like “any other strings” without “special processing”.

PART 2) PHP CONTENT LIBRARY

2-lib-content.php

<?php
class Content {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $lastID = null;
  public $error = "";
  function __construct() {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct() {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) HELPER - EXECUTE SQL QUERY
  function exec ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) SAVE CONTENT
  function save ($html) {
    $this->exec("INSERT INTO `web_contents` (`contents`) VALUES (?)", [$html]);
    return true;
  }

  // (E) LOAD CONTENT
  function load ($id) {
    $this->exec("SELECT `contents` FROM `web_contents` WHERE `id`=?", [$id]);
    return $this->stmt->fetchColumn();
  }
}

// (F) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (G) CONTENT OBJECT
$_CONTENT = new Content();

Next, we create a simple library that will save and load HTML content in the database. This may be intimidating to beginners, but keep calm and look closely.

(A, B, G) When $_CONTENT = new Content() is created, the constructor connects to the database automatically. The destructor closes the connection.
(C) exec() A helper function to run an SQL query.
(D & E) Self-explanatory…
- save() Save the given HTML snippet into the database.
- load() Load the given HTML snippet.
(F) Remember to change the database settings to your own.

PART 3) STORE & RETRIEVE HTML CODE

3A) STORE HTML CODE IN DATABASE

3a-insert-html.php

<?php
// (A) LOAD CONTENT LIBRARY
require "2-lib-content.php";
 
// (B) INSERT HTML
echo $_CONTENT->save("<strong>HELLO WORLD</strong>")
  ? "OK" : "ERROR" ;

Sorry to disappoint the folks who are looking for “cool hacker code”. This is all we need to store HTML in the database – Just use the library to do a simple INSERT SQL.

3B) RETRIEVE HTML CODE FROM DATABASE

3b-retrieve-html.php

<?php
// (A) LOAD CONTENT LIBRARY
require "2-lib-content.php";
 
// (B) OUTPUT HTML ?>
<!DOCTYPE html>
<html>
  <head>
    <title>MYSQL HTML DEMO</title>
    <meta charset="utf-8">
  </head>
  <body>
    <?=$_CONTENT->load(1)?>
  </body>
</html>

Lastly, we only need to retrieve the HTML snippets from the database table and insert them into an HTML page. Nothing special here again… Just use the library to do a “regular” SELECT SQL query, and output it. The end. HTML and the database lived happily ever after.

3C) HTML ENTITIES

3c-html-entity.php

<?php
// (A) LOAD CONTENT LIBRARY
require "2-lib-content.php";
 
// (B) RETRIEVE HTML - CONVERT HTML ENTITIES
$html = htmlentities($_CONTENT->load(1));
 
// (C) OUTPUT ?>
<textarea><?=$html?></textarea>
<code><?=$html?></code>

Some of you guys may want to retrieve the HTML in the database, then put it into a <textarea> or display it as a code snippet. As you may have guessed, if we plug that HTML directly, the browser will just render it as it is. For example, <textarea><strong>HELLO WORLD</strong></textarea>.

To “fix” this problem, we use the htmlentities() function to convert the angle brackets to the equivalent HTML entities – <strong>FOO</strong>. Now, < will display as < and > will display as >.

EXTRA) STRIPPING HTML TAGS

4-strip-tag.php

<?php
// (A) LOAD CONTENT LIBRARY
require "2-lib-content.php";
 
// (B) HTML SNIPPET WITH SCRIPT
$html = "<p>Hi!</p> <script>alert('FOO')</script>";
 
// (C) ALLOW ONLY "P" TAGS
$html = strip_tags($html, ["p"]);
 
// (D) SAVE INTO DATABASE
echo $_CONTENT->save($html)
  ? "OK" : "ERROR" ;

If you need an “HTML filter” and only require the “text without tags”, use the strip_tags() function – We can also define which tags to keep as exceptions.

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

EXTRA BITS & LINKS

That’s all for this guide, and here is a small section on some extras and links that may be useful to you.

IS IT REALLY OK TO STORE HTML IN THE DATABASE!?

It is absolutely all right and normal to store HTML code in the database. Many content management systems (CMS) do the same to save pages, posts, and articles in the database this way. Forget the angry trolls who tell you it is a bad idea.

The more important question to ask is – Does it make sense? If you are working on a huge project, then it can definitely benefit from a template system and search features from the database. But if it is just a small project with only a couple of pages, then raw HTML pages will probably be a smarter idea.

LINKS & REFERENCES

Storing HTML code in Mysql database – SitePoint Community
Entity – MDN
HTML Entity List – Freeformatter
HTML Encoding With htmlspecialchars and htmlentities – Vegibit
htmlentities function – PHP

TUTORIAL VIDEO

INFOGRAPHIC CHEAT SHEET

How To Store HTML Code In MySQL (click to enlarge)

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!