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:

Guest_Name Email address COUNTRY GUEST_COMMENT GUEST_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() 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:

Guest_Name Email address COUNTRY GUEST_COMMENT GUEST_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="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):

Guest_Name Email address COUNTRY
MichaelJah michaelcizt@mail.ru http://www.seo-page1.com/ Malta
KewiinDeelp kewiinerype@mail.ru https://goo.gl/3f2ujk Italy
XiromantSt vu.l.kan.is2.017@gmail.com Россия
JustSisters just.sisters.de@yandex.com http://just-sisters.de 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() 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:

EXPRESSION1
17

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:

  1. Guest_Name MichaelJah
    Email michaelcizt@mail.ru
    address http://www.seo-page1.com/
    COUNTRY Malta
    GUEST_COMMENT
    GUEST_PASSWORD

  2. Guest_Name KewiinDeelp
    Email kewiinerype@mail.ru
    address https://goo.gl/3f2ujk
    COUNTRY Italy
    GUEST_COMMENT Здравствуйте , отличный сайт для заработка https://goo.gl/3f2ujk <a href=https://goo.gl/3f2ujk>Перейти на сайт</a>
    GUEST_PASSWORD

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:

Name Email
MichaelJah michaelcizt@mail.ru
KewiinDeelp kewiinerype@mail.ru
XiromantSt vu.l.kan.is2.017@gmail.com
JustSisters just.sisters.de@yandex.com

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:

Name Email
MichaelJah michaelcizt@mail.ru
KewiinDeelp kewiinerype@mail.ru
XiromantSt vu.l.kan.is2.017@gmail.com
JustSisters just.sisters.de@yandex.com

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.


This page was dynamically generated by the web application development tool RADpage of H.E.I. See the new demo "Was ist AJAX? " (in German). In Germany H.E.I. provides Webdesign in Mannheim and Web Programming (Programmierung).
© 1996-2017 H.E.I. All Rights Reserved.



Homepage
Intro/Features
Component Guide
Programming
  Language Guide
    Modular Pages
    Dynamic Pages
    Interactive Pages
      Creating FORMs
      Session Mode
      Search FORMs
      Scrolling Pages
      Email FORMs
      SQL Intro
        Defining Tables
        Updating Tables
        Querying Tables
  Language Ref.
  Component Ref.
  Class Library
  User Components
  Tryout Form
  Tutorial
  New Features
  heitml 1
User Guide
FAQ
Mailinglist
Discussion Group
Services
Pricing/Register
Download
Frame
 
Contact
 
 
 
Search: