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?

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!

ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

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

 

TABLE OF CONTENTS

 

 

QUICK NOTES

• Create a dummy database and import 1-contents-table.sql.
• Change the database settings in 2-lib-content.php to your own.
• Run 3a-insert-html.php and 3b-retrieve-html.php in the web browser.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

PART 1) CONTENTS DATABASE TABLE

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

<?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) {
    $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

<?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

<?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

<?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

<?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.

 

 

 

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