用PHP比较两个MySQL表之间的数据 [英] Comparing data between two MySQL tables with PHP
问题描述
我有两个表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表之间的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!