CODEWITHSUNDEEP
phpmysql
Chris Carter
Chris Carter

Reputation: 25

Find string in different word order in MySQL query

This might be a pretty basic question but I am hoping someone will be able to help please?

Lets say I have a MySQL table with a field called exampleString which is a text field

In one record, the field exampleString contains the following text :

"apples oranges pears"

Now, what I want to be able to do (PHP) is the following :

  1. Perform a query to search for "apples oranges pears" - I can do that, no problem.

  2. Perform a query to search for "oranges apples pears" and it will return the record with the string "apples oranges pears" in it - I'm not sure how to do that. (Note: If the query search string was just "oranges pears" it shouldn't return the record).

I would also like to be able to make the query case-insensitive and i'm not sure how to do that either (although I haven't searched that yet).

Upvotes: 0

Views: 280

Answers (1)

Xenofexs
Xenofexs

Reputation: 511

You can use PHP for loop for each word you have in your input and make your SQL query.

In MySQL, you can use LIKE "%[your_string]%" for search a string content [your_string] http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

So, if you need match 'apples oranges pears' and 'apples pears oranges' you need to do something like : 

LIKE "%apples%" AND LIKE "%oranges%" AND LIKE "%pears%"

So you find the string content apples AND oranges AND pears with no order importance

In php now, the explode function transform a string in array

So, example of final code (adapt clean and secure this for you case. Test your vars etc)

$string = 'apples oranges pears';

$explode = explode(' ', $string);

$whereString = '';
foreach ($explode as $key => $value) {
    $whereString .= ' AND [your_field] LIKE "%'.$value.'%"';
}

echo $whereString;

Upvotes: 1

Related Questions