473,898 Members | 3,458 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query 2 MySQL databases in 1 statement

I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Thanks.

Jul 26 '06 #1
9 5861
no*********@gma il.com wrote:
I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?
Take a look at some of the comments at

http://us2.php.net/manual/en/functio...-select-db.php

HTH,
--
Benjamin D. Esham
bd*****@gmail.c om | AIM: bdesham128 | Jabber: same as e-mail
Más sabe el diablo por viejo que por diablo. (Spanish proverb)

Jul 26 '06 #2
The comments posted on php.net mainly addresses issues where two
SEPARATE queries accessing two different databases. This can be
accomplished with two different DB handles. However, my question is
regarding one handle accessing two databases.... as in, one query
interacting with two different databases.

Possible??

Thanks.
Benjamin Esham wrote:
no*********@gma il.com wrote:
I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Take a look at some of the comments at

http://us2.php.net/manual/en/functio...-select-db.php

HTH,
--
Benjamin D. Esham
bd*****@gmail.c om | AIM: bdesham128 | Jabber: same as e-mail
Más sabe el diablo por viejo que por diablo. (Spanish proverb)
Jul 26 '06 #3
The comments posted on php.net mainly addresses issues where two
SEPARATE queries accessing two different databases. This can be
accomplished with two different DB handles. However, my question is
regarding one handle accessing two databases.... as in, one query
interacting with two different databases.

Possible??

Thanks.
Benjamin Esham wrote:
no*********@gma il.com wrote:
I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Take a look at some of the comments at

http://us2.php.net/manual/en/functio...-select-db.php

HTH,
--
Benjamin D. Esham
bd*****@gmail.c om | AIM: bdesham128 | Jabber: same as e-mail
Más sabe el diablo por viejo que por diablo. (Spanish proverb)
Jul 26 '06 #4
Got it!

Apparently, if I use the database.table. feild syntax, then PHP/MySQL
connection does not seem to care which database I specified in the
mysql_select_db ($DatabaseName, $db) function.

But I obviously need permissions in both the DBs.

Jul 26 '06 #5
"no*********@gm ail.com" <no*********@gm ail.comwrote:
I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?
As long as the two databases are accessible via the same connection
(i.e., they are on the same server and the login credentials grant you
the required access for both of them) then it's a non-issue, it works
fine.

Otherwise it doesn't, and you will have to create a user that has the
appropriate select privileges on both databases or whatever.

miguel
--
Photos from 40 countries on 5 continents: http://travel.u.nu
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world: http://airport.u.nu
Jul 26 '06 #6
no*********@gma il.com wrote:
I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Thanks.

As I understand it, the database you specify when connecting to mysql
is your default database. For example, if you connect using "db1",
then to query that database, you can just use "SELECT * FROM table1"
which, because of your default database, is equivalent to "SELECT *
FROM db1.table1". You are still able to access other databases by
fully-qualifying the names, so even if "db1" is your default database,
you can still do something like "SELECT * FROM db2.table2".

Jul 26 '06 #7


mo************* ******@yahoo.co m wrote:
no*********@gma il.com wrote:
>I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.tabl eB.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Thanks.


As I understand it, the database you specify when connecting to mysql
is your default database. For example, if you connect using "db1",
then to query that database, you can just use "SELECT * FROM table1"
which, because of your default database, is equivalent to "SELECT *
FROM db1.table1". You are still able to access other databases by
fully-qualifying the names, so even if "db1" is your default database,
you can still do something like "SELECT * FROM db2.table2".
I assume that they need to be in the same MySQL?
Jul 26 '06 #8
When you connect to MySQL you are connecting to a server, not a database,
and a server may contain any number of databases. The reason for using the
mysql_select_db () command is to select a default database so that you do not
have to prefix each table name with a database name. It is still possible to
access a table from another database in a single query simply by using
"database.table ".

HTH

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org

<no*********@gm ail.comwrote in message
news:11******** *************@m 79g2000cwm.goog legroups.com...
>I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.table B.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Thanks.

Jul 26 '06 #9

"Snef" <s.******@snefi t.comwrote in message
news:a6******** *************** ****@news.chell o.nl...
>

mo************* ******@yahoo.co m wrote:
>no*********@gma il.com wrote:
>>I was wondering how it may be possible to query 2 MySQL databases using
one query statement from PHP.

For instance: SELECT database1.table A.field1 UNION
database2.tab leB.field2.

My concern is, when connecting to MySQL (or sending a query), I only
specify 1 database connection resource ID. How does that play out when
connecting to 2 databases?

Thanks.


As I understand it, the database you specify when connecting to mysql
is your default database. For example, if you connect using "db1",
then to query that database, you can just use "SELECT * FROM table1"
which, because of your default database, is equivalent to "SELECT *
FROM db1.table1". You are still able to access other databases by
fully-qualifying the names, so even if "db1" is your default database,
you can still do something like "SELECT * FROM db2.table2".
I assume that they need to be in the same MySQL?
You mean under the same MySQL instance (server). You connect to an instance,
then create databases using that instance, and you can talk to all of those
databases within that instance. The fact that you can designate one of the
databases as the default database does not mean that you can only talk to
that database.

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
Jul 27 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4124
by: eyes2ice | last post by:
Can I have the list of databases without to connect at to one of they? ... String DRIVER = "org.gjt.mm.mysql.Driver"; String DB_URL = "jdbc:mysql://localhost/test"; Class.forName(DRIVER); Connection connection = DriverManager.getConnection(DB_URL,"user","password"); Statement statement = connection.createStatement(); statement.execute("SHOW DATABASES");
4
2431
by: jy2003 | last post by:
I have read a book, which suggests we should change OR to UNION for better performance, but currently I have too many OR clauses(I have a query with 100 ORs) and it does not sound good to have 100 UNION. Any suggestion? Will it help if I create an index for (SetID, PID)? SELECT FileID, PID FROM File WHERE SetID = 4 AND (PID = \path\subpath\morepath\0107.html OR PID = \path\subpath\morepath\0101.html OR
3
3583
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID from letdata UNION SELECT as ID FROM MEMODATA; I get an ODBC error. The same query runs when the backend files are MDB files and it runs with MYSQL if I only combine 2 tables.
4
16764
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from customers; I can execute each statement individually but get the 'you have an error in
3
579
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to make a query that selects from a table as desribed below .. I have a table (Volunteer) that has a member field (memnumber) and a number of fields that are headed in various categories and are yes/no formated
7
2741
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource back from the database, but get's several, or that it just checks the statement first, and decides...
2
4535
by: Bob Alston | last post by:
If you have an access form with record source being a straightforward query and where clause in the form definition, will the query be sent to the back end jet/Access database and executed there, withonly the record(s) meeting the criteria being returned to the front end? Is JetShowPlan a good tool to see that this is working? Bob
1
4294
by: tomlebold | last post by:
Having problems displaying query results from combo boxes on a sub form, which is on the same form that is used to select criteria. This has always worked form me when displaying query results on another main and sub form. The requery on the sub form and refresh comands on the main form do not work when the form is first displayed and when the selection criteria is changed. Should I be doing a refresh and then repaint of the sub form. ...
2
1797
by: alnoir | last post by:
I'm trying to update some records using the UPDATE and SELECT query. I have two databases. The first database (db1) is a subset of the second database (db2). However, the first database is missing information in some of the columns that is found in the second database. So I want to query the second database for that information and update the records in the first database. DB1: ________________________ | col1 | col2 | col3 | ...
0
9842
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10859
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10956
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7191
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6079
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4712
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4298
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3309
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.