How about: SELECT c.country FROM db2.users u LEFT JOIN db1.countries c ON u.country_id=c.country_id GROUP BY c.country;
This assumes the following layout: db1.countries ------------------------ | country_id | country | ------------------------ | 67 | USA | | 68 | Uganda | | 69 | Canada | ------------------------ db2.users ----------------------------------- | user_id | username | country_id | ----------------------------------- | 1 | george | 69 | | 2 | harry | 67 | | 3 | bob | 68 | | 4 | bill | 69 | | 5 | joe | 68 | | 6 | martha | 68 | ----------------------------------- This should result in ----------- | country | ----------- | USA | | Uganda | | Canada | ----------- You should be able to add an "ORDER BY c.country" to that SELECT query if you want to sort the results alphabetically. - Jonathan -----Original Message----- From: Andreas Pucko [mailto:[EMAIL PROTECTED]] Sent: Friday, January 04, 2002 9:40 AM To: Jonathan Hilgeman Subject: AW: [PHP-DB] How to querry two db in a single statement? I wrote them: ############################################################################ #################### # database: connect function db_connect() { global $sys_dbhost,$sys_dbuser,$sys_dbpasswd, $mailadress_webmaster; $conn = @mysql_connect($sys_dbhost,$sys_dbuser,$sys_dbpasswd); if (!$conn) { # send error mail to webmaster $mailHeaders = "From: ".$mailadress_webmaster."\n"; // from $mailHeaders .= "X-Priority: 1\n"; // Urgent message! mail($mailadress_webmaster,'MySQL error. Website is down!',mysql_error(),$mailHeaders); HEADER("Location:/app_global/errors/unavailable.html");//redirect to unavailable } return $conn; } ############################################################################ #################### ############################################################################ #################### # Select the Database function select_db($link,$DB){ global $mailadress_webmaster, $header_1_boxcolor_2; if (!mysql_select_db($DB, $link)){ header_1('Error', $header_1_boxcolor_2); echo(' <br> We are sorry, an error has occured.<br>Please contact our webmaster at: <a href="mailto:'.$mailadress_webmaster.'">'.$mailadress_webmaster.'</a> <br> <br> Occured during executing '); DisplayErrMsg(sprintf("%s", $stmt)); echo(" statement <br>\n <br>\n Error: "); DisplayErrMsg(sprintf(" %d %s", mysql_errno($link), mysql_error($link))) ; exit() ; } } ############################################################################ #################### The layout of the db is verry complex. Too long to show all the tables. But in short form there is one table in db1 called country and another in db2 called user. The user has the country as a field. I am trying to find out the countries the users come from. Building a select field containing only the countries where user come from. Cheers Andy -----Ursprüngliche Nachricht----- Von: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 4. Januar 2002 17:46 An: '[EMAIL PROTECTED]' Betreff: RE: [PHP-DB] How to querry two db in a single statement? Are select_db and db_connect functions that you wrote or someone else wrote? They are not generic PHP4.x functions... In any case, it would help to see your database layouts... - Jonathan -----Original Message----- From: Andreas Pucko [mailto:[EMAIL PROTECTED]] Sent: Friday, January 04, 2002 8:29 AM To: Jonathan Hilgeman Subject: AW: [PHP-DB] How to querry two db in a single statement? Hello Jonathan, my problem is how to connect to 2 db in the same time. They are on the same server. Here is the code I wrote so far: ################################################### # connect to db1 $link = db_connect(); //connect to db select_db($link,$DB); // select db ################################################### ################################################### # Get countries who are currently used by registered members: // $geo_T1 is in $DB2 // $T5 is in $DB $stmt= " SELECT DISTINCT C.* /*Distinct means no equal results*/ from $geo_T1 C, $T5 U where C.country_id = U.country order by country_name "; $result = execute_stmt($stmt, $link); while ($row = mysql_fetch_object($result)){ $country_id[] = $row->country_id; $country_name[] = $row->country_name; }; ################################################### Thanx for your help Regards, Andy -----Ursprüngliche Nachricht----- Von: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 4. Januar 2002 17:09 An: 'Andy'; [EMAIL PROTECTED] Betreff: RE: [PHP-DB] How to querry two db in a single statement? If the two databases are on the same server, you can use joins to select data from multiple databases. For instance: Database 1, Table A: -------------------- | id | name | -------------------- | 1 | George | | 2 | Mark | | 3 | Harry | -------------------- Database 2, Table A: -------------------- | id | name | -------------------- | 1 | Bill | | 2 | Henry | | 3 | Ford | -------------------- You can: SELECT a.name as db1Name, b.name as db2Name FROM Database1.TableA a LEFT JOIN Database2.TableA b ON a.id=b.id; Now you'll get: ----------------------- | db1Name | db2Name | ----------------------- | George | Bill | | Mark | Henry | | Harry | Ford | ----------------------- Hope this helps. - Jonathan -----Original Message----- From: Andy [mailto:[EMAIL PROTECTED]] Sent: Friday, January 04, 2002 5:50 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] How to querry two db in a single statement? Hi there, I would like to make a querry on 2 db. There is info in tables I would like to compare. As I know, there has first to be a conect on the db before the querry. But how to do this on 2 db?? Thanx for any help. Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
