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...

re: MySQL Question

Den Fri, 10 Oct 2003 15:28:37 -0400. skrev Philip Ladin:
[color=blue]
> 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...[/color]
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.

re: MySQL Question

Thank you...

"Michael Rasmussen" <mir@datanom.net> wrote in message
news:pan.2003.10.10.19.55.20.227967@datanom.net...[color=blue]
> Den Fri, 10 Oct 2003 15:28:37 -0400. skrev Philip Ladin:
>[color=green]
> > I am using MYSQL and have multiple databases. I can write code to[/color][/color]
connect[color=blue][color=green]
> > 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...[/color]
> 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.
>[/color]

re: MySQL Question

Philip Ladin wrote:[color=blue]
> 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?[/color]

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.

re: MySQL Question

Thank you...
"Pedro" <hexkid@hotpop.com> wrote in message
news:bm75dt$jcgkf$1@ID-203069.news.uni-berlin.de...[color=blue]
> Philip Ladin wrote:[color=green]
> > I am using MYSQL and have multiple databases. I can write code to[/color][/color]
connect to[color=blue][color=green]
> > one database or the other. How do I write a sql statement that will[/color][/color]
allow me[color=blue][color=green]
> > to access tables in two different databases?[/color]
>
> 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.[/color]

re: MySQL Question

"Philip Ladin" <philipl@amalla.com> wrote in message
news:R_Ehb.31144$Sn1.11518@bignews4.bellsouth.net. ..[color=blue]
> Thank you...
> "Pedro" <hexkid@hotpop.com> wrote in message
> news:bm75dt$jcgkf$1@ID-203069.news.uni-berlin.de...[color=green]
> > Philip Ladin wrote:[color=darkred]
> > > I am using MYSQL and have multiple databases. I can write code to[/color][/color]
> connect to[color=green][color=darkred]
> > > one database or the other. How do I write a sql statement that will[/color][/color]
> allow me[color=green][color=darkred]
> > > to access tables in two different databases?[/color]
> >
> > 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>';
> > ?>[/color][/color]

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

Paulus

re: MySQL Question

On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
<paulus.magnus@loves-spam.com> scrawled:
[color=blue]
>"Philip Ladin" <philipl@amalla.com> wrote in message
>news:R_Ehb.31144$Sn1.11518@bignews4.bellsouth.net ...[color=green]
>> Thank you...
>> "Pedro" <hexkid@hotpop.com> wrote in message
>> news:bm75dt$jcgkf$1@ID-203069.news.uni-berlin.de...[color=darkred]
>> > Philip Ladin wrote:
>> > > I am using MYSQL and have multiple databases. I can write code to[/color]
>> connect to[color=darkred]
>> > > one database or the other. How do I write a sql statement that will[/color]
>> allow me[color=darkred]
>> > > 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>';
>> > ?>[/color][/color]
>
>I would also suggest that a requirement to query across databases means that
>your database structure has been poorly designed.
>[/color]

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.

re: MySQL Question

"James" <newsgroup@black-panther.freeserve.co.uk> wrote in message
news:3f87b403.98111186@news.freeserve.com...[color=blue]
> On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
> <paulus.magnus@loves-spam.com> scrawled:
>[color=green]
> >"Philip Ladin" <philipl@amalla.com> wrote in message
> >news:R_Ehb.31144$Sn1.11518@bignews4.bellsouth.net ...[color=darkred]
> >> Thank you...
> >> "Pedro" <hexkid@hotpop.com> wrote in message
> >> news:bm75dt$jcgkf$1@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>';
> >> > ?>[/color]
> >
> >I would also suggest that a requirement to query across databases means[/color][/color]
that[color=blue][color=green]
> >your database structure has been poorly designed.
> >[/color]
>
> 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[/color]
satellite[color=blue]
> databases, these all have a common schema but may be produced in house,
> or obtained from external sources.[/color]

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

re: MySQL Question

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" <paulus.magnus@loves-spam.com> wrote in message
news:bm8dlg$m2h$1@titan.btinternet.com...[color=blue]
>
> "James" <newsgroup@black-panther.freeserve.co.uk> wrote in message
> news:3f87b403.98111186@news.freeserve.com...[color=green]
> > On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
> > <paulus.magnus@loves-spam.com> scrawled:
> >[color=darkred]
> > >"Philip Ladin" <philipl@amalla.com> wrote in message
> > >news:R_Ehb.31144$Sn1.11518@bignews4.bellsouth.net ...
> > >> Thank you...
> > >> "Pedro" <hexkid@hotpop.com> wrote in message
> > >> news:bm75dt$jcgkf$1@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[/color][/color][/color]
will[color=blue][color=green][color=darkred]
> > >> 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[/color][/color][/color]
db2[color=blue][color=green][color=darkred]
> > >> > . "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[/color][/color]
> that[color=green][color=darkred]
> > >your database structure has been poorly designed.
> > >[/color]
> >
> > 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[/color]
> satellite[color=green]
> > databases, these all have a common schema but may be produced in house,
> > or obtained from external sources.[/color]
>
> 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
>
>[/color]