Querying SQL Tables by Keith Oustalet

Low-level Database Access

heitml provides a number of Database Application Components with hi-level tools for database access, and you will normally want to use them in developing your Web Pages rather than the methods discussed here. Therefore, the information in this last portion of our introduction to SQL Databases is provided merely to complete the foundation or "reference" material you need in order to understand the more advanced concepts explained in subsequent sections. Nearly all of the Tags in the heitml Database Libraries are built upon low-level primitives such as we dbupdate (which we've seen in previous pages) and dbquery (the subject of this page). Therefore, it is to your advantage to have at least a basic understanding of how they work.

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.

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 <dbhead> <? SrvFname> </dbquery>

And here's the result of that query:

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() Function) as though they were TABLE HEADINGS. Modifications to the code are shown in red:

<table border bgcolor="white"> <dbquery> SELECT * FROM guestbook <dbhead> <th> <? SrvFname> </th> </dbquery> </table>

And here's our new result:

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

<let SrvMax = 4> <table border bgcolor="white"> <dbquery> SELECT * FROM guestbook <dbhead> <th> <? SrvFname> </th> <dbbody> <dbrow> <tr> <dbcolumn> <td> <? SrvField> </td> <dbrowend> </tr> </dbquery> </table>

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

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() function. Here's how we'd change our code:

<let SrvMax = 4> <table border> <dbquery> SELECT COUNT(*) FROM guestbook <dbhead> <th> <? SrvFname> lt;/th> <dbbody> <dbrow> <tr> <dbcolumn> <td> <? SrvField> </td> <dbrowend> </tr> </dbquery> </table>

And here's the result:

One of the nicest things about heitml's dbquery tag 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 <dbbody> <dbrow> <li> <table border bgcolor="white"> <dbcolumn> <tr> <td> <b> <? SrvFname> </b> </td> <td> <? SrvField> </td> </tr> <dbrowend> </table> <p> </dbquery> </ol>

And here's the resulting output:

Before we leave this section on Accessing Databases we should note that each of the preceding code examples assumed that you wanted to show all the fields belonging to a given query (as defined in the SELECT * command). You can, of course, limit the fields returned by a query simply by specifying the fields you want to see (e.g. SELECT Guest_Name, Email FROM guestbook).

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="white"> <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:

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="white"> <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:

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