connecting to two mysql databases

Posted on 2007-08-07
Last Modified: 2013-12-12
Hi guys,
Ive got the following PHP, the problem is that the "first_database" and the "another_database" are tables in two different databases. Does anyone know how I can have two connections open at the same time and define which connection is used for each query. At the moment the only idea I have is to open and close the connections, but that seems like a waste

$query="SELECT * FROM first_database";

while ($i < $num) {

$query = "INSERT INTO another_table (id, colour, registration) VALUES ('$id', '$colour','$registration')";

Question by:fox_statton
    LVL 48

    Accepted Solution

    Nearly all mysql_* functions have a $link parameter where you can specify which database connection to use when running that function. See the docu of the different function at which position to place the link. If that link parameter is skipped, the last link created with mysql_connect is used.
    LVL 24

    Assisted Solution

    Each mysql_* command can take a second parameter which is the link identifier. When you create the connections, assign a link identifier like this;

      $link1 = mysql_connect(<server>, <username>, <password>);
      $link2 = mysql_connect(<server>, <username>, <password>);

    Then specific which link to use:

      $query="SELECT * FROM first_database";
      $result=mysql_query($query, $link1);
    Take a look at for more information on using link identifiers.
    LVL 3

    Assisted Solution

    In your FROM clause you should be able to specify `database`.`table` for selecting from multiple or different databases.

    Its what i do when i need to accomplish what you said; i hate opening up new links
    LVL 24

    Expert Comment

    @Sisson: You can do that for some types of queries, but it does not work in every instance. For example, if you use mysql_insert_id(), how would you control from which database the previous insert_id is returned?

    Using multiple links is an extra step, but you don't have to worry about confusion or mistakes in the queries themselves.
    LVL 3

    Expert Comment

    Yes, but mysql_insert_id() is not really the best function to user.
    a better way is the SQL function LAST_INSERT_ID()

    SELECT LAST_INSERT_ID() as lid FROM db.table
    LVL 4

    Assisted Solution

    You can try creating a username in your mysql/phpmyadmin with access on both databases. Then, after connecting to the databases, you can just specify which database to use in your query:

    1. Connect to your mysql using the username that can access the two databases:
     mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

    2. Use a specific database in your query:
    $query = "SELECT * FROM db1.table1";

    3. Using the other database:
    $query = "INSERT INTO db2.table2 (field1) VALUES (value1)";

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    A colleague recently asked me about how to give his client a small part of the web site that could be completely under the client's control.  Since I have done this sort of thing before to add emergency banners to a web site, I decided I would creat…
    Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now