You can of course use the "query" parameter of the "database" command to do anything you want with the database.  In fact, sending SQL query to the database engine is the only way to access the database for many online programming languages.  While BEE is certainly capable of doing it with "query", it did not stop there.

Eventually BEE will need to send a "query" to achieve the database operation.  However, BEE provides a middle layer interface to the programmers so that they don't even need to know that it is SQL in the underlying platform.

A note to the following examples: The phbook%query is a variable coming out as a "by-product" of the database operation.  It was the query the "database" command sent to the underlying platform.  If you do not want the query to be sent automatically, you can include an '!' in front of the action value (e.g. action="!select").  In this case, you will still get the query in phbook%query but there is no query sent.  This is handy in debugging.  (The following examples show the query instead of displaying the result.  If you want to see the record set, please append the example with the "foreach" loop from the previous section.)

Let's set up a simple search:

var phbook%table = "PhoneBook";

var phbook%match:ID = "equal:ID";

var phbook%match:Name = "regexp:Name";

var phbook%match:FromHeight = "min:Height";

var phbook%match:ToHeight = "max:Height";

Then search for all records where Height is 100 or above:

var phbook%search:FromHeight = 100;

database "phbook" action="select";

display "{phbook%query}";

// Output: select * from PhoneBook where Height >= '100'

Search for all records where Height is 200 or below (assumed that phbook%search was cleared without the previous settings):

var phbook%search:ToHeight = 200;

database "phbook" action="select";

display "{phbook%query}";

// Output: select * from PhoneBook where Height <= '200'

Search for all records where Name starts with "Jo":

var phbook%search:Name = "Jo*";

database "phbook" action="select";

display "{phbook%query}";

// Output: select * from PhoneBook where Name rlike "^Jo.*$"

Search for all records where Name starts with "Jo" and Height from 100 to 200 inclusively:

var phbook%search:FromHeight = 100;

var phbook%search:ToHeight = 200;

var phbook%search:Name = "Jo*";

database "phbook" action="select";

display "{phbook%query}";

// Output: select * from PhoneBook where Height >= '100' and Height <= '200'

//    and Name rlike "^Jo.*$"

In real life, we simply do

var phbook%search = "(var)sys%form";

database "phbook" action="select";

That will set up whatever the visitor entered to the search form (in the previous page run).  The operation that these two BEE statements do is equivalent to looping through the form field list, building the SQL based on all the matching criteria and sending the query to the database engine.  You probably can imagine how to do the same in other languages.

Now, let's put the whole thing together with the search and display:

<table>

  <tr>

    <td><b>ID</b></td>

    <td><b>Name</b></td>

    <td><b>Tel</b></td>

    <td><b>Height</b></td>

    <td><b>Birthday</b></td>

  </tr>

<script language="bee">

var phbook%table = "PhoneBook";

var phbook%decode:Birthday = "strftime:%d/%m/%Y";

var phbook%match:ID = "equal:ID";

var phbook%match:Name = "regexp:Name";

var phbook%match:FromHeight = "min:Height";

var phbook%match:ToHeight = "max:Height";

var phbook%search = "(var)sys%form";

database "phbook" action="select";

foreach ((db)phbook as rec) {

</script>

  <tr>

    <td>${rec:ID}</td>

    <td>${rec:Name}</td>

    <td>${rec:Tel}</td>

    <td>${rec:Height}</td>

    <td>${rec:Birthday}</td>

  </tr>

<script language="bee">

}

</script>

</table>