Reputation: 40
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>
<? } ?>
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?
Upvotes: 0
Views: 96
Reputation: 17289
Since community voted to close duplicate how to display duplicate email address
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.
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>';
Upvotes: 2
Reputation: 6661
MySQL UNION operator allows you to combine two or more result sets from multiple tables into a single result set
SELECT Contact_Email FROM email1
UNION
SELECT Contact_Email FROM email2
UNION
SELECT Contact_Email FROM email3
.
.
UNION
SELECT Contact_Email FROM email11
By default, the UNION operator eliminates duplicate rows from the result even if you don’t use DISTINCT operator explicitly.
Upvotes: 1