1

How can I connect with db1, server1 with sub query ? Thanks!

$link1 = mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());

$sql="SELECT db2.`e`.`entity_id` , db2.a.value AS klantnr, db2.b.value AS    voornaam,db2.c.value AS achternaam, db2.`t`.`value` AS telfnr, db2.ce.email FROM     db2.`tablename` e LEFT OUTER JOIN db2.customer_entity_varchar a ON db2.a.entity_id = db2.e.parent_id AND db2.a.attribute_id = '133' WHERE db2.ce.email NOT IN(SELECT db1.email_addresses.email_address FROM db1.tablename) AND db2.a.value IN (SELECT db1.accounts_cstm.custid_c FROM db1.accounts_cstm ) GROUP BY db2.ce.email";
$result = mysql_query($sql,????) or die(mysql_error());

I have another solution (way) but I get this error(Operand should contain 1 column(s))

$link1 =mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);
$link2 = mysql_connect("server2","user2","pas2") or die(mysql_error());
mysql_select_db("db2", $link2) or die(mysql_error());
$query="SELECT email_addresses.email_address FROM db1.tablename";
$result = mysql_query($query,$link1) or die(mysql_error()); 
$select=array();
$row=mysql_fetch_array($result);
$select[]=$row["email_address"];
$select = "('" . implode ("','", $select) . "')";
mysql_query("set     sql_big_selects=1",$moonen_link); 
$sql="SELECT `e`.`entity_id` , a.value AS klantnr, b.value AS voornaam, c.value AS achternaam, `t`.`value` AS telfnr, ce.email FROM `tablename` e LEFT OUTER JOIN customer_entity_varchar a ON a.entity_id = e.parent_id AND a.attribute_id = '133' WHERE ce.email NOT IN($select) GROUP BY ce.email"; $result = mysql_query($sql,$link2) or die(mysql_error());
user3328880
  • 23
  • 8
  • Just to clarify, `server1` and `server2` are different servers, correct? – Patrick Q Mar 20 '14 at 15:20
  • 1
    possible duplicate of [How to connect multiple database,servers in mysql and query from both tables of each other?](http://stackoverflow.com/questions/9584810/how-to-connect-multiple-database-servers-in-mysql-and-query-from-both-tables-of) – Patrick Q Mar 20 '14 at 15:22
  • possible duplicate of [MySQL -- Joins Between Databases On Different Servers Using Python?](http://stackoverflow.com/questions/5832787/mysql-joins-between-databases-on-different-servers-using-python) – Daniel W. Mar 20 '14 at 15:23
  • Yes server1 and server2 are different servers – user3328880 Mar 20 '14 at 15:25
  • You can use federation connection, its table that come from other db. – 4EACH Mar 21 '14 at 09:10

1 Answers1

-1

I think your "user1" should have access (privilege) to both database if they are on the same mysql server.

Then you could connect to one database with

$link1 = mysql_connect("server1","user1","pas1");
mysql_select_db('db1', $link1);

And then do your request on both server with $link1:

 $sql="SELECT db2.`e`.`entity_id` , db2.a.value AS klantnr, db2.b.value AS     voornaam,db2.c.value AS achternaam, db2.`t`.`value` AS telfnr, db2.ce.email FROM     db2.`tablename` e LEFT OUTER JOIN db2.customer_entity_varchar a ON db2.a.entity_id = db2.e.parent_id AND db2.a.attribute_id = '133' WHERE db2.ce.email NOT IN(SELECT db1.email_addresses.email_address FROM db1.tablename) AND db2.a.value IN (SELECT db1.accounts_cstm.custid_c FROM db1.accounts_cstm ) GROUP BY db2.ce.email";
$result = mysql_query($sql,$link1) or die(mysql_error());

Should be ok

Stanislas Nichini
  • 329
  • 2
  • 7
  • Where is the connection with the db2 and server2? I want the connection with db1,server1 on the (SELECT db1.accounts_cstm.custid_c FROM db1.accounts_cstm ) – user3328880 Mar 20 '14 at 15:38
  • if the databases are on the same server you could connect to other database without create a new link. Like DanFromGermany said you're on 2 different servers. so my answer is not wrong but unappropriate to your case. Sorry – Stanislas Nichini Mar 20 '14 at 16:50