Using MySQL To Search For Any Word In Any Order
As I haven’t posted a tutorial in a while, sorry about that, I decided I would show you all how to change a standard LIKE MySQL query into a query that will find all words in the query in any order.
Normally you would have a MySQL query that looks like this:
|
1 |
SELECT column FROM table WHERE column LIKE '%word1 word2%' |
This would only find word1 and word2 in that order. If you want to change this you need to make a few changes to the PHP code that sorts your query.
When you get your query from your search, do whatever SQL injection prevention you need to and then take your query and run it through explode like this:
|
1 |
$words = explode(' ', $searchquery); |
This will return an array of your search words separated into an array by the spaces. Now you just need to create your MySQL query:
|
1 2 3 4 5 6 7 8 9 |
$sql = "SELECT column FROM table WHERE"; $sql_end = ''; foreach($words as $word) { $sql_end .= " AND column LIKE '%{$word}%'"; } $sql_end = substr($sql_end, 4); $sql = $sql.$sql_end; $result = $mysqli->query($sql); //continue |
That will then make a query that looks like this (assuming there are 2 words):
|
1 |
SELECT column from table WHERE column LIKE '%word1%' AND column LIKE '%word2%' |
That will then find the words in any order. I would imagine, although I haven’t tried this, you could make the words optional by using the OR operator, this will then return the results if only some of the results are found where AND requires then all to be found or it will not return a result.
I hope this little tutorial has been some use to you guys & gals out there. Also I’ll be doing some screen cast style tutorials in the near future, so look out for them.
12 Comments
Kishore
can you please provide me the entire script of the above example
Paul Robinson
Well the rest of the script is just a standard MySQLi setup.
Sanitization is completely dependant on the data being received. If it is just string data though you could run it through some REGEX like this:
That will replace all characters that aren’t alphanumeric or spaces. You would do that before the explode splits the query up.
lmiguel
Oh thanks!… I was trying to using regex but your solution is just simply and easy! Thanks a lot!
Shahid Sandhu
Great help, thanks alot
Peter
Works great. Thanks for the post!
jim
Thanks for this sql query. Help me a lot!
Alex
Isn’t there a little typo at line 07?
It’s:
But it should be:
That is, there are two single quotes to close the command, instead of a single double quote.
Am I wrong?
Paul Robinson
Hi Alex,
Nicely spotted. I’ve corrected it now. Thanks for pointing it out.
Pilgrim
My technique 😉
$operator = “AND”; // you can easy handle better search options
$input = array(‘earth’, ‘magrathea’, ‘vogsphere’); // some array with data
$output = array(); // output (tmp) array
// create simply conditions in MySQL without operator
foreach($input as $in) {
$output[] = “column LIKE ”%{$in}%”;
}
// finaly create complete query using implode function with operator
$sql = “SELECT column FROM table WHERE ” . implode(” {$operator} “, $output);
unset($input, $output); // don’t forget to delete data that you will not require anymore
Timo Huovinen
If you have huge tables with lots of words and all the words must match in any order then I think that a much less performance intensive way would be to sort the words in the string in a specific order and then search using that string, you could even hash the ordered words string (and store the same hash for every row) and search using that
Paul Robinson
Hi,
I’m not sure I understand, the idea on this (very old) tutorial was to be able to search for all of the keywords but not in a specific order. Just their existence in the content to be searched would return a result.
Regardless I would actually advise using MySQL’s native Full Text search, where possible, now. It is pretty fast, accurate & easy(-ish) to use. Failing that, if you need complex searching, I would consider Sphinx, but that is towards the more advanced end of things.
Codean.Do
Thank You very mush. I used it to create a search in WordPress Database outside WordPress without alter table with full text modes.