Using MySQL data to populate sections of HTML template and render page with CGI C++

Tuesday September 27, 2011 ()

One of the many ways to serve dynamic content is through templates and databases. We will demonstrate how to accomplish this idea using MySQL as our data source and MySQL++ to fetch data from MySQL server. CGI C++ renders our output. This procedure is done in a Debian 6 environment but should work the same with any Debian derivatives like Ubuntu. This is Part 2 of our C++/CGI blog series.

The requirements

  1. libcgicpp - CGI/C++ library
  2. Apache2 virtual host configured for cgi
  3. MySQL client and server, MySQL C client library, MySQLC++ library
  4. HTML template

We have already covered items 1 and 2 from Part 1 of this blog series. If you need to read them, it can be found here https://kahimyang.com/kauswagan/HowtoBlogs.xhtml?b=641.

Now we will explore how to read a file (our HTML template), read MySQL database records to replace sections of our template, and render them via CGI, all using C/C++.

Install MySQL and MySQL C/C++ client library.


   # As always update Debian repositories
   apt-get update

   # Install 
   apt-get install mysql-client mysqld-server libmysqlclient-dev 
     libmysqlclient16  libmysql++-dev libmysql++-doc libmysql++3

MySQL will prompt for your root password. Enter any desired database root password.

We now have a database server and client. Next create database and tables. Visit the mysql documentation to learn more about databases and tables and etc. For the purposes of this blog, we assume that you already created a database and a table called html. We also assume that this table has columns with names html_page_id, html_content, html_meta_title, and html_meta_description.

Access database records with MySQL C++ library


    string html,title,description;	

    Connection conn (false);
    if (!conn.connect ("demodatabase","localhost",
           "demouser","demopassword")) {
       cout << conn.error () << endl;
       return 1;
    }

    Query query = conn.query ("select * from html where html_page_id=1");
    StoreQueryResult result = query.store ();

    if (result.num_rows () >= 1) {
       html = (string)result [0]["html_content"];
       title = (string)result [0]["html_meta_title"];
       description = (string)result [0]["html_meta_description"];
    }
    

Our HTML template file. This is our main content. Sections with [[xxxxx]] pattern are replaced with data from our MySQL table.


<html>
<head>
  <title>[[META-TITLE]]</title>
  <meta name="description" content="[[META-DESCRIPTION]]" />
</head>
<body>
<div style="vertical-align:top">
<table>
<tr>
	<td>
		Static/fixed content
	</td>
	<td>
  		[[PAGE-CONTENT]]
	</td> 
</tr>
</table>
</div>
</body>
</html>  

This is a very simple template made for demonstration purposes only. Actual template files could be a lot larger.

Read HTML template file. This HTML template file is read by our C++ code shown below.


 ifstream ifile;

 string line;
 char c;

 ifile.open ("templates/part2.html",ifstream::in);
 
 while (ifile.good()) {
   c = ifile.get ();
   if (c != EOF) {
      line += c; 
   }
 }

 ifile.close ();

We stored our templates in templates/. We read our template one character at a time. Please see the ifstream documentation for other ways to read file content.

Putting it all together


#include <iostream>
#include <fstream>

#include <mysql++.h>
#include <Cgicc.h>
#include <HTTPHTMLHeader.h>
#include <HTMLClasses.h>
#include <CgiDefs.h>


#include <stdlib.h>
#include <stdio.h>

using namespace std;
using namespace mysqlpp;
using namespace cgicc;

int main (int argc, char **argv) {

   cout << HTTPHTMLHeader ();
   cout << HTMLDoctype (HTMLDoctype::eStrict);

   string html,title,description;

   try {
       Connection conn (false);
       if (!conn.connect ("demodatabase",
              "localhost","demouser","demopassword")) {
          cout << conn.error () << endl;
          return 1;
       }

       Query query = conn.query 
              ("select * from html where html_page_id=1");
       StoreQueryResult result = query.store ();
     

       if (result.num_rows () >= 1) {
           html = (string)result [0]["html_content"];
           title = (string)result [0]["html_meta_title"];
           description = 
                (string)result [0]["html_meta_description"];
       }
   }
   catch (exception& e) {

       cout << e.what () << endl;
       return 1;
      
   }


   string line;

   try {

       char c;
       ifstream ifile; 

       ifile.open ("templates/part2.html",ifstream::in);

       while (ifile.good()) {
          c = ifile.get ();
          if (c != EOF) {
	     line += c; 
          }
       }
 
       ifile.close ();
   }
   catch (exception& e) {

      cout << e.what () << endl;
      return 1;

   }

    
   const string content="[[PAGE-CONTENT]]";
   const string pageTitle="[[META-TITLE]]";
   const string metaDescription="[[META-DESCRIPTION]]";

   try {

       int found = line.find (content);
       if (found) {
          line.replace (found,content.length(), html);    
       }

       found = line.find (pageTitle);
       if (found) {
          line.replace (found,pageTitle.length(), title);    
       }

       found = line.find (metaDescription);
       if (found) {
          line.replace 
               (found,metaDescription.length(), description);    
       }

   }
   catch (exception& e) {
       cout << e.what () << endl;
       return 1;
   }

   cout << line << endl;

    
   return 0;
}

To build the above code, we use a Makefile shown below


CXX := g++ 
CXXFLAGS := -I/usr/include/mysql -I /usr/include/mysql++ -I/usr/include/cgicc -g
LDFLAGS := -L/usr/lib -lmysqlpp -lmysqlclient -lcgicc -lm

all: part2

part2: part2.cpp
	g++ $(CXXFLAGS) $(LDFLAGS) -o part2.cgi part2.cpp

clean: rm -f *.o

Please check you man pages for information about the linker options. Output of this blog can be found below.

http://demo.kahimyang.info/cgi-cpp/part2/part2.cgi

That's it. Good luck.


1,628

Comments (Using MySQL data to populate sections of HTML template and render page with CGI C++ )