Using MySQL To Search For Any Word In Any Order

/ PHP / by Paul Robinson / 12 Comments
This post was published back on March 11, 2010 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

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:

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:

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:

That will then make a query that looks like this (assuming there are 2 words):

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

Author’s gravatar

can you please provide me the entire script of the above example

Reply
Author’s gravatar author

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.

Author’s gravatar

Oh thanks!… I was trying to using regex but your solution is just simply and easy! Thanks a lot!

Reply
Author’s gravatar

Thanks for this sql query. Help me a lot!

Reply
Author’s gravatar

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?

Reply
Author’s gravatar author

Hi Alex,

Nicely spotted. I’ve corrected it now. Thanks for pointing it out.

Author’s gravatar

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

Reply
Author’s gravatar

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

Reply
Author’s gravatar author

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.

Author’s gravatar

Thank You very mush. I used it to create a search in WordPress Database outside WordPress without alter table with full text modes.

Reply
Older Comments
Newer Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I'll keep your WordPress site up-to-date and working to its best.

Find out more