How do you connect to multiple MySQL databases on a single webpage

Question

I have information spread out across a few databases and want to put all the information onto one webpage using PHP   I was wondering how I can connect to multiple databases on a single PHP webpage   I know how to connect to a single database using    dbh   mysql connect  hostname   username   password           or die  Unable to connect to MySQL      However  can I just use multiple  mysql connect  commands to open the other databases  and how would PHP know what database I want the information pulled from if I do have multiple databases connected

User · Answer

if you are using mysqli and have two db connection file  like first one is  define  HOST   localhost    define  USER   user    define  PASS   passs    define    DB1     database name1      connMitra   new mysqli HOST  USER  PASS    DB1       second one is      define  HOST   localhost        define  USER   user        define  PASS   passs        define    DB2     database name1          connMitra   new mysqli HOST  USER  PASS    DB2       SO just change the name of parameter pass in mysqli like DB1 and DB2   if you pass same parameter in mysqli suppose DB1 in both file then second database will no connect any more  So remember when you use two or more connection pass different parameter name in mysqli function

User · Answer

If you use PHP5  And you should  given that PHP4 has been deprecated   you should use PDO  since this is slowly becoming the new standard  One  very  important benefit of PDO  is that it supports bound parameters  which makes for much more secure code   You would connect through PDO  like this   try      db   new PDO  mysql dbname databasename host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage         Of course replace databasename  username and password above   You can then query the database like this    result    db- gt query  select   from tablename    foreach   result as  row      echo  row  foo       n       Or  if you have variables    stmt    db- gt prepare  select   from tablename where id    id     stmt- gt execute array   id    gt  42     row    stmt- gt fetch      If you need multiple connections open at once  you can simply create multiple instances of PDO   try      db1   new PDO  mysql dbname databas1 host 127 0 0 1    username    password       db2   new PDO  mysql dbname databas2 host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage

User · Answer

If you use PHP5  And you should  given that PHP4 has been deprecated   you should use PDO  since this is slowly becoming the new standard  One  very  important benefit of PDO  is that it supports bound parameters  which makes for much more secure code   You would connect through PDO  like this   try      db   new PDO  mysql dbname databasename host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage         Of course replace databasename  username and password above   You can then query the database like this    result    db- gt query  select   from tablename    foreach   result as  row      echo  row  foo       n       Or  if you have variables    stmt    db- gt prepare  select   from tablename where id    id     stmt- gt execute array   id    gt  42     row    stmt- gt fetch      If you need multiple connections open at once  you can simply create multiple instances of PDO   try      db1   new PDO  mysql dbname databas1 host 127 0 0 1    username    password       db2   new PDO  mysql dbname databas2 host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage

User · Answer

lt  php        Sapan Mohanty        Skype sapan mohannty                                                oldData   mysql connect  localhost    DBUSER    DBPASS        echo mysql error         NewData   mysql connect  localhost    DBUSER    DBPASS        echo mysql error        mysql select db  OLDDBNAME    oldData        mysql select db  NEWDBNAME    NewData         getAllTablesName       SELECT table name FROM information schema tables WHERE table type    base table         getAllTablesNameExe   mysql query  getAllTablesName         echo mysql error        while   dataTableName   mysql fetch object  getAllTablesNameExe               oldDataCount         mysql query  select count    as noOfRecord from      dataTableName- gt table name   oldData            oldDataCountResult   mysql fetch object  oldDataCount              newDataCount         mysql query  select count    as noOfRecord from      dataTableName- gt table name   NewData            newDataCountResult   mysql fetch object  newDataCount            if    oldDataCountResult- gt noOfRecord     newDataCountResult- gt noOfRecord                 echo   lt br  gt  lt b gt      dataTableName- gt table name     lt  b gt                echo     Old       oldDataCountResult- gt noOfRecord              echo     New       newDataCountResult- gt noOfRecord               if   oldDataCountResult- gt noOfRecord  lt   newDataCountResult- gt noOfRecord                    echo      lt font color  green  gt   lt  font gt                   else                   echo      lt font color  red  gt   lt  font gt                               echo   lt br  gt ----------------------------------------                                gt

User · Answer

Unless you really need to have more than one instance of a PDO object in play  consider the following    con   new PDO  mysql host localhost    username   password         array PDO  ATTR PERSISTENT   gt  true      Notice the absence of dbname  in the construction arguments    When you connect to MySQL via a terminal or other tool  the database name is not needed off the bat  You can switch between databases by using the USE dbname statement via the PDO  exec   method    con- gt exec  USE someDatabase     con- gt exec  USE anotherDatabase      Of course you may want to wrap this in a catch try statement

