473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

MySQL Question

I am using MYSQL and have multiple databases. I can write code to connect to
one database or the other. How do I write a sql statement that will allow me
to access tables in two different databases?

Thank You...


Jul 17 '05 #1
8 2083
Den Fri, 10 Oct 2003 15:28:37 -0400. skrev Philip Ladin:
I am using MYSQL and have multiple databases. I can write code to connect
to one database or the other. How do I write a sql statement that will
allow me to access tables in two different databases?

Thank You...

You create two connections and remembers to add which connection every
request should go to.

$link1 = mysql_connect("localhost", "mysql_user", "mysql_password")
or die("Could not connect: " . mysql_error());
print ("Connected successfully");
mysql_close($link);
$link2 = mysql_connect("localhost", "mysql_user", "mysql_password")
or die("Could not connect: " . mysql_error());
print ("Connected successfully");
mysql_close($link);
mysql_select_db("foo",$link1);
mysql_select_db("bar",$link2);

mysql_query("select * from table1", $link1);
mysql_query("select * from table1", $link2);

etc.

--
Hilsen/Sincerely, Michael Rasmussen

En windows admin er en person, for hvem den største bedrift er, at
lave konfiguration af serveren med trial and error via en gui.

Jul 17 '05 #2
Thank you...

"Michael Rasmussen" <mi*@datanom.net> wrote in message
news:pa****************************@datanom.net...
Den Fri, 10 Oct 2003 15:28:37 -0400. skrev Philip Ladin:
I am using MYSQL and have multiple databases. I can write code to connect to one database or the other. How do I write a sql statement that will
allow me to access tables in two different databases?

Thank You...

You create two connections and remembers to add which connection every
request should go to.

$link1 = mysql_connect("localhost", "mysql_user", "mysql_password")
or die("Could not connect: " . mysql_error());
print ("Connected successfully");
mysql_close($link);
$link2 = mysql_connect("localhost", "mysql_user", "mysql_password")
or die("Could not connect: " . mysql_error());
print ("Connected successfully");
mysql_close($link);
mysql_select_db("foo",$link1);
mysql_select_db("bar",$link2);

mysql_query("select * from table1", $link1);
mysql_query("select * from table1", $link2);

etc.

--
Hilsen/Sincerely, Michael Rasmussen

En windows admin er en person, for hvem den største bedrift er, at
lave konfiguration af serveren med trial and error via en gui.

Jul 17 '05 #3
Philip Ladin wrote:
I am using MYSQL and have multiple databases. I can write code to connect to
one database or the other. How do I write a sql statement that will allow me
to access tables in two different databases?


multiple databases on the same server? easy :-)

<?php
$conn = mysql_connect('server', 'user', 'pass');
$sql = "select a.col1, a.col2, b.col2, b.col3"
. "from db1.table a, db2.table b" # a is in db1; b is in db2
. "where a.id=b.id";
$res = mysql_query($sql);
echo '<table>';
while ($row = mysql_fetch_row($res)) {
echo '<tr>';
foreach ($row as $x) {
echo "<td>$x</td>";
}
echo '</tr>';
}
echo '</table>';
?>

--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.
Jul 17 '05 #4
Thank you...
"Pedro" <he****@hotpop.com> wrote in message
news:bm************@ID-203069.news.uni-berlin.de...
Philip Ladin wrote:
I am using MYSQL and have multiple databases. I can write code to connect to one database or the other. How do I write a sql statement that will allow me to access tables in two different databases?


multiple databases on the same server? easy :-)

<?php
$conn = mysql_connect('server', 'user', 'pass');
$sql = "select a.col1, a.col2, b.col2, b.col3"
. "from db1.table a, db2.table b" # a is in db1; b is in db2
. "where a.id=b.id";
$res = mysql_query($sql);
echo '<table>';
while ($row = mysql_fetch_row($res)) {
echo '<tr>';
foreach ($row as $x) {
echo "<td>$x</td>";
}
echo '</tr>';
}
echo '</table>';
?>

--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.

