CODEWITHSUNDEEP
phpmysqlsql-like
JohnF
JohnF

Reputation: 167

Compare string against MySQL column

Say I have the following scenario:

$string1 = 'This is my string with the city New York in';
$string2 = 'This is my string with the city Los Angeles in';

MySQL-DB:

id|city
 0|San Diego
 1|Memphis
 2|Los Angeles
 3|Budapest
 4|New York

How should I do to take the whole strings - without splitting it - and check if any of the values in city is occurring in any of the strings?

I got lots of string with more info in than stated above.

Upvotes: 1

Views: 3396

Answers (5)

zerkms
zerkms

Reputation: 254886

WHERE $str1 LIKE CONCAT('%', city, '%') OR $str2 LIKE CONCAT(...) ...

ps: keep in mind $str1 should be properly escaped or used as a prepared statement parameter.

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26333

In MySQL you can do something like this:

SELECT id, city
FROM myTable
WHERE INSTR('This is my string with the city New York in', city) > 0

Something like this will find the names even if they're part of a word, so it will find Nome in "The nomenclature of Sao Paulo". Bad example, but the best I could think of: Nome matches because it's found in nomenclature. To avoid these types of matches a regular expression is needed:

SELECT id, city
FROM myTable
WHERE 'This is my string with the city New York in'
      RLIKE CONCAT('(^|[^[:alpha:]])', city, '($|[^[:alpha:]])')

The regex (after concatenation) would read, for example:

(^|[^[:alpha:]])New York($|[^[:alpha:]])

... which means: Match the beginning of the string or a non-letter character, then the value "New York", then the end of the string or a non-letter character. In short, it will only find whole words.

Upvotes: 1

jr3
jr3

Reputation: 915

If you are doing it in PHP.

One way would be to query the cities into an array. Then iterate through the array using strpos to see if the cities are contained in your string e.g.

foreach($city in arrayOfCities)
{
  if(strpos($string1, $city) !== false)
  {
    //city found in string do something
  }
}

This may not be the exact code but strpos will work: http://www.php.net/manual/en/function.strpos.php

Upvotes: 1

user557846
user557846

Reputation:

i would try 2 things and see which is the fasets\ works best based on what eve else im doing

  1. get all the cities implode() in to a pipe (|) separated list and use that in a preg match which i loop against the strings.

  2. feed the cities from a db in a while loop and use strpos() to check for there existence in the string.

Upvotes: 0

Nick Humrich
Nick Humrich

Reputation: 15755

You can use regular expressions in both php and mysql.

For mySQL you can use the REGEXP operator

In php you can use preg_match

The regex would be

".*" + city + ".*"

Upvotes: 0

Related Questions