Store & Retrieve HTML Code From MySQL Database With PHP

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

We can store HTML code in a MySQL database “as-it-is”, since HTML code quite literally plain text.

  1. Open a database connection – $pdo = new PDO( "mysql:host=HOST;charset=utf8;dbname=NAME", "USER", "PASSWORD");
  2. Prepare the insert SQL statement – $stmt = $pdo->prepare("INSERT INTO `TABLE` (`CONTENT`) VALUES (?)");
  3. Execute the SQL statement – $stmt->execute(['<strong>This is HTML</strong>']);

To retrieve the stored HTML code, we simply do a SQL select query.

  1. Prepare the insert SQL statement – $stmt = $pdo->prepare("SELECT * FROM `TABLE` WHERE `ID`=?");
  2. Execute the SQL statement – $stmt->execute([123]);
  3. Fetch the result – $results = $stmt->fetch();

That covers the basics, but just how does this work exactly? Let us walk through an example in this guide – Read on!

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

 

 

REAL QUICK TUTORIAL

 

TABLE OF CONTENTS

Download & Notes Store Retrieve HTML Useful Bits & Links
What’s Next?

 

 

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 test database and import 1-contents-table.sql.
  • Change the database settings in 2-insert-html.php and 3-retrieve-html.php to your own.
  • Launch 2-insert-html.php, then 3-retrieve-html.php.

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 on how to store and retrieve HTML code in the database.

 

STEP 1) DATABASE – CONTENTS 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 establishing the database and this is pretty much all the required basics – Just a regular text or varchar field. Since HTML is literally plain text, we can store it just like “any other strings”.

 

 

STEP 2) PHP & MYSQL TO STORE HTML

2-insert-html.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()); }

// (C) INSERT DUMMY HTML - AS IT IS
$html = "<strong>HELLO WORLD</strong>";
$stmt = $pdo->prepare("INSERT INTO `web_contents` (`contents`) VALUES (?)");
$stmt->execute([$html]);
$stmt = null;

// (D) INSERT DUMMY HTML - HTML ENTITIY (CODE SNIPPET)
$html = htmlentities("<strong>FOO BAR</strong>");
$stmt = $pdo->prepare("INSERT INTO `web_contents` (`contents`) VALUES (?)");
$stmt->execute([$html]);
$stmt = null;
 
// (E) CLOSE CONNECTION
if ($stmt !== null) { $stmt = null; }
if ($pdo !== null) { $pdo = null; }
echo "OK";

Next, let us create a PHP script to store HTML code into the database table – There is “nothing special” about HTML. Just treat them like “regular strings”, and use the “usual” INSERT INTO SQL statement to store into the database.

 

EXTRA) WHAT ARE HTML ENTITIES?

An HTML entity is a piece of text (“string”) that begins with an ampersand (&) and ends with a semicolon (;) . Entities are frequently used to display reserved characters (which would otherwise be interpreted as HTML code), and invisible characters (like non-breaking spaces).

MDN

Some of you guys may have noticed the usage of htmlentities() to store the HTML code snippet. Maybe a simple example will illustrate HTML entities and why they are required. Let’s say that we want to display the snippet <strong>FOO</strong> on an HTML page. But as you might have guessed, the web browsers will process it as an HTML tag and render it as-it-is.

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

 

 

STEP 3) PHP & MYSQL TO RETRIEVE HTML

3-retrieve-html.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()); }
 
// (C) RETRIEVE HTML
$stmt = $pdo->prepare("SELECT * FROM `web_contents` WHERE `id` IN (1,2)");
$stmt->execute();
$contents = $stmt->fetchAll();
 
// (D) CLOSE CONNECTION
if ($stmt !== null) { $stmt = null; }
if ($pdo !== null) { $pdo = null; }
 
 // (E) OUTPUT HTML ?>
<!DOCTYPE html>
<html>
  <head>
    <title>MYSQL HTML DEMO</title>
  </head>
  <body>
    <h1>"NORMAL" HTML CODE</h1>
    <?=$contents[0]['contents']?>
    <br><br>
 
    <h1>HTML CODE SNIPPET</h1>
    <pre><code><?=$contents[1]['contents']?></code></pre>
  </body>
</html>

Lastly, we create a PHP script to retrieve HTML snippets from the database table and insert them into an HTML page. This should be pretty straightforward now – We do not need any “special processing” to output HTML from the database. It is just a string that can be output using echo, print, or the short echo tag.

 

 

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

Yes, of course, we can store HTML in the database. But 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 sticking with raw HTML pages will probably be a smarter idea… You decide.

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

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

 

LINKS & REFERENCES

 

WHAT’S NEXT?

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 *