Locally owned and operated since 1993
Sunday May 18 2008
| Welcome to Islandnet.com Locally owned and operated since 1993 |
| Create an Account • Pricing & Features • Domain Registration • Customer Sites • Help & Info | |
Sunday May 18 2008 | |
| |
|
dbQuerySynopsisPlease note: this is an unsupported legacy gadget.With this gadget you can place databases on-line, and provide full featured searching capabilities to vistors. It supports complex searches that incorporate AND, OR, NOT, and parenthesis to virtually any complexity. Field tests include standard items like "equals" and "less than", as well as less common items like "sounds like" and "contains", etc.
Defining Your DatabaseThe very first thing you need to do is upload the actual database file. This must be an ASCII file, with one record per line, a single newline character marking the end of each line, and a single TAB character between each field value. The records may be of variable overall size, up to 10,000 bytes each. The file can be located in your root directory (where it can't be accessed directly via a web browser), or anywhere within your "www" directory or lower. The name of the file can be anything you want, so long as it ends in a .db extension.Once you have the raw data, you need to create a definition file. This file must have the exact same name as your data file, except that it must end in a .def extension instead of .db. It must also be placed in the same location as the data file. This file describes the database, defining field names and types, field name aliases, and more. The first thing you must have in your definition file is a list of field names and types. Field names are used in queries, and field types define what kinds of tests a query can perform on a field value. Fields can be "Alpha" (textual information), "Numeric" (non-floating point numbers), "Real" (floating point numbers), "Date", or "Logical" (Yes/no, true/false, etc). Let's say we want to build a really simple database of real estate that's for sale. We want to have a price, an address, a features list (fireplace, view, jacuzzi, etc), realtor's name, realtor's phone number, and a picture of the property and it's sold status. The definition file might look like this:
field price numeric field address alpha field features alpha field realtor alpha field number alpha field picture alpha field sold logicalThe order in which your list the fields is very important! The first defined field will correspond to the first, or left-most field in the data file and so on. A single record matching this definition file might look like this:
350000(T)415 Rock St(T)fireplace jacuzzi(T)T. Smith(T)555-8167(T)415rock.gif(T)No(NL)Of course, each (T) is actually a tab character, and (NL) represents the newline character at the end of the record. If a field happens to have an emtpy value, then you'd have two TABs right next to each other - empty fields should not be padded with spaces. NOTE: If you are creating the data file on a DOS or Mac platform, you must ensure that each record is terminated with a newline character and NOT a carriage return! DOS normally terminates lines of text with both a carriage return and a newline. The carriage return would be considered to be part of the last field, which may cause unpredictable results. Once your data and definition files are in place, the rest is all HTML. Based on the sample definition file above, users will be able to perform searches like these:
price < 400000 and features include "jacuzzi" sold and price > 500000 Setting Up for SearchingWhen a user makes a query, the "dbquery" gadget is used to build a temporary index containing those records that match the query. The user is then passed off to the "dbview" gadget which allows the user to browse the matching records one page at a time.Assuming we named our data file "realty.db" and our definition file is "realty.def" (remember they must have the same name), a simple query page that allows people to search our data might look like this:
<form method=post action=/cgi-bin/dbquery> <input type=hidden name=login value=guest> <input type=hidden name=db value=realty> <input type=hidden name=skipdels value=on> <input type=hidden name=header value=realty.hdr> <input type=hidden name=footer value=realty.ftr> <input type=hidden name=template value=realty.tpl> <input type=hidden name=error value=realty.err> <input type=hidden name=window value=20> Query: <input name=query> <input type=submit> </form>NOTE TO SITES WITH VIRTUAL DOMAINS: You must add one extra line that defines your domain name, like this: <input type=hidden name=domain value="www.yourdomain.com"> "db" is set to "realty" because that is the name of the data and definition files. Remember that this is relative to your root directory. If you want the actual files to reside in your "www" directory you'd use a value of "www/realty" instead. If you do, your data and definition files will be directly accessible (and downloadable) to anyone with a browser. "skipdels" indicates whether or not the search engine should include records marked for deletion. The default if you leave out this option is to skip deleted records. This is generally meant for administrative searches where the intention is to delete or undelete certain records. Deleted records do not get removed until a purge is done. Until this time deleted records may be undeleted. Technical note: a record is marked for deletion by adding 128 to the ascii value of the first character in the record. "header" is the name of a file, relative to your "www" directory. This file will form the top portion of the "sandwich" when the search results are displayed. "template" is the name of a file, relative to your "www" directory. This file will be displayed one or more times, forming the middle of the "sandwich". It is displayed once for each matching record that is found. "footer" is the name of a file, relative to your "www" directory. This file will form the bottom portion of the "sandwich". "error" is the name of a file, relative to your "www" directory. This is a special file that will only be displayed if there is an error parsing your search query (like refering to an invalid field name, or having unbalanced parenthesis, etc.) This file can contain anything you want, but anywhere the string {ERRORMSG} appears, the actual error message that caused the problem will be inserted. "window" is a number that specifies the maximum number of matching records that will be displayed on each screen. If your records are large with many fields, you may want to only display one record at a time. If your data is smaller you may want to display 10 or 20 at a time. The template file will be repeated this many times on each page. "query" is the actual search string that is parsed, and then used to search your data file. The header, template, and footer files are actually Template Files, meaning they can contain variables that are replaced on the fly. Several special variables are defined for you to use in any of these three files:
<input name=index value={i}>The values listed above would typically only be used in the header or footer files, although they are available in the template file if you really want them duplicated "window" times. The following values are only useful in the template or middle file:
Once your database and definition files are in place, and you have created
your initial query page, the header, footer, template, and error pages,
you're all set. People can now perform complex searches on your data
and browse the results.
Making QueriesThe query entered by the user must consist of one or more simple tests, joined together with "ands" and "ors". You can use the word "and", "&", "&&", "+", or "," to AND tests together. You can use "or","|", or "||" to OR them together. "Not" or "!" will negate a test. Tests can be grouped with parenthesis to override the default left to right evaluation.Most simple tests are of the form FIELDNAME TEST VALUE. FIELDNAME is simply one of the field names listed in the definition file. VALUE is the value to test the field against. If the value contains spaces, it must be surrounded in quotes. TEST is one of the following:
If a field name and test are missing, the previous field name and test are used. For example, we can search our real estate listings for any properties that have both a fireplace and a jacuzzi with a query like this:
features $ fireplace and jacuzziWhich is functionally identical to this:
features $ fireplace and features $ jacuzziAs well, many english phrases are recognised in place of certain test values. The above example could also have been entered like this:
features refers to fireplace and jacuzziOther english language tests include:
"equals", "is equal to", "is the same as", "is" "sounds like", "sounds similar to" "begins with, "starts with" "ends with", "finishes with" "less than", "fewer than", "prior to" "refers to", "contains", "mentions", "includes"A special field name allows you to perform substring searches on ALL alpha fields. For example, this query could be used to search all fields for a keyword:
* contains "fire" Simplifying Query EntryAllowing a user to enter their own queries directly gives them the most flexibility and provides the most powerful searching capabilities. This is also the most complicated way of doing it, since the user must be aware of how to formulate a query.If a common search would be to find all properties within a given price range, we might just want the user to enter two numbers, then list all the records in that range. Here's how we can do that. Instead of letting them fill in the QUERY value themselves, we change our initial form like this:
<input type=hidden name=query value="price >=
{p1} and price <= {p2}">
Starting Price: <input name=p1>
Ending Price: <input name=p2>
Note that the QUERY value, which is mandatory, is hidden. Instead, the
user is asked to provide the values P1 and P2. The gadget will substitute
the values {p1} and {p2} in the query string before performing the
search.
Making it Easier for the UserSometimes it is nice to be able to use multiple field names to refer to the same field. This can be acomplished in your definition file with aliases. For example, we might add the following lines:
alias price cost alias price value alias address street alias number phoneThese additions now allow users to use the word "cost" or "value" in place of "price" when forming a query. They can also use "street" instead of "address", and "phone" instead of "number". The real field names will also work of course. You may also have a need to replace certain values in a query with other values. For example, if you know that users might use the abbreviation "fp" when looking for a fireplace, and you know that all your records use the full word, you might add a line to your definition file like this:
replace features fp fireplaceWhen a user enters "fp" as a value to test the "features" field against, it will be replaced with "fireplace" before the search begins. Here's another example from a database that has "gender" as a field. In this database, gender is always either "m" or "f". To allow users to use "male, "boy", and "masculine", and "female", "girl", and "feminine", they use these two lines:
replace gender male,boy,masculine m replace gender female,girl,feminine f | |
| Home • About Us • Contact Us • Terms of Use • Privacy Policy • Help Documents |
| Page generation time: 0.02 seconds |