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!

ⓘ 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

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

First, here is the download link to the example source code as promised.

 

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.

 

 

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() { try {
    $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
    ]);
  } catch (Exception $ex) { exit($ex->getMessage()); }}

  // (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", "utf8");
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>
  </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 – &lt;strong&gt;FOO&lt;/strong&gt;. Now, &lt; will display as < and &gt; 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.

 

 

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

 

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!

Leave a Comment

Your email address will not be published. Required fields are marked *