MySQL Question
This is a discussion on MySQL Question within the PHP Language forums, part of the PHP Programming Forums category; I am using MYSQL and have multiple databases. I can write code to connect to one database or the other. ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
10-10-2003
|
|||
|
|||
Re: MySQL Question
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. 
|
|
10-10-2003
|
|||
|
|||
|
Re: MySQL Question
Thank you...
"Michael Rasmussen" <mir@datanom.net> wrote in message news:pan.2003.10.10.19.55.20.227967@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. >
|
|
10-10-2003
|
|||
|
|||
|
Re: MySQL Question
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.
|
|
10-10-2003
|
|||
|
|||
|
Re: MySQL Question
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>'; > ?> > > -- > 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.
|
|
10-11-2003
|
|||
|
|||
|
Re: MySQL Question
"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 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
|
|
10-11-2003
|
|||
|
|||
|
Re: MySQL Question
On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus"
<paulus.magnus@loves-spam.com> scrawled: >"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 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.
|
|
10-11-2003
|
|||
|
|||
|
Re: MySQL Question
"James" <newsgroup@black-panther.freeserve.co.uk> wrote in message news:3f87b403.98111186@news.freeserve.com... > On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus" > <paulus.magnus@loves-spam.com> scrawled: > > >"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 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. 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
|
|
10-14-2003
|
|||
|
|||
|
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... > > "James" <newsgroup@black-panther.freeserve.co.uk> wrote in message > news:3f87b403.98111186@news.freeserve.com... > > On Sat, 11 Oct 2003 06:53:44 +0000 (UTC), "Paulus Magnus" > > <paulus.magnus@loves-spam.com> scrawled: > > > > >"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 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. > > 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 > >
|
Linear Mode
