4 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 database, into a regular 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 * FROM `contents` WHERE `id` = 123

Yes, it’s no secret. HTML is literally plain text with some formatting. No need to think too deep, 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.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Store Retrieve HTML Useful Bits & Links
Tutorial Video The End

 

DOWNLOAD & NOTES

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

 

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.

 

QUICK NOTES

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

If you spot a bug, please feel free to comment below. I try to answer 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.

 

 

STORE & RETRIEVE HTML CODE

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

 

STEP 1) CONTENTS DATABASE TABLE

1-contents-table.sql
CREATE TABLE `web_contents` (
  `id` int(11) NOT NULL,
  `contents` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `web_contents`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `web_contents`
  MODIFY `id` int(11) 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”.

 

STEP 2) SNIPPET TO CONNECT TO DATABASE

2-connect-db.php
<?php
// (A) 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', '');

// (B) CONNECT TO DATABASE
try {
  $pdo = new PDO(
    "mysql:host=".DB_HOST.";charset=".DB_CHARSET.";dbname=".DB_NAME,
    DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

Next, we create a simple snippet that will connect to the database using the PDO extension… Just take note that this is a lazy and pretty bad way to do it. For big projects, it is a better idea to keep all the common functions in a class library. Remember to change those settings to your own.

 

 

STEP 3) STORING HTML CODE IN THE DATABASE

3-insert-html.php
<?php
// (A) CONNECT TO DATABASE
require "2-connect-db.php";
 
// (B) INSERT HTML
$html = "<strong>HELLO WORLD</strong>";
$stmt = $pdo->prepare("INSERT INTO `web_contents` (`contents`) VALUES (?)");
$stmt->execute([$html]);
echo "OK";

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

 

STEP 4) RETRIEVING HTML CODE FROM THE DATABASE

4-retrieve-html.php
<?php
// (A) CONNECT TO DATABASE
require "2-connect-db.php";
 
// (B) RETRIEVE HTML
$id = 1;
$stmt = $pdo->prepare("SELECT * FROM `web_contents` WHERE `id`=?");
$stmt->execute([$id]);
$contents = $stmt->fetch();
 
// (C) OUTPUT HTML ?>
<!DOCTYPE html>
<html>
  <head>
    <title>MYSQL HTML DEMO</title>
  </head>
  <body>
    <?=$contents['contents']?>
  </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 connect to the database, do a “regular” SELECT SQL query, and output it. The end. HTML and the database lived happily ever after.

 

 

EXTRA) HTML ENTITIES

5-html-entity.php
<?php
// (A) CONNECT TO DATABASE
require "2-connect-db.php";
 
// (B) RETRIEVE HTML - CONVERT HTML ENTITIES
$id = 1;
$stmt = $pdo->prepare("SELECT * FROM `web_contents` WHERE `id`=?");
$stmt->execute([$id]);
$contents = $stmt->fetch();
$contents = htmlentities($contents['contents']);
 
// (C) OUTPUT ?>
<textarea><?=$contents?></textarea>
<code><?=$contents?></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

6-strip-tag.php
<?php
// (A) CONNECT TO DATABASE
require "2-connect-db.php";
 
// (B) RETRIEVE HTML - CONVERT HTML ENTITIES
$id = 1;
$stmt = $pdo->prepare("SELECT * FROM `web_contents` WHERE `id`=?");
$stmt->execute([$id]);
$contents = $stmt->fetch();
$contents = strip_tags($contents['contents']);
 
// (C) OUTPUT ?>
<textarea><?=$contents?></textarea>

If you need a “stronger version” and only require the “text without tags”, use the strip_tags() function.

 

 

USEFUL 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 *