User · Answer

You might be able to use MySQLi syntax  which would allow you to handle it better   Define the database connections  then whenever you want to query one of the database  specify the right connection   E g     Db1   new mysqli   DB HOST   USERNAME   PASSWORD       1st database connection   Db2   new mysqli   DB HOST   USERNAME   PASSWORD       2nd database connection   Then to query them on the same page  use something like    query    Db1- gt query  select   from tablename    query2    Db2- gt query  select   from tablename   die   Db1- gt error      Changing to MySQLi in this way will help you

User · Answer

Warning   mysql xx functions are deprecated since php 5 5 and removed since php 7 0  see http   php net manual intro mysql php   use mysqli xx functions or see the answer below from  Troelskn    You can make multiple calls to mysql connect    but if the parameters are the same you need to pass true for the   new link   fourth  parameter  otherwise the same connection is reused  For example    dbh1   mysql connect  hostname   username   password     dbh2   mysql connect  hostname   username   password  true     mysql select db  database1    dbh1   mysql select db  database2    dbh2     Then to query database 1 pass the first link identifier   mysql query  select   from tablename    dbh1     and for database 2 pass the second   mysql query  select   from tablename    dbh2     If you do not pass a link identifier then the last connection created is used  in this case the one represented by  dbh2  e g     mysql query  select   from tablename      Other options  If the MySQL user has access to both databases and they are on the same host  i e  both DBs are accessible from the same connection  you could    Keep one connection open and call mysql select db   to swap between as necessary  I am not sure this is a clean solution and you could end up querying the wrong database  Specify the database name when you reference tables within your queries  e g  SELECT   FROM database2 tablename   This is likely to be a pain to implement    Also please read troelskn s answer because that is a better approach if you are able to use PDO rather than the older extensions

User · Answer

If you use PHP5  And you should  given that PHP4 has been deprecated   you should use PDO  since this is slowly becoming the new standard  One  very  important benefit of PDO  is that it supports bound parameters  which makes for much more secure code   You would connect through PDO  like this   try      db   new PDO  mysql dbname databasename host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage         Of course replace databasename  username and password above   You can then query the database like this    result    db- gt query  select   from tablename    foreach   result as  row      echo  row  foo       n       Or  if you have variables    stmt    db- gt prepare  select   from tablename where id    id     stmt- gt execute array   id    gt  42     row    stmt- gt fetch      If you need multiple connections open at once  you can simply create multiple instances of PDO   try      db1   new PDO  mysql dbname databas1 host 127 0 0 1    username    password       db2   new PDO  mysql dbname databas2 host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage

User · Answer

dbh1   mysql connect  hostname   username   password      dbh2   mysql connect  hostname   username   password  true     mysql select db  database1    dbh1    mysql select db  database2   dbh2     mysql query  select   from tablename    dbh1   mysql query  select   from tablename    dbh2     This is the most obvious solution that I use but just remember  if the username   password for both the database is exactly same in the same host  this solution will always be using the first connection  So don t be confused that this is not working in such case  What you need to do is  create 2 different users for the 2 databases and it will work

User · Answer

Warning   mysql xx functions are deprecated since php 5 5 and removed since php 7 0  see http   php net manual intro mysql php   use mysqli xx functions or see the answer below from  Troelskn    You can make multiple calls to mysql connect    but if the parameters are the same you need to pass true for the   new link   fourth  parameter  otherwise the same connection is reused  For example    dbh1   mysql connect  hostname   username   password     dbh2   mysql connect  hostname   username   password  true     mysql select db  database1    dbh1   mysql select db  database2    dbh2     Then to query database 1 pass the first link identifier   mysql query  select   from tablename    dbh1     and for database 2 pass the second   mysql query  select   from tablename    dbh2     If you do not pass a link identifier then the last connection created is used  in this case the one represented by  dbh2  e g     mysql query  select   from tablename      Other options  If the MySQL user has access to both databases and they are on the same host  i e  both DBs are accessible from the same connection  you could    Keep one connection open and call mysql select db   to swap between as necessary  I am not sure this is a clean solution and you could end up querying the wrong database  Specify the database name when you reference tables within your queries  e g  SELECT   FROM database2 tablename   This is likely to be a pain to implement    Also please read troelskn s answer because that is a better approach if you are able to use PDO rather than the older extensions

User · Answer

You don t actually need select db  You can send a query to two databases at the same time  First   give a grant to DB1 to select from DB2 by GRANT select ON DB2   TO DB1 localhost   Then  FLUSH PRIVILEGES   Finally  you are able to do  multiple-database query  like SELECT DB1 TABLE1 id  DB2 TABLE1 username FROM DB1 DB2 etc   Don t forget that you need  root  access to use grant command

