Advanced Querying... Similar to MySQL 'LIKE' Func?

[RicherD]

Hi all,

A client has requested a 'intelligent' search feature for their website which has me slightly stumped.

The theory is this: Customer enters product code in search box, the web site returns all results with matching characters in consequtive order... without necessarily being the same string.

So, 'DFG100' would return: DGF-100, DFGQ100, DFG1100 but not DGF100

I've been thinking of performing a 'findall' request then sending the search term to the results page as a token, then displaying the valid hits using a pattern count if statement / inline action... although i'm not sure this is feasable / efficient.

Anyone out there with experience of this problem? I've been looking for some kind of plugin / code but not had much luck...

Any advice greatfully received, thanks

[Vaughan]

You'd need to make a table of all the logic involved in the search: once this is done the rest would be easy.

Not knowing your business model, the example you posted above makes no sense to me -- I'd have never guessed that DGF-100 is OK but DGF100 is not. It's that "sense" that you have to capture and program-in to make the search work. And it's the tough part, particularly of there are a lot of variations.

Once you have the rules worked out you could create a calculation field based on the product code that creates all the different variations paragraph delimited. Then get the user to search in this calculation field.

[Garry Claridge]

It is doable! However, you would need to be able to state the search rules.

For example, with "DGF100" we can assume that this contains two elements with at least one character between them. The elements are either split by letters and numbers, or split in half.

You may be able to use some Javascript to prepare the actual search criteria.

All the best.

Garry

p.s. The "LIKE" operator in SQL is a "Pattern Match" search. Similar wildcards can be used with FM.

[RicherD]

Thanks for the responses guys

Looking back it seems i got my example muddled up (it was late ) it should have read :

So, 'DFG100' would return: DFG-100, DFGQ100, DFG1100 but not DGF100

The basic rule is to find results with matching characters in consecutive order.

Using a calculation to create all possible combinations seems a good idea, i will definately try this. The product codes are around 6-8 characters in length, with a minimum search length of 3 chars there will be a lot of combinations to create!

[RicherD]

Hmmm, thinking more about your post Garry, i think a simple javascript could be answer.

Inserting a '*' before and after each character on the form should do the trick...

Ie. 'DFG100' would become '*D*F*G*1*0*0*'

That would find the matching records.

Think i may have it cracked now, thanks again guys

[Unable]

If I were a betting person, I would wager that '*D*F*G*1*0*0' will not work. It will probably find no records!

The asterisk (*) is a search symbol for one or more characters. (See Status Bar > Symbols in the db file.) It is entered as part of a search string in a CDML bw or ew search.

You might try performing the search directly in the db file before you waste time writing code which is likely to not do what you desire.

[Anatoli]

K*l*e*i*n will find Klein, but not Kleinwort in FM.

K*l*e*i*n* or Klein will find Klein and Kleinwort as well.

[Garry Claridge]

The SQL statement could look like this:

SELECT * FROM mytable WHERE myfield LIKE "DFG_100" ORDER BY myfield

The CDML equivalent would be:

-db=mytable&-format=mypage.html&myfield=DFG@100&-sortfield=myfield&-find

To construct the string "DFG@100" would be relatively easy with Javascript.

All the best.

Garry

p.s. The CDML URL may need some refining, however that is the basic statement.

[RicherD]

Good job you're *not* a betting person then Unable

I've got it working as desired using a simple javascript hack. The visitor enters the model number in a form input field, which is then processed via javascript on submit to include '*' before and after each character. A hidden field called 'model' (the database field) is then allocated the new search string, and the form submitted (thus hiding the '*'s from the end user). Tested in ie / opera / mac ie / safari and works seamlessly... so fingers crossed.

Thanks for all the suggestions guys

<script type="text/javascript" language="JavaScript">

<!--

function SearchParse() {

var CharArray = new Array();

var text = document.forms[0].input.value

var newtext = "*";

var stringlen = text.length

if (stringlen < 3) {alert('Please enter at least three search characters!');return false;}

for (var i = 0; i < stringlen; i++ ) {

CharArray = text.charAt(i);

}

for (var i = 0; i < stringlen; i++ ) {

newtext = newtext + CharArray + "*";

}

document.forms[0].Model.value = newtext;

document.forms[0].submit();

}

-->

</script>

<form action="FMPro" method="post" name="Search" onSubmit="SearchParse();">

<input type="hidden" name="-find" value="">

<input type="hidden" name="-format" value="search.html">

<input type="hidden" name="-lay" value="web">

<input type="hidden" name="-db" value="database.fp5">

<input type="hidden" name="-max" value="20">

<input type="hidden" name="model" value="" border="0">

<input type="text" name="input" value="Model No">

<input type="button" value="Go!" onClick="SearchParse()">

</form>

[Unable]

re: Good job you're *not* a betting person then Unable

Oh?

