[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]
Re: [PHP-DB] How to querry two db in a single statement?
Try the following: ?php mysql_connect(firstDB,username,password) or die (Unable to connect to Server); mysql_select_db(DB) or die (Unable to select DB); ---GET THE INFO YOU WANT AND PLACE IT IN AN APPROPRIATE VARIABLES --FOR LATER COMPARISON mysql_connect(secondDB,username,password) or die (Unable to connect to Server); mysql_select_db(DB) or die (Unable to select DB); --COMPARE WITH THE VALUES FROM THE VARIABLES ? I believe that this should work (I haven't tried it though!!!) May the force be with you!!! George - Original Message - From: Andy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 04, 2002 3:50 PM 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]
Fwd: Re: [PHP-DB] How to querry two db in a single statement?
Didn't notice that this didn't get cc:d to the list, so here it hoes. -- Forwarded Message -- Subject: Re: [PHP-DB] How to querry two db in a single statement? Date: Fri, 4 Jan 2002 15:58:35 +0200 From: markus|lervik [EMAIL PROTECTED] To: Andy [EMAIL PROTECTED] On Friday 04 January 2002 15:50, you wrote: 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?? As far as I know there aren't any relational databases that support querying multiple databases in one query. I guess you'd be best off querying the first database, save the result, query the second database, save the result and then compare the two results. Please do correct me if I'm wrong, people. Cheers, Markus --- -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 -- 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]
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]
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:[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.brPlease
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
Re: Fwd: Re: [PHP-DB] How to querry two db in a single statement?
Hi all: AFAIK, it is possible in a bunch of database software to query multiple DBs through joining. I have a Foxpro background (so most of what I say is therefore, b.s.) but you can define your query tables using this notation: database.table Which allows you to query multiple DBs. You should be able to do this (using joins) in MySQL. However, I don't know how off the top of my head. That might be more of a MySQL-list-geek type of question. Cheers, db markus|lervik wrote: Didn't notice that this didn't get cc:d to the list, so here it hoes. -- Forwarded Message -- Subject: Re: [PHP-DB] How to querry two db in a single statement? Date: Fri, 4 Jan 2002 15:58:35 +0200 From: markus|lervik [EMAIL PROTECTED] To: Andy [EMAIL PROTECTED] On Friday 04 January 2002 15:50, you wrote: 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?? As far as I know there aren't any relational databases that support querying multiple databases in one query. I guess you'd be best off querying the first database, save the result, query the second database, save the result and then compare the two results. Please do correct me if I'm wrong, people. Cheers, Markus --- -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 -- 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] -- -- Dan Barton Terrestrial Program Biologist Asst. Data Manager Point Reyes Bird Observatory http://www.prbo.org [EMAIL PROTECTED] [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]
