Searching all databases

[vasilek]

I am building a web site with about 20 FM databases in the backend. I'd like to put one search field on every web page, similar to Amazon.com. Is there a way to search for all the values in all fields in all databases? In other words, to perform a search of the entire web site. Thanks

[Anatoli]

It cannot be done in simple manner and if it could be done, it will be painfully slow.

How do you know Amazon is doing that this complicated way across many databases and all fields?

Could you tell us more? Why so many fields and why so many databases. It is probably question of improving database model.

[vasilek]

I didn't mean that Amazon is doing it this particular manner. In fact, they not using FM in their online store at all. But somehow you can search for products just by entering a keyword or a phrase just into one field.

I have several categories of products and each category is a separate FM database (due to specificity of the solution they cannot be combined into a one database). I'd like to search for products by entering a keyword or a phrase (product name, a word from the description of a product, etc.) into one search field on the web page.

Is it doable? Thanks.

[Anatoli]

It should be in single database. Category is just attribute. How many records are in all databases?

Just theoretic chance is to search through all 20 databases in reply page. That will be always much slower, than search in single database.

[Garry Claridge]

I would look at using [FMP-InlineAction] tags.

All the best.

Garry

[vasilek]

Yeah, but how do you search multiple fields in the database from one field of the web form? I have: Name, Description, Features, Price, etc.

[Unable]

Like Garry says, try FMP-InlineAction. Though they can be excriciatingly slow on some machines.

"...but how do you search multiple fields in the database from one field of the web form? I have: Name, Description, Features, Price, etc."

Well, you are the developer and have a more intimate knowledge of your mutliple databases, but I think it would behoove you to consider a complete db redesign or perhaps a database solution which fields are calculations of the fields of the other databases, but which would afford you some common uniqueness or you might consider looking at the various fields upon which you would want to structure your searches to develop some common uniqueness such that a search can be greatly simplified by searching a single field And is your concern really searching on multiple fields or merely displaying data from multiple fields?

More directly regarding, "... how do you search multiple fields...", I know I have posted at least one example on the Sample Files forum which searches two fields with a form action. Find it. Study it. Good luck.

[vasilek]

The databases structure redesign is not an option. It is complete and working perfectly. The search field is an extra convenient feature we'd like to add for the users. It has to search for products just by entering any keyword or a phrase. One product is a database record. This record has several fields with info about this product, such as Title, Overview, Specifications, etc.

For example: the search keywords 'Panasonic' should return a list of all Panasonic products in different categories; or '2.4 kHz' should return all products with '2.4 kHz' string in Specifications field, etc.

I do not know, I just trying to put a global search field for the web site, like every normal online store has. (Examples: amazon.com, staples.com, jandr.com, compusa.com, circuitcity.com... you name it). Searching multiple fields or concatenate several fields into one... Whatever the better solution.

Is it a hard to do? I am familiar with CDML and Lasso.

[Anatoli]

RE: The databases structure redesign is not an option. It is complete and working perfectly.

I will say it isn't in your case.

I believe none of the mentioned companies have 20 databases for searching.

But since you know Lasso, it is much easier with LassoScript and Inlines, which can do the search easy in anything you will program in it.

[Garry Claridge]

You can use "-lop=or" (or <input type="hidden" name="-lop" value="or">) to achieve this. For example:

[FMP-InlineAction: -db=mydb01.fp5, -lay=mylayout, -lop=or, name={CurrentToken}, Description={CurrentToken}, Features={CurrentToken}, price={CurrentToken}, -find]

Results Here

[/FMP-InlineAction]

[FMP-InlineAction: -db=mydb02.fp5, -lay=mylayout, -lop=or, name={CurrentToken}, Description={CurrentToken}, Features={CurrentToken}, price={CurrentToken}, -find]

Results Here

[/FMP-InlineAction]

In this example I have assumed that you have assigned the search value to a Token. You can also use a combination of "-op" values before each field.

All the best.

Garry

[vasilek]

You are probably misunderstanding my task if you're questioning the design of our solution. But I still appreciate your input into this. Thank you very much. Your helped us a lot with other posts on this forum.

[vasilek]

You're absolutely right. I will be passing the value of the field in the -token. Since you suggested to use Inlines, I pictured the same idea of using them as you draw here. Thanks for help.

[Anatoli]

I've already said it will be possible in Lasso and with Inlines etc. It will be slow. IMHO, Lasso will execute that faster, than WebCompanion, but it will be slow.

Separate database for group of articles is bad design if you like it or not. The only exception is database with 10 millions records or database approaching 2GB file limit.

Much better will be to use 20 databases in Product relation system and not the same 20 databases with different group of products.

To manage that design will be from difficult to nightmarish.

[vasilek]

What is IMHO? We have only a couple of databases with products. The rest are for the website support. And those products are very different by nature. We already had all products in one database. Different fields were used with different categories and only a small number of them was matching. So we split this one database into several. I know, it may sound strange, and I myself also would recommend to everyone to keep all their products in one place, but our case is one of those different ones. So now we need to search in several databases. I think by passing the -token and then using Inlines on the response page will help in some way.

[Unable]

IMO = in my opinion.

The H could be "honest", "humble", possibly evenly "humorous".

"You are probably misunderstanding my task if you're questioning the design of our solution."

"...a web site with about 20 FM databases..."

"We have only a couple of databases with products. The rest are for the website support."

Wow, 18 db files for "website support". I get the feeling that you are misunderstanding your task.

[vasilek]

Do not judge if you do not all the aspects of the task. You're looking at it only from one side I just opened in this thread.

BTW, I like your username.