如何查找相同列表中的电子邮件? [英] How to find which emails are in the same lists?

问题描述

我有11个表[email1，email2，email3，... email11]

I have a 11 tables [email1, email2, email3, ... email11]

<?php
$con = mysql_connect("localhost", "root", "");
$db = mysql_select_db("email-db", $con);
$sql = "SELECT Contact_Email FROM email1, email2, email3, email4, email5";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    ?>
    <tr>
        <td><? echo $row['Contact_Email']; ?></td>
        <td><? echo '<br>'; ?></td>
    </tr>
<? } ?>

我实际上想要执行的操作是使用所有表上的join从所有表中选择所有电子邮件.怎么办?

What I actually want to do it to select all emails from all tables using join on all of them. How can it be done?

推荐答案

由于社区投票决定关闭重复的

Since community voted to close duplicate how to display duplicate email address

这里是4个表的示例，您可以根据需要将其扩展到11个.抱歉，但是我没有调试此代码，我想主要的障碍是要从数据库中获取正确值的mysql查询.

Here is an example for 4 tables you can extend it till 11 if you need. Sorry, but I did not debug this code, I guess the main obstacle was the mysql query to get correct values from database.

您绝对应该停止使用mysql *函数！

And you definitely should stop using mysql* functions!

$sql ="SELECT t.Contact_Email,
  e1.Contact_Email email1,
  e2.Contact_Email email2,
  e3.Contact_Email email3,
  e4.Contact_Email email4
FROM (
SELECT e.Contact_Email FROM email1 e
UNION ALL 
SELECT e.Contact_Email FROM email2 e
UNION ALL 
SELECT e.Contact_Email FROM email3 e
UNION ALL 
SELECT e.Contact_Email FROM email4 e
) t
LEFT JOIN email1 e1
ON t.Contact_Email = e1.Contact_Email
LEFT JOIN email2 e2
ON t.Contact_Email = e2.Contact_Email
LEFT JOIN email3 e3
ON t.Contact_Email = e3.Contact_Email
LEFT JOIN email4 e4
ON t.Contact_Email = e4.Contact_Email";

echo '<table><thead><tr><th>Email</th>';
for ($i=1;$i<5; $i++){
    echo "<th>email $i</th>";
}
echo '</tr></thead><tbody>';

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    echo '<tr><td>'.$row['Contact_Email'].'</td>';
    for ($i=1;$i<5; $i++){
        echo '<td>'.(empty($row['email'.$i])?'no':'yes').'</td>';
    }
    echo '</tr>';
}
echo '</tbody></table>';

更新，使用相同的查询，但将输出部分更改为:

UPDATE Use the same query but change the output part to:

echo '<table><thead><tr><th>Email</th>';
for ($i=1;$i<5; $i++){
    echo "<th>email $i</th>";
}
echo "<th>Total (yes)</th>";
echo "<th>Total (no)</th>";
echo '</tr></thead><tbody>';

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    $yesCount = 0;
    $noCount = 0;
    echo '<tr><td>'.$row['Contact_Email'].'</td>';
    for ($i=1;$i<5; $i++){
        echo '<td>'.(empty($row['email'.$i])?'no':'yes').'</td>';
        if (empty($row['email'.$i])) {
            $noCount++;
        } else {
            $yesCount++;
        }
    }
    echo '<th>'.$yesCount.'</th>';
    echo '<th>'.$noCount.'</th>';
    echo '</tr>';
}
echo '</tbody></table>';

这篇关于如何查找相同列表中的电子邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！