Home > Articles > Data > MySQL

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

CGI Program

Remember, I'm using the two libraries I mentioned earlier, MySQL++ and cgicc, which you must link into your application.

NOTE

I ran into something strange: The compiler freaked if I included the MySQL header file before the CGI header files. But when I reversed it and put the MySQL include statement after the ones for the CGI library, all was fine. So remember this order.

The cgicc library makes heavy use of the C++ Standard Library. When you want to find out what information the user typed into the form, the cgicc library stuffs the information for each form element into a Vector instance. (Remember, a Vector is basically a souped-up array.) To access the elements of the Vector, you need to iterate through them using an iterator. Yet, most of the elements you use in HTML have only one item (such as a string that you type into a text field). Thus, I created myself a little helper function called getFormString that takes the single string from an element's Vector and saves the value to a string instance.

Here's the complete CGI program:

#include <iostream>
#include "cgicc/Cgicc.h"
#include "cgicc/HTTPHTMLHeader.h"
#include "cgicc/HTMLClasses.h"

// mysql must come after cgi stuff for some reason
#include <mysql++>

using namespace cgicc;
using namespace std;

// Helper function for form text boxes and lists
string getFormString(Cgicc& cgi, char *element) {
  const_form_iterator name = cgi.getElement(element);
  if(name != (*cgi).end() && ! name->isEmpty())
    return (string)(**name);
  else
    return (string)"";
}

int main(int argc, char* argv[])
{
  Cgicc cgi;
  cout << HTTPHTMLHeader();
  
  cout << HTMLDoctype(HTMLDoctype::eStrict) << endl;
  cout << html() << endl;
  cout << head() << title("Results") << head() << endl;
  
  cout << body() << endl;
  cout << h1("Here is the data:") << endl;
  cout << "Hello!" << p() << endl;
  
  try {
  
    Connection connect("phpbase","localhost",
    "phpuser","phppass");
    
    string name = getFormString(cgi, "name");
    string column = getFormString(cgi, "whichname");
    string querystr =
      "select firstname, lastname, email"
      " from cgicustomer where "
      + column
      + " like '%"
      + name
      + "%'";
    
    cout << querystr << "<br>" << endl;
    
    Query query = connect.query();
    query << querystr;
    
    Result res = query.store();
    Row row;
    Result::iterator iter;
    cout << table().set("border=1") << endl;
    for (iter = res.begin(); iter != res.end(); iter++) {
      row = *iter;
      cout << tr() << endl;
      cout << td((const char *)row[0]) << endl;
      cout << td((const char *)row[1]) << endl;
      cout << td((const char *)row[2]) << endl;
      cout << tr() << endl;
    }
    cout << table() << endl;
    
  } 
  catch (BadQuery excep) { 
    // Apache will send cerrs to the error log.
    // I also like to write the error to the browser.
    cerr << "MySQL Generated an error: " << 
    excep.error << endl;
    cout << "MySQL Generated an error: " << 
    excep.error << "<p>";
    return -1;
  }
  
  cout << body() << html() << endl;
  return 0;
}

This program first writes out a header used by the browser; the header looks like this:

Content-Type: text/html

This header tells the browser what type of file it's about to receive; in this case, the browser will receive a text file containing HTML code.

The Connection instance sets up the connection to the database server. The first parameter is the database name, the second parameter is the server name (usually you'll just use localhost), the third parameter is the username, and the fourth parameter is the password.

Next, I construct the SQL query. Notice that I use the data that came right in from the HTML form for the column name (firstname or lastname, stored in the whichname element). That saves me an if statement! I then create a Query instance by calling the Connection object's query function. To actually get my query into the Query instance, I use the << operator:

query << querystr;

To run the query, I call the Query object's store method. This method returns a Result instance, which works much like a Vector in that you iterate through it. With each iteration, you'll get back another row in the result set. (Remember, a SQL statement can return multiple rows!)

Finally, to access the columns in each row, I treat the iterator as an array, like so:

row[0]

This returns the first row of the current column. Alternatively, you can use the column's actual name:

row["firstname"]

I want to put the row data inside an HTML <td> tag because I'm building a table. If you pass a string to a tag function in the cgicc library, that string will appear inside the tags. For example, note this code:

cout << td("hello") << endl;

The code writes:

<td>hello</td>.

Unfortunately, I couldn't send the row data to the td function because the compiler couldn't figure out a good cast. So I manually cast the variable to a const char *, and all was fine:

cout << td((const char *)row[0]) << endl;

Here are some general comments about this code:

  • Everything I print to cout goes to the Web page I'm building. Thus, to print a paragraph tag, I can simply type cout << "<p>" << endl;. However, because I'm using the cgicc library, I instead use its p() function, which does the same thing:

cout << "Hello!" << p() << endl; 
  • I'm using the string class, which is part of the C++ Standard Library, as of the 1998 ANSI C++ standard. This class lets you concatenate strings simply using the + operator. You can see that I used this operator to build the querystr variable. (Make sure that one of the first two items you add is a string instance.)

  • The cgicc library is smart; when you first call body(), you'll get a <body> tag. Then, when you call body() a second time, you'll get a closing </body> tag.

  • I surround my MySQL calls with a try block, and I catch BadQuery exceptions. If I construct a bad query, I do two things: I write the error to the Apache error logs (because I'm using the Apache Web server) by using cerr. But I also like to see my error message in the browser, so I also send it to cout, which writes the error to the HTML.

NOTE

A note to Windows programmers: Make your CGI application a console app, not a Windows app. With a console app, you'll automatically get cout, cerr, and cin. (With a Windows app, you have to open them manually!)

  • + Share This
  • 🔖 Save To Your Account