RE: [PHP-DB] How to querry two db in a single statement?

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:Andreas.Pucko@web.de] 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:JHilgeman@ecx.com] Gesendet: Freitag, 4. Januar 2002 17:46 An: 'Andreas.Pucko@web.de' 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:Andreas.Pucko@web.de] 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:JHilgeman@ecx.com] Gesendet: Freitag, 4. Januar 2002 17:09 An: 'Andy'; php-db@lists.php.net 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:news.letters@gmx.de] Sent: Friday, January 04, 2002 5:50 AM To: php-db@lists.php.net 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: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net