User · Answer

Instead of mysql connect use mysqli connect   mysqli is provide a functionality for connect multiple database at a time    Db1   new mysqli  hostname  username  password  db name1       this is connection 1 for DB 1   Db2   new mysqli  hostname  username  password  db name2       this is connection 2 for DB 2

User · Answer

Warning   mysql xx functions are deprecated since php 5 5 and removed since php 7 0  see http   php net manual intro mysql php   use mysqli xx functions or see the answer below from  Troelskn    You can make multiple calls to mysql connect    but if the parameters are the same you need to pass true for the   new link   fourth  parameter  otherwise the same connection is reused  For example    dbh1   mysql connect  hostname   username   password     dbh2   mysql connect  hostname   username   password  true     mysql select db  database1    dbh1   mysql select db  database2    dbh2     Then to query database 1 pass the first link identifier   mysql query  select   from tablename    dbh1     and for database 2 pass the second   mysql query  select   from tablename    dbh2     If you do not pass a link identifier then the last connection created is used  in this case the one represented by  dbh2  e g     mysql query  select   from tablename      Other options  If the MySQL user has access to both databases and they are on the same host  i e  both DBs are accessible from the same connection  you could    Keep one connection open and call mysql select db   to swap between as necessary  I am not sure this is a clean solution and you could end up querying the wrong database  Specify the database name when you reference tables within your queries  e g  SELECT   FROM database2 tablename   This is likely to be a pain to implement    Also please read troelskn s answer because that is a better approach if you are able to use PDO rather than the older extensions

User · Answer

Try below code        conn   mysql connect  hostname   username   password        mysql select db  db1   conn       mysql select db  db2   conn         query1    SELECT   FROM db1 table        query2    SELECT   FROM db2 table     You can fetch data of above query from both database as below   rs   mysql query  query1   while  row   mysql fetch assoc  rs          data1      row      rs   mysql query  query2   while  row   mysql fetch assoc  rs          data2      row     print r  data1   print r  data2

User · Answer

dbh1   mysql connect  hostname   username   password      dbh2   mysql connect  hostname   username   password  true     mysql select db  database1    dbh1    mysql select db  database2   dbh2     mysql query  select   from tablename    dbh1   mysql query  select   from tablename    dbh2     This is the most obvious solution that I use but just remember  if the username   password for both the database is exactly same in the same host  this solution will always be using the first connection  So don t be confused that this is not working in such case  What you need to do is  create 2 different users for the 2 databases and it will work

User · Answer

If you use PHP5  And you should  given that PHP4 has been deprecated   you should use PDO  since this is slowly becoming the new standard  One  very  important benefit of PDO  is that it supports bound parameters  which makes for much more secure code   You would connect through PDO  like this   try      db   new PDO  mysql dbname databasename host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage         Of course replace databasename  username and password above   You can then query the database like this    result    db- gt query  select   from tablename    foreach   result as  row      echo  row  foo       n       Or  if you have variables    stmt    db- gt prepare  select   from tablename where id    id     stmt- gt execute array   id    gt  42     row    stmt- gt fetch      If you need multiple connections open at once  you can simply create multiple instances of PDO   try      db1   new PDO  mysql dbname databas1 host 127 0 0 1    username    password       db2   new PDO  mysql dbname databas2 host 127 0 0 1    username    password      catch  PDOException  ex      echo  Connection failed       ex- gt getMessage

User · Answer

Instead of mysql connect use mysqli connect   mysqli is provide a functionality for connect multiple database at a time    Db1   new mysqli  hostname  username  password  db name1       this is connection 1 for DB 1   Db2   new mysqli  hostname  username  password  db name2       this is connection 2 for DB 2

User · Answer

Warning   mysql xx functions are deprecated since php 5 5 and removed since php 7 0  see http   php net manual intro mysql php   use mysqli xx functions or see the answer below from  Troelskn    You can make multiple calls to mysql connect    but if the parameters are the same you need to pass true for the   new link   fourth  parameter  otherwise the same connection is reused  For example    dbh1   mysql connect  hostname   username   password     dbh2   mysql connect  hostname   username   password  true     mysql select db  database1    dbh1   mysql select db  database2    dbh2     Then to query database 1 pass the first link identifier   mysql query  select   from tablename    dbh1     and for database 2 pass the second   mysql query  select   from tablename    dbh2     If you do not pass a link identifier then the last connection created is used  in this case the one represented by  dbh2  e g     mysql query  select   from tablename      Other options  If the MySQL user has access to both databases and they are on the same host  i e  both DBs are accessible from the same connection  you could    Keep one connection open and call mysql select db   to swap between as necessary  I am not sure this is a clean solution and you could end up querying the wrong database  Specify the database name when you reference tables within your queries  e g  SELECT   FROM database2 tablename   This is likely to be a pain to implement    Also please read troelskn s answer because that is a better approach if you are able to use PDO rather than the older extensions

