?
Solved

connecting to two mysql databases

Posted on 2007-08-07
8
Medium Priority
?
175 Views
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";
$result=mysql_query($query);
$num=mysql_numrows($result);

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$colour=mysql_result($result,$i,"colour");
$registration=mysql_result($result,$i,"registration");


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


++$i;
}
0
Comment
Question by:fox_statton
6 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 500 total points
ID: 19646349
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.
http://www.php.net/mysql
0
 
LVL 24

Assisted Solution

by:glcummins
glcummins earned 500 total points
ID: 19646376
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);
  $num=mysql_numrows($result);
 
Take a look at http://www.php.net/mysql_query for more information on using link identifiers.
0
 
LVL 3

Assisted Solution

by:Sisson
Sisson earned 500 total points
ID: 19649713
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:glcummins
ID: 19649778
@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.
0
 
LVL 3

Expert Comment

by:Sisson
ID: 19649844
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
0
 
LVL 4

Assisted Solution

by:dkaisla
dkaisla earned 500 total points
ID: 19651979
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)";
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

621 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