asked on

connecting to two mysql databases

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;
}

ASKER CERTIFIED SOLUTION

hernst42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

glcummins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sisson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

glcummins

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

Sisson

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

SOLUTION

dkaisla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial