Re: PHP/MySQL Search Engine Query Question

>I am currently working on a website that is implemented using PHP and MySQL. > >The site currently has a simple search engine that allows a shopper to type >in a search string that is stored in $search. For example, if a shopper >types in 1972 Ford Mustang >$string ="1972 Ford Mustang" > >Using the following SQL statement: >SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% > >Records are returned that have this exact string and in this exact order >(I'm aware a wild card character is included on the front and back of the >string). > >My desire is to be able to logically AND each token of the search together >independent or the order of the tokens. >I want to return all records that have Mustang AND 1972 AND Ford. > >Since a shopper inputs the search string in advance I don't know how many >tokens will be used. How about using OR and a ranked sorting so that the more words that match, the better? <?php if (!isset($start)){ $start = 0; # Or is it 1? MySQL/PostgreSQL do it differently. Grrrr. } $words = explode(' ', $search); $query = "select whatever, 0 "; while (list(,$word) = each($words)){ if (trim($word)){ $query .= " + whatevercolumn LIKE '%$word%' "; } } $query .= " as score "; $query .= " from sometable "; $query .= " where score > 0 "; $query .= " order by score desc "; $query .= " limit $start, $limit "; ?> Since LIKE returns TRUE/FALSE, when you "add them up" they turn into 1/0, and you get a one-point score for each matching word. -- Like Music? http://l-i-e.com/artists.htm I'm looking for a PRO QUALITY two-input sound card supported by Linux (any major distro). Need to record live events (mixed already) to stereo CD-quality. Soundcard Recommendations? Software to handle the recording? Don't need fancy mixer stuff. Zero (0) post-production time. Just raw PCM/WAV/AIFF 16+ bit, 44.1KHz, Stereo audio-to-disk.