Search and order by multiple MYSQL requests

I am trying to create a search query kind of thing for my website, and I know I can use the easy '%string%' to get something with a similar result, but that means you must type in the exact order of words of the article/string you were trying to find.

For example, If you type "One Three Two", you can only find articles that have the "One Three Two" order anywhere in the string... and it can't search for something worded like: "One Two Three" etc.

So I thought of a solutio开发者_Go百科n but I haven't thought of how to actually work out the solution... this is my thought-up procedure:

1. explode() the string into all the words typed into it
2. mysql query every single word one by one using the %like%
3. if there are articles with all of the words, it will show first, and so it's sorted by most occurrences of the same article.

that's it! so I am just wondering if that is even achievable using PHP? or do I have to go back down to Perl or something?

Sure it is. True expressions in MySQL are 1, so just add them together and order by that.

mysql> select ('ABC' LIKE '%B%') + ('ABC' LIKE '%C%');
+-----------------------------------------+
| ('ABC' LIKE '%B%') + ('ABC' LIKE '%C%') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
1 row in set (0.02 sec)

And just use OR in your WHERE clause to get any articles that match.

SELECT ...
  WHERE 'ABC' LIKE '%B%' OR 'ABC' LIKE '%C%'

You might also want to consider full text search. It is much much harder to setup but can make your search seem much more like a search engine. Can be very valuable for finding extra results that have related keywords. Also, it should handle the explode of the phrase (all three words together) internally, you just pass it what the user gives:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Create the query such that it it's a union of the various searches, that follows your idea to get the most matching in front:

example for your 3 words

 select 1 as levl, text from table where text like '%w1%' or text like '%w2%' or text like '%w3%'
union
select 2 as levl, text from table where text like '%w1%' or text like '%w2%' 
union
select 2 as levl, text from table where text like '%w2%' or text like '%w3%' 
union
select 2 as levl, text from table where text like '%w1%' or text like '%w3%' 
union
select 3 as levl, text from table where text like '%w1%'  
union
select 3 as levl, text from table where text like '%w2%'  
union
select 3 as levl, text from table where text like '%w3%' 
order by levl

Note that parts of a word are retrieved as well. If you want only whole words, parse each row in the result in php to eliminate the incorrect ones. Sql will the main selector, you do the refinery.