Searching with keywords in VFP

[stepen123]

Is it possible to search with keywords in any order? For example a field contains the keywords 'journey earth centre', another record has a field with 'centre journey earth'. Is it possible to find both records by searching for 'earth journey centre'?
If yes, how should the query be made? What are the rules for the field containing the keywords? Where can I find more info on this issue?

 

[Olaf Doschke]

You will need to do three searches and then intersect the results to find the rows havin all three words in them. This is kind of a full text search not being optimizable.

Create Cursor curTest (ID I, cText C(60))
Insert into curTest Values (1,"Journey To The Center Of The Earth")
Insert into curTest Values (2,"Journey To The Center Of London")
Insert into curTest Values (3,"Center Of The Earth Isn't Worth A Journey")

Select * from curTest t1;
 where lower(cText) like '%journey%' ;
   and lower(cText) like '%earth%' ;
   and lower(cText) like '%center%'

So to process any search you'll need to generate specific SQL queries and execute them via macrosubstitution or Execscript. And the first step to this would be to determine Wordcount() of the search term and then process each word via GetWordNumber().

You will have one where clause part for each word in the search term and then contcatenate them with 'AND' in between.

Bye, Olaf.

 

[MrDataGuy]

stepen123,
OlafDoschke gave a perfectly fine answer, but with VFP there are normally many ways
to do anything. Here are a few more options for you.

Create Cursor curTest (ID I, cText C(60))
Insert into curTest Values (1,"Journey To The Center Of The Earth")
Insert into curTest Values (2,"Journey To The Center Of London")
Insert into curTest Values (3,"Center Of The Earth Isn't Worth A Journey")


&&-- Another SQL option
	SELECT * from curTest;
	  WHERE [journey] $ lower(cText);
	    AND [earth]   $ lower(cText);
	    AND [center]  $ lower(cText)
    
&&-- A simple Browse option    
	BROWSE FOR;
	        [journey] $ lower(cText);
	    AND [earth]   $ lower(cText);
	    AND [center]  $ lower(cText)
    
&&-- If you only needed to find one (
&&-- (Thou you can use "Contine" to find more
	LOCATE FOR;
	        [journey] $ lower(cText);
	    AND [earth]   $ lower(cText);
	    AND [center]  $ lower(cText)   

&&-- Looping the needed records
	SCAN for;
	        [journey] $ lower(cText);
	    AND [earth]   $ lower(cText);
	    AND [center]  $ lower(cText)   

	
		&&-- Other needed code
		
	ENDSCAN

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[MrDataGuy]

BTW we should of asked. How big is the field being searched? ( e.g. 100 characters, 200 characters, a Memo?), how big is the table, 100 records, 1 million, 500 megs etc. How many records are being searched for ( normally 1, or 100). These details can change how we might suggested programming the searches.

What about near matchs? e.g. would finding "the center of London" record example be a good match. Why I ask is a number of years ago Mike Lewis (He hangs arond this board) did a FoxPro advisor article on the subject, and I later did a follow up to it on how to make it (the search code) faster. It was for finding matches in a movie database when people did not know the exact spelling or name of a movie they where looking for. e.g. "Dancing while raining" might find "Signing in the rain", thou it could have been the 5th out of 20 suggestions.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Olaf Doschke]

Good point about the size of the database. If it's for a mass data, other approaches and much more preprocessing of the data are recommended, eg you can extract single words into a seperate table and link to records containing these words. This enables you to optimze searches, which cannot be optimized using LIKE or $.

You can offer a ranking of relevance, eg through sorting by the number of matching words found.

If you want all words to be found you can start the search with the most seldom word (which you can find out in an extracted word list, if you also store the number of occurrances) and continue searching in that intermediate result.

Lots of possibilities.

Bye, Olaf.

 

[stepen123]

Many thanks to your replies.

I am trying to analyse text. The database is not big, say 10 thousand records. So far I search only in Char(100) fields, which contain keywords in a different order. My goal is to find all records, containing the searched keywords. However the task will get complicated if the number of keywords in the field exceeds the limitations for a Char field and I have to use a memo (or some kind of encoding).

Another problem is that the number of keywords is not limited to 3 only.

My question was a mater of principle. I think this case is easily achieved by an SQL query under PHP, so I was interested if the implementation of SQL in VFP has a similar built-in functionality.

I'm not a programmer and am looking for an easy solution by VFP, which in the end may turn out to be the most difficult way. If you know of a different DBMS or programming language, which have this functionality, please tell me.

 

[MrDataGuy]

looking for an easy solution by VFP"
IMO Olaf and I gave several possible easy solutions.

"If you know of a different DBMS or programming language, which have this functionality, please tell me"

I suspect that almost any will allow you to do what you want in one way or another.

Now a 'complex' solution might be to have 3 tables. One with the main information. A 2nd table that is just 2 columns: Primary Key and KeyWord. The 3rd table would be a "linking table" PK, Foreign Key to the main table, Foreign Key to the keyword table.

This way a main record can have almost unlimited keywords, the SQL query would be fast, nice table normalization, side effect of easy counting of the number of time a keyword is used in the main table etc.

But the above design may well be overkill for your needs.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Mike Lewis]

Stephen,

I'm not sure if this will help, but have you considered using an existing free-text searching tool? What I have in mind is something that will index the individual words within the text, and let you search for any combination of words or phrases.

If that's the sort of thing that might meet your needs, take a look at the FoxWeb search engine (

http://www.foxweb.com/fwFullText).

It's reasonably easy to integrate with your existing FoxPro code, and is free.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

It would just be fair, if you simply refer to the PHP function (or is it rather MySQL?) enabling you to easily do such keyword searches. Then we might show you something similar in VFP. Otherwise, why not use PHP or MySQL then? PHP is available in mayn forms as a CGI plugin of a webserver, but also processes PHP code directly passed to a php.dll or php.exe. Also you can use MySQL data from VFP over the MySQL ODBC driver.

Bye, Olaf.

 

[Olaf Doschke]

Here's my finding of how such a keyword search is proposed in PHP/MySQL:

http://www.plus2net.com/sql_tutorial/search-keyword.php

It's not just a sample SQL, but it's working with the same schema: Split the search into keywords and for each keyword generate a whereclause "fieldname LIKE '%keyword%'".

I already mentioned you can generate an SQL string an execute it in VFP and you can make use of GetWordnum() instead of PHP split(). As simple as a loop from 1 to GetWordCount(), retreiving GetWordnum(counter). Otherwise use ALINES() and set a space as the 'line' separator to get n array of words.

The size of the data will be ok for VFP to give fast results. VFP string function actually are quite fast, see here:

http://www.code-magazine.com/article.aspx?quickid=0001081&page=2

Bye, Olaf.