Does your string with the JS enter '*D*F*G*1*0*0*' (your proposed code which you posted #82600 - 09/26/03 04:02 AM, and upon which I based my comment) into the db file field.

Or does your successful string just maybe, kinda, sorta enter something just a wee, tiny, miniscule, little, nano bit different like 'D*F*G*1*0*0*' ?

Pete Rose I'm not. Are you?

[Garry Claridge]

The displayed Javascript puts an "*" at the start of the search string.

All the best.

Garry

[Unable]

re: puts an "*" at the start of the search string

Interesting.

I tested this in 5.0v.3 by directly entering '*D*F*G*1*0*0', which returned no records. I then entered 'D*F*G*1*0*0*' which did return the desired records.

[Garry Claridge]

A problem with the "*" is that it requires one or more characters. If you didn't have any entries with at least one character before the "D" then nothing will be found.

All the best.

Garry

p.s. I wonder of this is only true if it is placed at the beginning of a string. I will check it some more!

[Unable]

I tested again, and I sit corrected, since '*D*F*G*1*0*0*' and '*D*F*G*1*0*0' now returned records. I have no idea why my earlier test returned no records.

In light of the new bit of information, "Please enter at least three search characters!",

I then tried '*D*F*G'. Oh my!

And if someone enters these three characters '*1*0*0'. Now that is something else.

[Garry Claridge]

Re my: problem with the "*" is that it requires one or more characters

Is not correct. It can be zero or more characters as stated in the CDML Reference.

My tests in another thread where I believed a bug existed with "*", in that it required at least one character was erroneous.

One thing to note is that I find the search works better, for me, with an "Exact Match" (==).

All the best.

Garry

[RicherD]

Hi Guys,

The search term submitted does have an asterix at the start, ie. *D*F*G*1*0*0*

You'll see it set in the javascript when the variable is declared : var newtext = "*";

I hadn't thought about the leading character issue, I can see the logic where Filemaker *could* consider it invalid... but yes, it seems to work just fine.

Thanks again for the responses, you've been most helpful

[Unable]

re: but yes, it seems to work just fine

But RicherD, when I do a (direct entry on the db file) search entering just the three characters as *D*F*G (FMPro5.0v.3), it finds no records. Your search criteria allows for that search. Have you tested that?

FWIW I also got no records entering *DFG.

[Unable]

Oops. I guess I left-off the * at the end. *D*F*G* works (the same results as using DFG*).

[Unable]

As I read through this thread it became apparant that no testing was performed on a CDML solution for the stated problem.

With that in mind I now have examined this problem with my copy of the Sample File demo CDML -Op values and Symbols. I created six new records with the four strings of RicherD's post (DGF-100, DFGQ100, DFG1100, and DGF100) and two additional records (34DFG100 and 12-DFG1100) entered respectively in the field "last". I then ran the solution through a browser.

Using the operator "begins with" in combination with the character string DFG returned the same results (2 records) as the JavaScript solution of RicherD. (And similar results searching DGF.)

Based on this very simple test, and without knowing the complexity of all the item string-formats of RicherD's solution, it may be this can be handled by CDML's -op tag assigned the value "begins with". Testing across all of the records would surely resolve that. If so, the only JS necessary for the solution would be to confirm that a min. three (or whatever) character string was entered by the client.

In this instance I would suggest the less JS to be loaded and processed in the solution, the more efficient the solution. The db file is capable and will be used doing the real data processing even with the JS.

[RicherD]

Hi Unable,

I've duplicated my database and loaded it with your samaple data above, and ran the same queries as you.

Searching for DFG i get :

Your query returned 4 models.

DFG1100

34DFG100

12-DFG1100

DFG-100

Searching for DGF i get :

Your query returned 2 models.

DGF2100

DGF100

Plus, adding a extra record "DXXGXXF100" and searching for DGF :

Your query returned 3 models.

DGF2100

DGF100

DXXGXXF100

I can't replicate those last results via CDML without using javascript, but i'd be very interested in a purely-filemaker solution. I'm using Filemaker 6 which may be the difference, if you're interested i will PM you a URL to my dev machine and you can try it yourself.

All the best

[Unable]

Re: I've duplicated my database and loaded it with your samaple data above, and ran the same queries as you.

Was that with the JS or the -op value="bw"? Curious because it could be a difference between 5.x and 6.0.

I just created a two records DXXGXXF100 and DXXFXXG100in 5.0v.3. Checked it thru the browser using begins with/DFG and then DGF and still got but 2 records found with each request. Using contains with those entries the first entry returned 4 records the latter returned 2 records. Neither found the DXX...

Again, this is using the demonstration from the Sample Files forum. I think you can open the db file in 6 if you are interested. Just add new records in the db file and enter the strings in "last".

I would not be able to view your site till much later tonite or manana. But of course I'm interested.