Re: Search by "AND"....

If any particular word in the word column can only be associated with one itemID (so that if you had some word, say happy in the DB, there couldn't be two instances of happy associated to one itemID), you could count the number of itemID, like so select count(itemID) as numitemID from table1,table2 where table1.wordID=table2.wordID and (word like '%wordA%' or word like '%wordB%'); and then loop through the results looking for any results equal to 2. If my first condition isn't true, then you would have to do the query for one word, then loop through the results checking for the existence of the second word in relation to any of the itemID's, ie: select itemID from table1,table2 where table1.wordID=table2.wordID and word like '%wordA%'; and then loop through those results, then in the loop: select itemID from table1,table2 where table1.itemID=$previousqueryitemid and table1.wordID=table2.wordID and word like '%wordB%' and store the itemID in an array for any itemID that returns a row in the second query, then loop through the array. I don't believe there is a simpler way in PHP/MySQL (specifically MySQL), because I think you would need a database that support sub-selects, which MySQL doesn't support (which is the reason for the second looped query). Matthew At 01:27 AM 12/14/2000 +0800, you wrote:

No i'm not going to find a word with contains both wordA and wordB.. I want to find an itemID that has both wordA AND wordB.. Create table item ( itemID ... .... ) CREATE TABLE word ( wordID int(10) unsigned NOT NULL auto_increment, word varchar(100) DEFAULT '' NOT NULL, PRIMARY KEY (wordID), UNIQUE word (word) ); CREATE TABLE word_item ( wordID int(10) unsigned DEFAULT '0' NOT NULL, itemID int(10) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (wordID,jobID) ); Any help? Thanks. Matthew Ballard wrote: Are you sure that at least one of the word records in table2 contain both wordA and wordB (or whatever they may be), and that there is a corresponding wordID in table1? Try: select wordID from table2 where word like '%wordA%' and word like '%wordB%'; and see if it returns any records, then try with the relational part of the query. I tried an almost identical query, just with different tables, and it worked just fine. That is the most logical way I can think of to do what you want (assuming you do want both words to be part of word). Matthew At 03:13 PM 12/13/2000 +0800, you wrote:

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 -- 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