PHP & MySql match a Word/Phrase in a String

Question

i have a Table in MySql with Cities.

|---------------------|------------------|
|          ID         |     City         |
|---------------------|------------------|
|          1          |     Berlin       |
|---------------------|------------------|
|          2          |     Los Angeles  |
|---------------------|------------------|
|          3          |     Shanghai     |
|---------------------|------------------|

and i have a String in Php like this: $string = "I like Berlin and Los Angeles."

Now i want look in the MySql-Database if the String contains a City/Cities. But i dont know what is the best solution. Maybe its better or/and faster to have the List with Cities as Json File?

Take a array from your database , meanwhile explode your string sentence may be by " " , put them in a loop & check if you find any words in common . — Vikrant
– Vikrant, Commented Jan 18, 2017 at 9:10

Nishant Nair · Accepted Answer · 2017-01-19 04:56:32Z

3

You an use mysql query for checking it.

Select * from table_name where match(City) against ('I like Berlin and Los Angeles')

or you can use like

Select * from table_name where city like '%I like Berlin and Los Angeles%'

I am modified the query as you need the sort mechanism in it.

Select *,(match(City) against ('I like Los Angeles  and Berlin')) as relevance  from city 
where match(City) against ('I like Los Angeles and  Berlin') ORDER BY relevance ASC;

It would give you sort on city present in table.

edited Jan 19, 2017 at 4:56

answered Jan 18, 2017 at 9:13

Nishant Nair

1,9971 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Hieu Vo Over a year ago

the column which we want to use match function must be a FULLTEXT index

Dennis Over a year ago

@Nishtant Your second answer ist interesting! I never had the idea to check the complete String as a like function! And the City-Field must be a FULLTEXT index?

Dennis Over a year ago

The "against" method is working fine but one little problem. how can i get the results in the order, how it is in the string? in my example it should be 1. berlin und 2. los angeles.

Nishant Nair Over a year ago

@Dennis I have modified the query and tried on my console too. Its working and request you to have a Full text Index on city column. it helps for searching faster result.

Dennis Over a year ago

@Nishtant First, thanks for your great help! What i exactly need is, that the results of the mysql should be in sort how the the cities are in the string. "I like Berlin and Los Angeles" >> the result must be 1. berlin and 2. los angeles. the background is, i m working on a flight search and the the first city is the destination and the second the origin. is this possible with mysql?

|

Manh Nguyen · Accepted Answer · 2017-01-18 09:29:41Z

0

You should take all cities from database to an array then checking it.

$cities = ['Berlin', 'Los Angeles', 'Shanghai'];
$string = 'I like Berlin and Los Angeles.';
$result = [];
$pattern = '/('.implode('|', $cities).')/';
preg_match_all($pattern, $string, $result);

SAMPLE: http://phpio.net/s/2t70

answered Jan 18, 2017 at 9:29

Manh Nguyen

9405 silver badges12 bronze badges

1 Comment

Dennis Over a year ago

That works nice! How can i do this with a multidimensional array?

Collectives™ on Stack Overflow

PHP & MySql match a Word/Phrase in a String

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related