用PHP比较两个MySQL表之间的数据

问题描述：

我有两个表table1 =记录，table2 =重复项.两个表在任何给定时间都包含可变数量的列，并且它们都包含完全相同的列，除了table2具有附加的ID列...两个表都包含列user_id.数据来自CSV导入.如果user_id中已经存在user_id，则会将其插入table2.

I have two tables table1 = records, table2 = duplicates. Both tables contain a variable number of columns at any given time and they both contain the exact same columns with the exception of table2 having an additional ID column... Both tables contain a column, user_id. The data comes from a CSV import. If the user_id already exists in table1 it is inserted into table2.

使用table2，我需要能够抓取所有行并将它们打印到表中，这没有问题.我很难解决的部分...对于要打印的table2中的每一列，我需要检查数据是否匹配(基于user_id)和table1中的数据，并以某种方式对其进行标记...(可能是表格单元格上的其他颜色背景)

With table2 I need to be able to grab all the rows and print them out in a table, no problem. The part I'm having a hard time figuring out... For each column in table2 that gets printed I need to check if the data matches(based on the user_id) the data in table1 and somehow flag it... (maybe a different color background on the table cell)

示例:

table1包含以下行:

user_id | user_name
-------------------
2342342 | some name

和table2:

user_id | user_name
-------------------
2342342 | different name

那么输出将是:

-----------------------------------------
|2342342 | *flag* different name *flag* |
-----------------------------------------

关于如何进行这项工作的任何想法?如果有帮助，我将使用Codeigniter构建此应用.

Any idea as to how I could make this work? If it helps any, I'm building this app with Codeigniter.

答

此查询将从表2中选择所有条目，如果名称与表1中的名称不同，则is_different将为1，否则为0:

This query will select all the entries from table2, and if the name is different than the name in table1, is_different will be 1, otherwise it's 0:

SELECT
    table2.user_name,
    IF(table2.user_name != table1.user_name, 1, 0) AS is_different
FROM
    table2
LEFT JOIN
    table1
ON
    table1.user_id = table2.user_id

编辑

如果您需要比较多个列，则可以在一个查询中进行多个测试:

You can do several of those tests in one query, if you need to compare more than one column:

SELECT
    table2.user_name,
    table2.user_email,
    IF(table2.user_name != table1.user_name, 1, 0) AS is_name_different,
    IF(table2.user_email != table1.user_email, 1, 0) AS is_email_different
FROM
    ...

用PHP比较两个MySQL表之间的数据

相关推荐