Re: Search by "AND"....

Like I keep saying - my searching class does boolean AND's - it is possible to do this and have it highly optimized and return in one string. http://www.miester.org/software/miester_search_v0.1.tar.gz --Joe On Wed, Dec 13, 2000 at 10:52:47AM -0800, Matthew Ballard wrote: > 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 > > > -- > 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