User · Answer

Try below code        conn   mysql connect  hostname   username   password        mysql select db  db1   conn       mysql select db  db2   conn         query1    SELECT   FROM db1 table        query2    SELECT   FROM db2 table     You can fetch data of above query from both database as below   rs   mysql query  query1   while  row   mysql fetch assoc  rs          data1      row      rs   mysql query  query2   while  row   mysql fetch assoc  rs          data2      row     print r  data1   print r  data2

User · Answer

Unless you really need to have more than one instance of a PDO object in play  consider the following    con   new PDO  mysql host localhost    username   password         array PDO  ATTR PERSISTENT   gt  true      Notice the absence of dbname  in the construction arguments    When you connect to MySQL via a terminal or other tool  the database name is not needed off the bat  You can switch between databases by using the USE dbname statement via the PDO  exec   method    con- gt exec  USE someDatabase     con- gt exec  USE anotherDatabase      Of course you may want to wrap this in a catch try statement

User · Answer

I just made my life simple   CREATE VIEW another table AS SELECT   FROM another database another table    hope it is helpful    cheers

User · Answer

You don t actually need select db  You can send a query to two databases at the same time  First   give a grant to DB1 to select from DB2 by GRANT select ON DB2   TO DB1 localhost   Then  FLUSH PRIVILEGES   Finally  you are able to do  multiple-database query  like SELECT DB1 TABLE1 id  DB2 TABLE1 username FROM DB1 DB2 etc   Don t forget that you need  root  access to use grant command

User · Answer

lt  php        Sapan Mohanty        Skype sapan mohannty                                                oldData   mysql connect  localhost    DBUSER    DBPASS        echo mysql error         NewData   mysql connect  localhost    DBUSER    DBPASS        echo mysql error        mysql select db  OLDDBNAME    oldData        mysql select db  NEWDBNAME    NewData         getAllTablesName       SELECT table name FROM information schema tables WHERE table type    base table         getAllTablesNameExe   mysql query  getAllTablesName         echo mysql error        while   dataTableName   mysql fetch object  getAllTablesNameExe               oldDataCount         mysql query  select count    as noOfRecord from      dataTableName- gt table name   oldData            oldDataCountResult   mysql fetch object  oldDataCount              newDataCount         mysql query  select count    as noOfRecord from      dataTableName- gt table name   NewData            newDataCountResult   mysql fetch object  newDataCount            if    oldDataCountResult- gt noOfRecord     newDataCountResult- gt noOfRecord                 echo   lt br  gt  lt b gt      dataTableName- gt table name     lt  b gt                echo     Old       oldDataCountResult- gt noOfRecord              echo     New       newDataCountResult- gt noOfRecord               if   oldDataCountResult- gt noOfRecord  lt   newDataCountResult- gt noOfRecord                    echo      lt font color  green  gt   lt  font gt                   else                   echo      lt font color  red  gt   lt  font gt                               echo   lt br  gt ----------------------------------------                                gt

User · Answer

You might be able to use MySQLi syntax  which would allow you to handle it better   Define the database connections  then whenever you want to query one of the database  specify the right connection   E g     Db1   new mysqli   DB HOST   USERNAME   PASSWORD       1st database connection   Db2   new mysqli   DB HOST   USERNAME   PASSWORD       2nd database connection   Then to query them on the same page  use something like    query    Db1- gt query  select   from tablename    query2    Db2- gt query  select   from tablename   die   Db1- gt error      Changing to MySQLi in this way will help you

User · Answer

I just made my life simple   CREATE VIEW another table AS SELECT   FROM another database another table    hope it is helpful    cheers

User · Answer

if you are using mysqli and have two db connection file  like first one is  define  HOST   localhost    define  USER   user    define  PASS   passs    define    DB1     database name1      connMitra   new mysqli HOST  USER  PASS    DB1       second one is      define  HOST   localhost        define  USER   user        define  PASS   passs        define    DB2     database name1          connMitra   new mysqli HOST  USER  PASS    DB2       SO just change the name of parameter pass in mysqli like DB1 and DB2   if you pass same parameter in mysqli suppose DB1 in both file then second database will no connect any more  So remember when you use two or more connection pass different parameter name in mysqli function

[php] How do you connect to multiple MySQL databases on a single webpage?

Examples related to php

Examples related to mysql