Product Database.

Enterprise editions of TechCart come with a complete product database backend that runs on top of any MySQL database (Referred to as WAMY [Web Access to MYsql]) .  The database backend works hand in hand interacting with the database, the website and the TechScript scripting language. 

WAMY was created to be extremely flexible and to meet the needs of virtually all database situations e-commerce based or not.   As many e-commerce stores require very specific and special fields, WAMY has been designed to operate with any field structure, and combined with the TechScript scripting language WAMY provides one of the most flexible catalog backends in existence.

WAMY accesses the database using variables passed through URLs or form posts. Output is based on a series of templates. This very open ended approach to database access software provides a single set of utilities capable of handling a variety of databases for different web based applications.

Head
The value of HEAD should specify the header file of the page to be displayed. If you would like to specify a path remember that the path is relative to the CGI script.
In a URL: script.pl?head=../templates/header.html

Foot
The value of HEAD should specify the header file of the page to be displayed. If you would like to specify a path remember that the path is relative to the CGI script.
In a URL: script.pl?foot=../templates/footer.html

Template
The template field specifies that file that will display the results returned from the database. If there are multiple results to be returned this file will be repeated for each result returned. Again, if you would like to specify a pathname it is relative to the CGI script.

Table
The TABLE field is always required. This field specifies what table from the database to use in the query.

Search
The SEARCH field contents can vary depending on the action or the searchtype specified. If you are performing a ‘SQL’ SEARCHTYPE the actual SQL query will be contained in this field. If you are performing one of the built in keyword searches the keywords for the search are contained here in the SEARCH field.

Max:
The MAX field tells the script how many results to display per page. If this is not set it defaults to 10. If you wish to display only one record this should be set to 1.   If there are more records returned than the max field specifies, Next Page/Last Page buttons will appear at the end of the first set of results.  

