[SOLVED] Joining tables

[wsantos]

Is it possible to join two tables from two different connection?

$con1 = mysql_connect("ip1","user","pwd");
  if(!$con1)
  {
    die('Could not connect: ' . mysql_error());
  }
  $db1 = mysql_select_db("table1",$con1);

  $con2 = mysql_connect("ip2","user","pwd");
  if(!$con1)
  {
    die('Could not connect: ' . mysql_error());
  }
  $db2 = mysql_select_db("table2",$con2);

[pikemsu28]

if the username/password has the same access to both databases your query can just include the database name before the table name

 

$query = "SELECT * FROM database1.table1 JOIN database2.table2 WHERE database1.table1.id = database2.table2.id";

[wsantos]

like this?

 

 

 

select table2.uniqueid,table3.action_id,table3.action from db2.table3 join db2.table2 join db1.table1 where table3.action = table2.action and table1.uniqueid = table2.uniqueid limit 30

 

or do i put the db name in the fieldnames like this?

 

select db2.table2.uniqueid,db2.table3.action_id,db2.table3.action from db2.table3 join db2.table2 join db1.table1 where table3.action = table2.action and table1.uniqueid = table2.uniqueid limit 30

[pikemsu28]

the second one. you need to put that database before everything when pulling from multiple databases.

 

made some slight changes to query.

select db2.table2.uniqueid,db2.table3.action_id,db2.table3.action from db2.table3, db2.table2, db1.table1 where DB2.table3.action = DB2.table2.action and DB1.table1.uniqueid = DB2.table2.uniqueid limit 30

 

why are you joining db1.table1 when you are not pulling any data from it? it's ambiguous.

 

EDIT:

it's also possible to use aliases

 

select tb2.uniqueid,tb3.action_id,tb3.action from db2.table3 as tb3, db2.table2 as tb2, db1.table1 as tb1 where tb3.action = tb2.action and tb1.uniqueid = tb2.uniqueid limit 30

[wsantos]

here is my complete connection and query as it appears on my script except for the ip,username, and password

 

// Connect to CDR Table
  $conCDR = mysql_connect("ip1","user","pwd");
  if(!$conCDR)
  {
    die('Could not connect: ' . mysql_error());
  }

// Connect to CDR Table
  $conAsteriskCDR = mysql_connect("ip2","user","pwd");
  if(!$conAsteriskCDR)
  {
    die('Could not connect: ' . mysql_error());
  }

$query = "SELECT cdr.cdr.uniqueid, cdr.cdr.clid, asteriskcdr.lookup_actions.action_id, asteriskcdr.lookup_actions.action from  asteriskcdr.lookup_actions join asteriskcdr.queue_actions join cdr.cdr where asteriskcdr.lookup_actions.action = asteriskcdr.queue_actions.action and cdr.cdr.uniqueid = asteriskcdr.queue_actions.uniqueid and cdr.cdr.clid like '%" . $Group . " G%' and cdr.cdr.start >= '$startdate' and end <= '$enddate' ORDER BY cdr.cdr.cdrid;

mysql_close($conCDR);
mysql_close($conAsteriskCDR);