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
Fullscreen Mode – Click Here
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
and3b-retrieve-html.php
in the web browser.
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
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.
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

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!