RE: [PHP] Search by "AND"....

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