使用LIKE将数据从一个表匹配到另一个表

I'm wondering you can match data from one table to another (example below)

我想知道你可以匹配从一个表到另一个表的数据(例如下面的例子)

What if I wanted to link my second table to the first one and add the id of the city to my headline.

如果我想将第二个表格链接到第一个表格并将城市的ID添加到我的标题中该怎么办?

I guess I would need to something like:

我想我需要这样的东西:

 SELECT * FROM headlines where headline LIKE %$city%

And then do an insert everything time I got something returned. But then I would need to repeat my query for the hundred of cities I have every time I add a new headline.

然后插入所有返回的东西。但是,每当我添加新标题时,我都需要重复查询我所拥有的数百个城市。

    First Table (Cities) | Second Table (Headlines)
    ______________________________________________________
    id: 1 name: New-York | Something happened in New-York
    id: 2 name: LA       | Something else happened in LA
                         | Somehting happened in LA and New-York

Can anybody point me to whatever I should be looking into to do this ?

任何人都能指出我应该做的事情吗?

2 个解决方案

#1

You would use a join clause with a like condition:

您将使用具有类似条件的join子句:

 SELECT h.*, c.cities
 FROM headlines h join
      cities c
      on h.headline like concat('%', c.city, '%')

The key is getting the wildcard parameters in the pattern, using concat().

关键是使用concat()获取模式中的通配符参数。

By the way, this will do a nest-loop query, which will not perform very well. Because of the like condition, indexes will not help improve performance.

顺便说一句,这将执行一个嵌套循环查询,这将不会很好地执行。由于类似条件,索引无助于提高性能。

A better approach is to build a full text index on the headline column and use match for the comparison. This will also help prevent conflicts like "London" matching "Londonderry".

更好的方法是在标题列上构建全文索引,并使用匹配进行比较。这也有助于防止“伦敦”与“伦敦德里”相匹配的冲突。

And, in your example LA is particularly problematic, because many words contain LA. You could try something like:

而且,在你的例子中,洛杉矶特别成问题,因为很多单词包含洛杉矶。你可以尝试类似的东西:

      on concat(' ', replace(replace(replace(h.headline, ',', ' '), ':', ' '), '.', ' '), ' ') like
               concat('% ', c.city, ' %')

That is, replace punctuation with spaces, add a space at the beginning and end of the headline, and then look for the city name surrounded by spaces. This prevents a match between "LA" and "AtLAnta" (caps only for emphasis).

也就是说,用空格替换标点符号,在标题的开头和结尾添加空格,然后查找由空格包围的城市名称。这可以防止“LA”和“AtLAnta”之间的匹配(仅限于强调)。

#2

you can try this

你可以试试这个

$sql = "SELECT * FROM cities";
$res_mysql=mysql_query($sql);  
   while($arr=mysql_fetch_array($res_mysql)){
      while(mysql_fetch_array($res_mysql_like)){
          $update= "UPDATE `Headlines` SET `id` ='".$arr[1]."'
          WHERE `headline LIKE %$arr[1]%";
      mysql_query($update)
      or die(mysql_error());
      }
   }

you need to select fisrt id fron city table and check if appear in headline table, then do the same white 2 id of table cities.

你需要选择fisrt id fron city table并检查是否出现在标题表中,然后执行相同的白色2 id表城市。

i hope this can help you, maybe i didn't understand very well the problem, mi english level isn't very good.

我希望这可以帮助你,也许我不太了解这个问题,mi英语水平不是很好。

#1