MySQL Question

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Philip Ladin

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




  • Michael Rasmussen

    #2
    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($li nk);
    $link2 = mysql_connect(" localhost", "mysql_user ", "mysql_password ")
    or die("Could not connect: " . mysql_error());
    print ("Connected successfully");
    mysql_close($li nk);
    mysql_select_db ("foo",$link 1);
    mysql_select_db ("bar",$link 2);

    mysql_query("se lect * from table1", $link1);
    mysql_query("se lect * 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.

    Comment

    • Philip Ladin

      #3
      Re: MySQL Question

      Thank you...

      "Michael Rasmussen" <mir@datanom.ne t> wrote in message
      news:pan.2003.1 0.10.19.55.20.2 27967@datanom.n et...[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($li nk);
      > $link2 = mysql_connect(" localhost", "mysql_user ", "mysql_password ")
      > or die("Could not connect: " . mysql_error());
      > print ("Connected successfully");
      > mysql_close($li nk);
      > mysql_select_db ("foo",$link 1);
      > mysql_select_db ("bar",$link 2);
      >
      > mysql_query("se lect * from table1", $link1);
      > mysql_query("se lect * 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]


      Comment

      • Pedro

        #4
        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($sq l);
        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.

        Comment

        • Philip Ladin

          #5
          Re: MySQL Question

          Thank you...
          "Pedro" <hexkid@hotpop. com> wrote in message
          news:bm75dt$jcg kf$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($sq l);
          > 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]


          Comment

          • Paulus Magnus

            #6
            Re: MySQL Question

            "Philip Ladin" <philipl@amalla .com> wrote in message
            news:R_Ehb.3114 4$Sn1.11518@big news4.bellsouth .net...[color=blue]
            > Thank you...
            > "Pedro" <hexkid@hotpop. com> wrote in message
            > news:bm75dt$jcg kf$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($sq l);
            > > 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


            Comment

            • James

              #7
              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.311 44$Sn1.11518@bi gnews4.bellsout h.net...[color=green]
              >> Thank you...
              >> "Pedro" <hexkid@hotpop. com> wrote in message
              >> news:bm75dt$jcg kf$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($sq l);
              >> > 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.


              Comment

              • Paulus Magnus

                #8
                Re: MySQL Question


                "James" <newsgroup@blac k-panther.freeser ve.co.uk> wrote in message
                news:3f87b403.9 8111186@news.fr eeserve.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.311 44$Sn1.11518@bi gnews4.bellsout h.net...[color=darkred]
                > >> Thank you...
                > >> "Pedro" <hexkid@hotpop. com> wrote in message
                > >> news:bm75dt$jcg kf$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($sq l);
                > >> > 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


                Comment

                • Philip Ladin

                  #9
                  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.btinte rnet.com...[color=blue]
                  >
                  > "James" <newsgroup@blac k-panther.freeser ve.co.uk> wrote in message
                  > news:3f87b403.9 8111186@news.fr eeserve.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.311 44$Sn1.11518@bi gnews4.bellsout h.net...
                  > > >> Thank you...
                  > > >> "Pedro" <hexkid@hotpop. com> wrote in message
                  > > >> news:bm75dt$jcg kf$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($sq l);
                  > > >> > 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]


                  Comment

                  Working...