Jul 17 '05 #5
"Philip Ladin" <ph*****@amalla.com> wrote in message
news:R_*******************@bignews4.bellsouth.net. ..
Thank you...
"Pedro" <he****@hotpop.com> wrote in message
news:bm************@ID-203069.news.uni-berlin.de...
Philip Ladin wrote:
I am using MYSQL and have multiple databases. I can write code to connect to one database or the other. How do I write a sql statement that will allow me to access tables in two different databases?


multiple databases on the same server? easy :-)

<?php
$conn = mysql_connect('server', 'user', 'pass');
$sql = "select a.col1, a.col2, b.col2, b.col3"
. "from db1.table a, db2.table b" # a is in db1; b is in db2
. "where a.id=b.id";
$res = mysql_query($sql);
echo '<table>';
while ($row = mysql_fetch_row($res)) {
echo '<tr>';
foreach ($row as $x) {
echo "<td>$x</td>";
}
echo '</tr>';
}
echo '</table>';
?>


I would also suggest that a requirement to query across databases means that
your database structure has been poorly designed.

Paulus
Jul 17 '05 #6
On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
<pa***********@loves-spam.com> scrawled:
"Philip Ladin" <ph*****@amalla.com> wrote in message
news:R_*******************@bignews4.bellsouth.net ...
Thank you...
"Pedro" <he****@hotpop.com> wrote in message
news:bm************@ID-203069.news.uni-berlin.de...
> Philip Ladin wrote:
> > I am using MYSQL and have multiple databases. I can write code to

connect to
> > one database or the other. How do I write a sql statement that will

allow me
> > to access tables in two different databases?
>
> multiple databases on the same server? easy :-)
>
> <?php
> $conn = mysql_connect('server', 'user', 'pass');
>
>
> $sql = "select a.col1, a.col2, b.col2, b.col3"
> . "from db1.table a, db2.table b" # a is in db1; b is in db2
> . "where a.id=b.id";
>
>
> $res = mysql_query($sql);
> echo '<table>';
> while ($row = mysql_fetch_row($res)) {
> echo '<tr>';
> foreach ($row as $x) {
> echo "<td>$x</td>";
> }
> echo '</tr>';
> }
> echo '</table>';
> ?>


I would also suggest that a requirement to query across databases means that
your database structure has been poorly designed.


May not be - it may be that you have a core of data in one database, and
that you have a number of optional additional data in a number of satellite
databases, these all have a common schema but may be produced in house,
or obtained from external sources.

This allows for the arbitrary combination of data from a number of
different sources without having to have the bastardisation of table names
to produce table "mydata_feature" instead of "mydata.feature".

On a large project I work on (not in PHP) we do this a lot to achieve quite
complex data manipulations (at the last count we were placing over
100Gbytes of data in the public domain)

But we also have a layer that can do this join in software rather than
using the niceness of using MySQLs database join features - it just results
in a lot more queries a lot more code and a loss of speed, but does work
when the databases are not collocated.
Jul 17 '05 #7

"James" <ne*******@black-panther.freeserve.co.uk> wrote in message
news:3f***************@news.freeserve.com...
On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
<pa***********@loves-spam.com> scrawled:
"Philip Ladin" <ph*****@amalla.com> wrote in message
news:R_*******************@bignews4.bellsouth.net ...
Thank you...
"Pedro" <he****@hotpop.com> wrote in message
news:bm************@ID-203069.news.uni-berlin.de...
> Philip Ladin wrote:
> > I am using MYSQL and have multiple databases. I can write code to
connect to
> > one database or the other. How do I write a sql statement that will
allow me
> > to access tables in two different databases?
>
> multiple databases on the same server? easy :-)
>
> <?php
> $conn = mysql_connect('server', 'user', 'pass');
>
>
> $sql = "select a.col1, a.col2, b.col2, b.col3"
> . "from db1.table a, db2.table b" # a is in db1; b is in db2
> . "where a.id=b.id";
>
>
> $res = mysql_query($sql);
> echo '<table>';
> while ($row = mysql_fetch_row($res)) {
> echo '<tr>';
> foreach ($row as $x) {
> echo "<td>$x</td>";
> }
> echo '</tr>';
> }
> echo '</table>';
> ?>
I would also suggest that a requirement to query across databases means thatyour database structure has been poorly designed.


