如何比较PHP中两个表中的两个字段？

Table_1

ID | Username
 1   John
 2   Mike
 3   Chase
 4   Shane

Table_2

 ID | Username
 1  | John
 2  | Kenny
 3  | Chase
 4  | Shane

I want to get ID from Table_1. Then find that ID in table_2. Then i need it to look at the field Username in both tables. and if they match do nothing, If they don't match then update it to the username in Table_2 and run some code (like email me)

我想从Table_1获取ID。然后在table_2中找到该ID。然后我需要它来查看两个表中的字段用户名。如果它们匹配什么都不做,如果它们不匹配则将其更新为Table_2中的用户名并运行一些代码(如给我发电子邮件)

I need to to check every row in Table_1 everytime I run the script.

每次运行脚本时,我都需要检查Table_1中的每一行。

2 个解决方案

#1

You have to use INNER JOIN in case to find the matching ID in both the tables and if you got one then update.

你必须使用INNER JOIN以便在两个表中找到匹配的ID,如果你有一个然后更新。

$sql = mysql_query("SELECT table_1.id as id_one,table_1.name as name_one,table_2.name as name_two FROM table_1 INNER JOIN table_2 ON (table_1.id = table_2.id)") or die(mysql_error());

    if(mysql_num_rows($sql) > 0)
    {
         while($fetch = mysql_fetch_assoc($sql))
         {
               if($fetch['name_one'] != $fetch['name_two'])
               {
                     // UPDATE table_1 'name' FIELD

                       mysql_query("UPDATE table_1 SET name = '".$fetch['name_two']."' WHERE id = ".$fetch['id_one']) or die(mysql_error());

                          // DO WHATEVER YOU WANT
               }

         }
    }

#2

My MySQL is a little rusty, but it can be done with a single Update statement. The following should update the usernames in Table_1 only where they differ from Table_2. You may have to tweak the syntax a little...

我的MySQL有点生疏,但可以使用单个Update语句完成。以下内容应仅更新Table_1中与Table_2不同的用户名。您可能需要稍微调整一下语法...

update Table_1 t1
join  Table_2 t2
   on  t2.id = t1.id
  and  t2.Username <> t1.Username
set   t1.Username = t2.Username;

You can query beforehand to generate any email you need.

您可以事先查询以生成所需的任何电子邮件。

#1