Re: Search by "AND"....

This is how Miester Search does it - it makes a dictionary of unique words, indexes the record against the dictionary by counting instances of a word in each record. A more advanced scoring method is in the works as is the ability to index records on the fly. --Joe On Wed, Dec 13, 2000 at 10:25:15AM -0800, Robert V. Zwink wrote: > How about counting the number of times the record contains the words, then > use > > HAVING score >= ".count($keywords) > > The only problem is that you cannot have redundant words in you word index > file. > > This is what a similar query looks like that I use: > > SELECT product.id, > product.short_sku, > product.coordinate_group, > product.retail, > product.inventory_location, > product.deck , > count(search_table.word) AS score > FROM product, search_table > WHERE product.id = search_table.qid > AND (search_table.word IN('DISKETTES','COOL' )) > GROUP BY search_table.qid HAVING score >= 2 > ORDER BY score DESC LIMIT 0, 25 > > If this looks like what you are looking for, but doesn't make sense, let me > know and I'll try to explain further. > > Robert Zwink > http://zwink.levitate.org > > > > > > > -----Original Message----- > From: Koo [mailto:koo@sun-scope.com] > Sent: Tuesday, December 12, 2000 11:14 PM > To: php-general@lists.php.net > Subject: [PHP] Search by "AND".... > > > I got two typical MySQL tables for fulltext search: > > table1 table2 > ------ ------ > wordID wordID > itemID word > > where "word" holds all words > ------------------------------------------------------ > When I try to query the database for any items with > EITHER wordA OR wordB, I do the following: > > mysql> select itemID from table1,table2 > where table1.wordID=table2.wordID > and (word like '%wordA%' or word like '%wordB%'); > > It works. > > But when I want query for any items which contain BOTH > wordA AND wordB, the following query always outputs 0 rows. > > mysql> select itemID from table1,table2 > where table1.wordID=table2.wordID > and (word like '%wordA%' and word like '%wordB%'); > > Why? And what's the simplest way to accomplish my task? > > Many thanks. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net --- Joe Stump PHP Programmer www.Care2.com