Language Guide

Accessing Databases

You access SQL databases with heitml's <dbquery> command. We'll start here with a simple example and then build on it, with subsequent examples becoming progressively more complex. Along the way, you'll not only develop an appreciation for the usefulness of the <dbquery> command, but you should also come away with ideas to use when addressing specific problems in the applications you develop.

All of these examples will draw information from our "guestbook" database, which you can see if you visit our SQL Form example page.

Extracting FIELD NAMES from an SQL TABLE

Normally, you would expect to know the field names already, assuming that the SQL TABLE or DATABASE resides on your own server. But let's pretend for a moment that you can't remember the fields for a particular TABLE, and you're too lazy (or too engrossed with your current programming assignment) to consult whatever documentation you keep on hand to answer such questions.

So let's use the following <dbquery> to get the information we need:

<dbquery>  SELECT * FROM guestbook 
   <db head> <fname> 
</dbquery>

And here's the result of that query:

Guest_Name Email ADDRESS Country Comment Password

Now let's see if we can separate the field names from each other by surrounding the query with a TABLE BORDER. While we're at it, let's make it a bit easier to read by defining the field names (represented by the <fname> tag) as though they were TABLE HEADINGS. Modifications to the code are shown in red:

<table border bgcolor=ffffff>
   <dbquery>  SELECT * FROM guestbook 
      <db head> <th> <fname> </th> 
   </dbquery>
</table>

And here's our new result:

Guest_Name	Email	ADDRESS	Country	Comment	Password

Of course, now that we know what field names are in the TABLE, we might like to take a look at some of the records.

Displaying records from an SQL database

We could display the records contained within the Guestbook by making another simple modification to our code (also shown in Red).

Note: Because this is only an example, and it's not necessary for us to see every record in the guestbook database at this time, we've limited the srvmax variable to 4. You might want to keep this in mind when developing your own applications, especially if the default value for srvmax on your system is set to a very high number. You wouldn't want heitml to generate an HTML page that listed a million records. That would tie up your Web Server quite a while (not to mention the transmission time to the client's Web Browser).

<let srvmax = 4>
<table border bgcolor=ffffff>
   <dbquery>  SELECT * FROM guestbook 
      <db head> <th> <fname> </th> 
        <db body>  
        <db rowbegin> <tr> 
           <db column> <td> <field> </td> 
        <db rowend> </tr> 
   </dbquery>
</table>

And here's the result (note that the Comment field has been omitted to fit within the confines of the Browser display window):

Guest_Name	Email	ADDRESS	Country
Hermann P. Rapp	rapp@regio-info.de	D-68259 Mannheim	Germany/ Europe
The Giant from Armonk	BigBlue@ibm.com	Armonk, New York	USA
Marc Boschma	marcb@telstra.com.au	Locked Bag 4840. Melbourne, VIC 8100	Australia
Keith Oustalet	oustalet@h-e-i.de	E 7, 14 : 68159 Mannheim	Germany

Even though the srvmax variable has been limited to 4, we can discover the true number of records satisfying a given search condition by using the SQL COUNT() command. Here's how we'd change our code:

<let srvmax = 4>
<table border>
   <dbquery>  SELECT COUNT(*) FROM guestbook 
      <db head> <th> <fname> lt;/th> 
        <db body>  
        <db rowbegin> <tr> 
           <db column> <td> <field> </td> 
        <db rowend> </tr> 
   </dbquery>
</table>

And here's the result:

EXPR
28

One of the niecest things about heitml's <dbquery> command is the control it gives you over how you display the results of your queries. The previous examples in this section showed all records in TABLE format, but you can just as easily create an ordered LIST. Here's a code sample:

<let srvmax = 2>
<ol>
  <dbquery>   SELECT * FROM guestbook 
    <db body>   
      <db rowbegin> 
        <li>
          <table border bgcolor=ffffff>
            <db column>
              <tr> <td> <b> <fname> </b> </td> 
                   <td> <field> </td>
              </tr>
      <db rowend> 
          </table> <p>  
  </dbquery>
</ol>

And here's the resulting output:

1. Guest_Name	Hermann P. Rapp
   Email	rapp@regio-info.de
   ADDRESS	D-68259 Mannheim
   Country	Germany/ Europe
   Comment	Pretty impressive !! heitml will take our website http://www.regio-info.de to a higher level .....
   Password

2. Guest_Name	The Giant from Armonk
   Email	BigBlue@ibm.com
   ADDRESS	Armonk, New York
   Country	USA
   Comment	Nice implementation of SQL here.
   Password

Alternatively, however, we could also control the output from a query by using a more specific syntax within the framework of a <dbquery> Tag, illustrated as follows:

<let srvmax = 4>
<table border bgcolor=ffffff>
  <tr> <th> Name <th>
       <th> Email </th>
    <dbquery q> SELECT * FROM guestbook 
      <dbrow>
         <tr> <td> <? q.Guest_Name> </td>
              <td> <? q.Email> </td>
         </tr>
    </dbquery>
  </tr>
</table>

And the output looks like this:

Name	Email
Hermann P. Rapp	rapp@regio-info.de
The Giant from Armonk	BigBlue@ibm.com
Marc Boschma	marcb@telstra.com.au
Keith Oustalet	oustalet@h-e-i.de

Even though the SELECT * command requested every field from the guestbook, we used the <dbrow> feature of the <dbquery> command to display only a sub-set of those fields.

Creating Hot Links with data obtained from SQL TABLES

Our last example above generated a list of names and Email addresses, but these Email addresses would be far more useful if you could simply click on them and send a message. Because heitml makes it so easy to mix HTML code with your SQL queries, we only have to make one small change to our code to bring the information in our SQL guestbook to life:

<let srvmax = 4>
<table border bgcolor=ffffff>
 <tr> <th> Name <th>
      <th> Email </th>
  <dbquery q> SELECT * FROM guestbook 
   <dbrow>
      <tr> <td> <? q.Guest_Name> </td>
           <td> <a href=mailto:<? q.Email>>
                 <? q.Email> </a> </td> 
         </tr>
    </dbquery>
  </tr>
</table>

And here's the new output:

Name	Email
Hermann P. Rapp	rapp@regio-info.de
The Giant from Armonk	BigBlue@ibm.com
Marc Boschma	marcb@telstra.com.au
Keith Oustalet	oustalet@h-e-i.de

Summary

In this section we started off by showing you how to interrogate an SQL TABLE or DATABASE in order to find out the names of the individual fields. Then we showed how to take those field names and use them in the heading of an HTML <table> that displayed individual records from the DATABASE. We showed you how to control the number of records outputted from your Server, using the <srvmax> variable, as well as how to find out how many records satisfied a given search condition by using the SQL COUNT() command.

We demonstrated the flexibility of heitml's <dbquery> command by showing you alternative ways of displaying results from a search (e.g. creating an ordered list with HTML's <ol> Tag). We also showed how you can restrict the information a user see's, either by specifying field names through the SQL SELECT command, or by using a more specific syntax within the <dbquery> Tag.

And finally, we showed how easily HTML tags can be interleaved with SQL queries by creating hot links to information found within the SQL database.

We have certainly not exhausted the possibilities of heitml's <dbquery> Tag. There are literally thousands of ways to retrieve data using the SQL SELECT command, and the way you present that data is limited only by your imagination. But we've given you some ideas to get you started, and the best way to become proficient with <dbquery> is to make your own modifications to the above examples and see how they work.