Link | fulltext |
Author | Johan Solve |
Category | Database |
Version | 6.x |
License | Public Domain |
Posted | 17 Jan 2006 |
Updated | 06 Sep 2006 |
More by this author... |
This tag emulates FileMaker's convenient fulltext search behavior with MySQL. It searches for words beginning with the words in the query, appearing in any order, with either AND or OR in one or multiple fields, including support for "phrase searches", using either ldml or sql inlines. However it does not take advantage of any MySQL index so it's best suited for databases that are not too large (some 10,000 records work fine).
Search for dog cat will find caterpillar in a doghouse but not scattered dogs. Also supports phrases in search request such as "black dog" which will find black dog or black doghouse but not black spotted dog
The tag works with Lasso 6 and later.
Version 1.0.1 2004-02-24 Removed ` around SQL field names
Version 1.0 2004-02-12
normal search with default AND operator between search words, to use in ldml inline (default) inline: -database..., (fulltext: 'fieldname'='search string'), -search; search with OR operator between search words: inline: -database..., (fulltext: 'fieldname'='search string', -or), -search; make the search span multiple fields as if they were one concatenated field inline: -database..., (fulltext: (array: 'fieldname1', 'fieldname2', ...)='search string'), -search; use fulltext in SQL inline inline: -database..., -sql='SELECT * FROM table WHERE ' + (fulltext: 'fieldname'='search string', -sql), -search; Test form:
Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.
(get: 1) -> name), 'temp_searchstring'=(params -> (get: 1) -> value); local: 'fieldname'=#temp_fieldname, 'searchstring'=#temp_searchstring, 'thisfield'=''; if: #fieldname -> type != 'array'; #fieldname = (array: #fieldname); /if; // Set search mode -- wordbeginswith is more expensive. By setting this to false we will search // for words anywhere in the field, not just in the beginning of words. Set it to false for best performance. // Set it to true for best user experience. local: 'wordbeginswith'=true; // Define word separators to be able to handle wordbeginswith searches. // The more word separators, the more expensive search. // For better performance, reduce this array. local: 'wordseparators'=(array: ' ', '.', ',', ':', '-', '"', '\'', '(', '/', '&', '+', '\r', '\n', '[', ']', '<', '>', '{', '}'); if: params >> '-or'; local: 'operator'='OR'; else; local: 'operator'='AND'; /if; if: params >> '-sql'; local: 'usesql'=true; else; local: 'usesql'=false; /if; // HANDLE PHRASES // extract phrases from search string local: 'phrases'=(string_findregexp: #searchstring, -find='"(.*?)"'); // remove phrases from search string #searchstring = (string_replaceregexp: #searchstring, -find='".*?"', -replace=''); // clean up remaining search string to make it strictly word-space-word etc // replace any sequence of non-word characters with a single space #searchstring = (string_replaceregexp: #searchstring, -find='[\\s,.()&/!%-]+', -replace=' '); // make the remaining search string into an array, word by word (separated by space) #searchstring = #searchstring -> split: ' '; // add the phrases if: (#phrases -> type) == 'array' && (#phrases -> size) >1; loop: -loopfrom=2, -loopto=(#phrases -> size), -loopincrement=2; //element 2, 4, 6 etc in the array are the phrases we want #searchstring -> (insert: #phrases -> (get: loop_count)); /loop; /if; // clear blank words, step backwards through array to avoid tripping over collapsing array loop: -loopfrom=(#searchstring -> size), -loopto=1, -loopincrement = (-1); if: (#searchstring -> (get: loop_count)) == ''; #searchstring -> (remove: loop_count); /if; /loop; if: #usesql; local: 'output'=(string), 'searchword_sql'=(string), 'separator_sql'=(string); if: #wordbeginswith; iterate: #searchstring, (local: 'searchword'); #searchword_sql=(encode_sql: #searchword); // escape MySQL wildcard characters #searchword_sql = (string_replace: (string_replace: #searchword_sql, -find='%', -replace='\\%'), -find='_', -replace='\\_'); #output += ' ('; iterate: #fieldname, #thisfield; #output += ' (' + #thisfield + ' LIKE "' + #searchword_sql + '%" '; iterate: #wordseparators, (local: 'separator'); #separator_sql=(encode_sql: #separator); #output += ' OR ' + #thisfield + ' LIKE "%' + #separator_sql + #searchword_sql + '%" '; /iterate; #output += ') OR '; /iterate; #output -> removetrailing: ' OR '; #output += ') ' + #operator; /iterate; else; iterate: #searchstring, (local: 'searchword'); #searchword_sql=(encode_sql: #searchword); // escape MySQL wildcard characters #searchword_sql = (string_replace: (string_replace: #searchword_sql, -find='%', -replace='\\%'), -find='_', -replace='\\_'); #output += ' ('; iterate: #fieldname, #thisfield; #output += '' + #thisfield + ' LIKE "%' + #searchword_sql + '%" OR '; /iterate; #output -> removetrailing: ' OR '; #output += ') '; #output += #operator; /iterate; /if; #output -> removetrailing: #operator; else; // ldml inline -- default local: 'output'=(array); #output -> (insert: '-opbegin'=#operator); if: #wordbeginswith; iterate: #searchstring, (local: 'searchword'); #output -> (insert: '-opbegin'='or'); iterate: #fieldname, #thisfield; #output -> (insert: '-op'='bw'); #output -> (insert: #thisfield=#searchword); iterate: #wordseparators, (local: 'separator'); #output -> (insert: '-op'='cn'); #output -> (insert: #thisfield=#separator + #searchword); /iterate; /iterate; #output -> (insert: '-opend'='or'); /iterate; else; iterate: #searchstring, (local: 'searchword'); if: #fieldname -> size > 1; #output -> (insert: '-opbegin'='or'); /if; iterate: #fieldname, #thisfield; #output -> (insert: '-op'='cn'); #output -> (insert: #thisfield=#searchword); /iterate; if: #fieldname -> size > 1; #output -> (insert: '-opend'='or'); /if; /iterate; /if; #output -> (insert: '-opend'=#operator); /if; return: #output; /define_tag; ?>
No comments
©LassoSoft Inc 2015 | Web Development by Treefrog Inc | Privacy | Legal terms and Shipping | Contact LassoSoft