Making a php query that filters words in a MySQL column

I'm new to both php and mySQL, so forgive me if this is a simple or common question, but I looked all over and couldn't find an answer.

Basically, I'm building a quick search for a real estate page where someone can type in an address and the search results will bring it up (and similar results if they type in something broad like "90th street")

Right now my query looks like this. ($QuickSearch is the value they typed in.)

$query   =  "SELECT * FROM MLSSales WHERE (ID = '$QuickSearch') or (concat_ws(' ',StreetNum, Stre开发者_运维百科etName) like '%$QuickSearch%');

Now this search works well for me, but the problem is that some of the listings in the StreetName column contain the words "West" or "East" or something like that. If someone searches something like "West 90th Street", "300 West 90th Street", "300 West 90th", ect. my search works. But if they type something like "300 90th Street" I won't get results.

How can I make the search slightly more lenient without making it so lenient that it will bring in bad results.

What you'd need is a fulltext search, or manipulate your search text to make it more wildcarded.

Your query boils down to

... like '%300 90th Street'

which only works if those 3 words are exactly next to each other with exactly one space between them.

A Full text search would treat it more as

... like '%300%' or like '%90th%' or like '%Street%'

(as a very hacky example). If you can't use a FULLTEXT index on this table, then you can try to do some of this pre-processing yourself to turn each word in the search phrase into its own little 'like' clause. Performance will suck, because `%...%' clauses can't use indexes, but would allow arbitrary matches.

I would strongly advocate against writing your own full text search engine using "WHERE column1 LIKE '%blah%'" expressions. You will have all sorts of issues with word stemming, misspellings, etc.

MySQL has one already. It's the one using MATCH() syntax. Looks like you want only the precise matches. MySQL supports that - you can filter out the records that have a low match score. See this page of the manual for the details: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

Example:

CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> ) ENGINE=MyISAM;

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root'
    -> IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root'
    -> IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+