Mysql WHERE column is in string

Question

I hope, this is not a very stupid question, but since some hours, I'm looking for a solution, but without success.

I have a table with name of cities:

cities.name

Berlin
New York
Hamburg
...

And I have a string: "Bonn New York Chicago"

Now, I would like to pick all "known" cities from this string. But as you can see, I'm not able to split this string to an array (because of "New York"). So "FIND_IN_SET" is not suitable for me. The function "LIKE" is working in the "wrong direction".

So I would need something like %cities.name% EKIL $string :-D

Does anyone have an idea, how I can handle this problem without using PHP?

I hope, I'm not missing the forest for the trees...

Thanks a lot for your help!

Tobias

Answer 1

You need to make one cities array and then check your string against your cities array.

$to_match = array('Bonn','New York','Chicago'); // all cities array

$str = "Bonn New York Chicago"; // Your city string
$new_array = array();

foreach($to_match as $value) {
  if(stristr($str, $value)) {
    $new_array[] = $value;
  }
}

print_r($new_array); // here you can get only match cities against all cities array

This will iterate over each array element, then check if the value of match exists in $str, Then it will add it to $new_array.