Searching - Multiple Databases (Relational) Searching - Multiple Databases (Relational)
WebCatalog can search multiple databases as easily as it searches a single database. Often you will want to look up information in a second database while displaying the results of a search. If the relationship is very simple (an exact match of text in one database field to the text of another database field) then you may use the faster [Lookup] tag. If the relationship is more complex, then you may need the more sophisticated [Search] context.
[Lookup] Example: Let's say you maintain a list of names and addresses, and you want to display the fully-spelled-out name of the state that a person lives in, but you only store the 2-letter code in the address database. Create a database that lists all 50 states by their 2-letter code, and put the fully-spelled state name into another field in that database:
-------States.db------- Abbrev FullName CA California AZ Arizona FL Florida ------------------------ -------Results.tpl----- [FoundItems] [Name], [Address], [City] [Lookup db=States.db&lookInField=Abbrev&value=[State]&returnField=FullName] [/FoundItems] ------------------------
Notice that the lookup looks throuh the States.db database for an exact match of the [State] field information that comes from the first database -- it then returns the text from the [FullName] field of the corresponding state name stored in States.db.
[Search] Example: Let's say you store all the orders created by your store in 2 databases. One of the databases stores the order# and Name/Address/Account information, while another database stores each individual SKU of the line items purchased during an order
-------Orders.db------- OrderNumber Name Address City State Zip 1234 Grant 667 Corte San Marcos CA 90031 1395 John 12 Elias San Diego CA 90004 ------------------------ ------LineItems.db------ OrderNumber SKU Qty Price 1234 Shoe1 1 12.95 1234 Shirt1 2 15.95 1234 Service 1 99.95 1395 Gift1 1 9.95 1395 Box12 3 74.95 ------------------------
To display a list of all the orders with associated line items, your search results template might look like the following:
-------Results.tpl----- [FoundItems] [OrderNumber], [Name], [Address], [City] [Search db=LineItems.db&eqOrderNumberdata=[OrderNumber]] [FoundItems] [SKU], [Qty], [Price] [/FoundItems] [/Search] [/FoundItems] ------------------------ ----Output from above---- 1234, Grant, 667 Corte, San Marcos Shoe1 1 12.95 Shirt1 2 15.95 Service 1 99.95 1395, John, 12 Elias, San Diego Gift1 1 9.95 Box12 3 74.95 -------------------------
Notice that the interior [Search] looks for all matching line items that have the same order number as the outer Orders.db database. This collects and displays all line items for that order underneath the proper header information.