wbuttons
By default WAMY will display next page/lastpage buttons once it reaches the max results per page set by the MAX tag. If you would not like wamy to display these buttons (particularly useful if the results are being displayed in a form. Then you may specify wbuttons=n and the buttons will not be displayed.

wmissing
If no results are returned from a search WAMY will display the no results page. By default this is html/missing.html. If you would like to specify a different page you can do so with the wmissing tag. This should be a relative path/filename.

Wnobr
By default wamy converts all line breaks into <BR> for viewing on html page. This can be especially irritating on an edit page. By specifying wnobr=y wamy will not convert the line breaks.

Wtest
This will display the SQL query it is sending or attempting to send to the database. Great for diagnostics.

Sort
This should be followed by the fieldname of which you would like to sort by. If you wanted to sort by the field called state you would specify in your form sort=state. By default the sort is ascending, to sort in descending order you would specify sort=state DESC, or in a url like &sort=state%20DESC.

Fields
During certain search queries this field is used to specify which fields in the table to search in. If there is more than one field to use in the search, the field names can be separated with a comma (,) or a space.

Action
The ACTION field tells the script how to handle the form input. The allowed values for ACTION are below.  Action is used only for edit/delete and add record functions.

Action = Add
If ACTION contains the word ‘add’ a new record will be added to the database. The form that posts the information to be added should be setup so that the form field names match that of the table field names. For example if you have a simple mailing list database and the addressee name fields are called ‘fname’ and ‘lname’ the field names in your form should be ‘fname’ and ‘lname’. The script searches for any fields in your form that match fields in the database and adds the new record accordingly.

The ‘bogus’ field must be included in this form.

Action = Edit
To edit a record simply create a form where the field names match that of the database fields names.

All fields in the database must be present in the form as hidden fields or editable fields.

It is best to have the edit page generated from a prior search. This way you can insert <!—fieldname--> in the values of the form fields for easy editing.

The ‘bogus’ hidden field as well as the ‘page’ field must be included in this form.

Action = Delete
To instruct the script to delete a record the ACTION field must be set to ‘DELETE’. To specify what record to delete, you need to what you want to delete. For example, if you are dealing with a simple mailing list database and you want to delete all occurrences of ‘John’ in the ‘fname’ field you would specify your form fieldname as ‘fname’ and the form field value of that as ‘John’.

Just like in the ACTION=ADD function, the script will search for any fields in your form that are name the same as the fields in the database table you are editing.

Let’s use the previous example and make it a little more complex. Let’s say you only want to delete occurrences of all records where the ‘fname’ field is ‘John’ and the ‘state’ field is ‘OH’. You would specify both fields and desired values in the posting form. The query would then be formulated like:

DELETE FROM TABLE WHERE fname=’John’ AND state=’OH’;

If you want to delete records with the occurrence of ‘Oak Street’ in the ‘Address’ field, (keeping in mind that ‘Oak Street’ is typically only a portion of the field value) you can use a wildcard method in the same way as explained above except the value of ‘Address’ in your form should read ‘%Oak Street%’. % serves as the wild card indicator.

The ‘bogus’ hidden field as well as the ‘page’ field must be included in this function.

Bogus
This field is used to pull field names from the database. This field can be set to any valid field name in the table being queried. This field is only required on action=add, action=edit and action=delete functions.

Bogus is used to perform a fake prequery of the database before the actual query is performed. This prequery allows WAMY to pull a list of the field names from the table so that the script can match them with the fields in the posting form.

Searchtype
This field specifies the type of keyword search to perform.

The SEARCH form field should then contain the keywords to search separated by a space or a comma (,). The form field called ‘FIELD’ is also required. ‘FIELD’’s value should contain a list of the database fields to use in your search. This list should be delimited by a comma (,) or a space.

The current keyword searches that are available are:

Keywordfuzzy
This sort of search breaks apart keywords and searches each keyword individually using wildcards thus generating a query like:

Keywords : house boat

SELECT * from table WHERE field LIKE ‘%house%’ or field LIKE ‘%boat%’;

keywordexact
This sort of search breaks apart keywords and searches each keyword individually not using wildcards thus generating a query like:

Keywords : house boat

SELECT * from table WHERE field LIKE ‘house’ or field LIKE ‘boat’;

sql
If you specify this type of search the Q form field should be a true SQL query. (i.e.: SELECT FROM table WHERE field like ‘%what%’;)

fieldmatch
This sort of search does not break apart the keywords, and looks for an exact match in the specified field.

Keywords : house boat

SELECT * from table WHERE field = ‘house boat’;

In this case, if field contained ’red house boat’ no results would be returned because it was searching for only records where the field = ‘house boat’

All
This display all records in the table.

The PAGE form field is used when adding, editing, or deleting records in a database. PAGE will specify what URL to take the website visitor when the specified action has been completed.

The wsendmail field is used if you would like to send a confirmation email for add/edit and delete actions. If specified this field should contain an email template for the confirmation. This template works the same as all other WAMY templates. But should always start in the following syntax:

To: NAME <email>

From: NAME <email>

Subject: xxxxxxxx

Wrequired
If you would like certain fields marked as required during edits or additions you will want to create a hidden field named WREQUIRED the value of this should list all fields you wish to have validated.

This can be a list of form fields separated by a comma.

Example: wrequired="name, address, address2, city, state, zip""

Wemail
If you would like an email address checked for validity during edits or additions you will want to create a hidden field named WEMAIL the value of this should list all email fields you wish to have validated.

This can be a list of form fields separated by a comma.

Example: wemail="email, friend_email"

Templates:
There are three types of templates that the script(s) utilize any time they display a page. The filename of these templates are specified in the URL/FORM FIELDS.

HEAD and FOOT files are regular HTML files. They serve as page header and footer files.

The file that shows the search results from any query is specified using the TEMPLATE field. Whatever file you specify here can display any or all of the fields returned from the search query. If there are more than 1 results returned then this page will repeat. In order to display the results from any given database field you simply insert simple HTML tags where you would like the data displayed. The tags are formulated like:

<!—field-->

So if the field you wish to display the data for is called ‘name’ your tag would look like:

<!—name-->

You can put these tags anywhere in the template file and repeat them as many times as you want. You can also use these tags to formulate links or hidden form fields. You can also manipulate the output using the TechScript Scripting Language. All three templates are parsed through TechScript.

All information for queries and screen layout are passed through the url or hidden form fields. In order to generate any search results a template page must exist and be specified. The following outline some basic usage examples. We have put these on several lines for page formatting, please note that these are full urls.

I would like to display all of my products from my database table called PRODUCTS using the template called mytemplate.html:

http://www.domain.com/cgi-bin/cart/wamy.cgi?
table=products&searchtype=all&template=mytemplate.html

I would like to display all of my products from my database table called PRODUCTS using the template called mytemplate.html, however I don't like the default of 10 per page I would like to only display 5 per page:

http://www.domain.com/cgi-bin/cart/wamy.cgi?
table=products&searchtype=all&template=mytemplate.html
&max=5

I would like to display all of my products from my database table called PRODUCTS using the template called mytemplate.html, I only want 5 items per page and I would like to add a header called header.html and a footer called footer.html:

http://www.domain.com/cgi-bin/cart/wamy.cgi?
table=products&searchtype=all&template=mytemplate.html
&max=5&head=header.html&foot=footer.html

My product database uses a field called xindex as the primary key.  I would like to only display one product using my template called fullpage.html, the products xindex value is 123:

http://www.domain.com/cgi-bin/cart/wamy.cgi?
table=products&searchtype=fieldmatch
&template=fullpage.html&fields=xindex&search=123

Back To Contents