May not be - it may be that you have a core of data in one database, and
that you have a number of optional additional data in a number of

satellite databases, these all have a common schema but may be produced in house,
or obtained from external sources.


Very true, I just think in the majority of cases (99% of all the occasions
that I've seen it) that using more than one database is an indication of a
poorly designed data structure.

Paulus
Jul 17 '05 #8
I agree with you that in most cases, spreading data across disparate
databases is a poor design. In my case, I was converting some data from
different sources and was writing some maintenance scripts....

"Paulus Magnus" <pa***********@loves-spam.com> wrote in message
news:bm**********@titan.btinternet.com...

"James" <ne*******@black-panther.freeserve.co.uk> wrote in message
news:3f***************@news.freeserve.com...
On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
<pa***********@loves-spam.com> scrawled:
"Philip Ladin" <ph*****@amalla.com> wrote in message
news:R_*******************@bignews4.bellsouth.net ...
> Thank you...
> "Pedro" <he****@hotpop.com> wrote in message
> news:bm************@ID-203069.news.uni-berlin.de...
> > Philip Ladin wrote:
> > > I am using MYSQL and have multiple databases. I can write code to
> connect to
> > > one database or the other. How do I write a sql statement that will> allow me
> > > to access tables in two different databases?
> >
> > multiple databases on the same server? easy :-)
> >
> > <?php
> > $conn = mysql_connect('server', 'user', 'pass');
> >
> >
> > $sql = "select a.col1, a.col2, b.col2, b.col3"
> > . "from db1.table a, db2.table b" # a is in db1; b is in db2> > . "where a.id=b.id";
> >
> >
> > $res = mysql_query($sql);
> > echo '<table>';
> > while ($row = mysql_fetch_row($res)) {
> > echo '<tr>';
> > foreach ($row as $x) {
> > echo "<td>$x</td>";
> > }
> > echo '</tr>';
> > }
> > echo '</table>';
> > ?>

I would also suggest that a requirement to query across databases means thatyour database structure has been poorly designed.


May not be - it may be that you have a core of data in one database, and
that you have a number of optional additional data in a number of

satellite
databases, these all have a common schema but may be produced in house,
or obtained from external sources.


Very true, I just think in the majority of cases (99% of all the occasions
that I've seen it) that using more than one database is an indication of a
poorly designed data structure.

Paulus

Jul 17 '05 #9

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

Similar topics

19
by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5...
0
by: MJL | last post by:
This is a mysql/php question (but a little more on the mysql side.) The two are so closely related these days, I thought it would be ok to ask here. I installed on my Suse Linux system mysql 4.0...
8
by: Tony Clarke | last post by:
Hi, Just a quick question about performance with MySQL & PHP. If I had a table in a MySQL database with about 100,000 records in it and I need to find the last record is there a quick way to do...
2
by: pieter_hordijk | last post by:
Hi all, maybe this isn't a php question, but a MySQL question. If so I'm sorry for asking you guys to help me :) I know this question is asked often in NGs, but I couldn't find the answer...
2
by: kimshapiro100 | last post by:
Question on PhP, MySQL I am thinking of a consumer internet business for which I will have to have a database driven site built. I am thinking of using PhP, MySQL as the main technologies...
39
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort...
1
by: Jim Carlock | last post by:
I have a couple questions about MySQL involving which version of MySQL to use. I'm looking for minimal memory use on a Windows XP machine. Which version would be best for this? And can anyone...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
5
by: news.telia.net | last post by:
Hi! I have a question. I have installed php and mysql on an apache-server on windows and I can't connect to the server. I tried to create a database (since I am trying to learn howto). My...
27
by: gerrymcc | last post by:
Hello, I'm a php/mysql beginner... Is there any way of making the mysql command line client full-screen? Sometimes it's easier to use the client than go thru php, but since it's only about 80...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.