HELP! Need to select data from 2 databases

A

Anonymous

Guest
Hi Guys,

I have 2 databases that I need to extract email addresses from.

What I want to do is end up with a result set containing :-

a) The 'email' field from DB1.members and DB2.users

b) The email addresses that are in DB1.members BUT NOT in DB2.users

c) The email addresses that are NOT in DB1.members but ARE in DB2.users.

Confused? .. I am... :cry:

There is NO field by which to compare the databases/tables as in a LEFT JOIN ... ON, or maybe the syntax has me confused.

Any help please??????
 
PaulG said:
a) The 'email' field from DB1.members and DB2.users

b) The email addresses that are in DB1.members BUT NOT in DB2.users

c) The email addresses that are NOT in DB1.members but ARE in DB2.users.
Click to expand...

I'm not sure what you want here -- if you include all of the e-mail addresses that are in both tables, then it's superfluous to also return the mutually-exclusive e-mail addresses in the same result set. You'll just end up with the same list repeated twice. Maybe if you could try to explain in a different way what you're after. Or, better yet, draw us a simple ASCII diagram of what the two tables look like and what you want the result set to look like.

Here's a stab at what I think you might be after, though: If you're using MySQL 4, you can use the UNION operator to join the results of several different SELECTs. Just compose a SELECT statement for each of the sets of data you want to get and then string them together using UNION and you'll get all of the results in one list.
 
You probably should be doing it through the MySql way. But this is what came to mind when i read your post.

I've just had a play with how to do this seeing as I've been slacking off with PHP lately.

Code: 
<pre>
<?php
$conn = mysql_connect("localhost", "joel", "skanerd5");
$db1 = mysql_select_db("db1");
$db2 = mysql_select_db("db2");

$result1 = mysql_query("SELECT email FROM db1.users") or die(mysql_error());
$result2 = mysql_query("SELECT email FROM db2.members") or die(mysql_error());

if (mysql_num_rows($result1) > 0) {
	$i = 0;
	while ($row = mysql_fetch_assoc($result1)) {
		$email1[$i] = $row['email'];
		++$i;
	}
}
print "Users:\n";
print_r($email1);
print "\n";

if (mysql_num_rows($result2) > 0) {
	$i = 0;
	while ($row = mysql_fetch_assoc($result2)) {
		$email2[$i] = $row['email'];
		++$i;
	}
}
print "Members:\n";
print_r($email2);
print "\n";
$email_all = array_merge($email1, $email2);
print "All:\n";
print_r($email_all);
?>
</pre>
this will give you 3 arrays.
 
Thanks Guys,

swirlee, all i want to do is extract the email addresses from both tables so I can mail them all.

Also I want to sometimes email all those in DB1 who are NOT in BD2

AND sometimes I will want to email those who are in DB2 but NOT in DB1.

I'm just not getting the syntax I think.

My DB1 has 51 records and DB2 has 119 records.
If i use
SELECT a.email,b.email FROM DB1.members a, DB2.users b;
this gives a result set of 6069 records (51 x 119) !!!!! :?

Joel, thanks for the code, but it doesn't exclude emails in one or other of my databases.

Paul.
 
The code I had, had 3 arrays - One with the emails from db1, one with the emails from db2 and one with them all. I don't know the best way to mail, as in to put the mail() in the loop, or have a string which is appended to by array contents in the loop. Maybe try the mail forum for that.
 
You must log in or register to reply here.
Back
Top