Page 1 of 1

Help with switching between 2 open databases.

Posted: Sun Aug 09, 2009 2:09 pm
by Columbo
Hi,

I'm new to this forum and I thought I would try to get a bit of help here.

I am trying to use two databases in the same app. I am trying to read specific columns from one table in database1 and then insert that data into database2.
I am using PHP 5 and MySQL 5.

I am able to retrieve the data from database1 but I can’t get it to insert into database2.
The problem seems to be using two databases at the same time and selecting which one to use.

I open the two databases and read the data from database1 which works fine but when I try to select database2 and insert the data, I get an error that says: ‘Table MyTable2 doesn't exist’. I am assuming that is because it is looking at the wrong database for the table. (It didn't switch database?)

I have tried using the USE $database2 and also tried using mysql_select_db($database2, $db_handle2); but I must be doing it incorrectly if it can’t find the table. The code I am using is as follows:

Code: Select all

 
$username2="myname"; 
$password2="password"; 
$dbname2="database2"; 
$server2="localhost:/tmp/mysql5.sock"; 
 
$db_handle2=mysql_connect($server2, $username2, $password2); 
mysql_select_db($dbname2,$db_handle2); 
 
$username1="myname"; 
$password1="password"; 
$dbname1="database1"; 
$server1="localhost:/tmp/mysql5.sock"; 
 
$db_handle1=mysql_connect($server1, $username1, $password1); 
mysql_select_db($dbname1,$db_handle1); 
 
$sql="SELECT TITLE, DESCRIPTION, URL, CATEGORY_ID, OWNER_NAME, OWNER_EMAIL FROM MYTABLE1"; 
 
$result=mysql_query($sql) or die(mysql_error()); 
 
while($row=mysql_fetch_array($result)) { 
 
$siteTitle=$row['TITLE']; 
$description=$row['DESCRIPTION']; 
$url=$row['URL']; 
$cat=$row['CATEGORY_ID']; 
$webmasterEmail=$row['OWNER_NAME']; 
$webmasterName=$row['OWNER_EMAIL']; 
USE $database2; //Also tried mysql_select_db($dbname2,$db_handle2); 
 
 
$sql=”INSERT INTO MyTable2($siteTitle, $description, $url, $cat, $webmasterEmail, $webmasterName) VALUES (‘”.$siteTitle.”’, ‘”.$description.”’, ’”.$url.”’, ‘”.$cat.”’, ‘”.$webmasterEmail.”’, ‘”.$webmasterName.”’)"; 
 
$result=mysql_query($sql) or die(mysql_error()); 
 
I keep getting the ‘Table MyTable2' doesn't exist’ error.

Can anyone give me a short piece of sample code to show me how to correctly open and switch back and forth between two databases? I would really appreciate it.

Thanks.

Re: Help with switching between 2 open databases.

Posted: Sun Aug 09, 2009 2:17 pm
by Aravinthan

Re: Help with switching between 2 open databases.

Posted: Sun Aug 09, 2009 10:08 pm
by Columbo
To be honest, I'm not that familiar with the JOIN keyword but looking at explanations of its use on w3schools and other sites, it seems to be used to join 2 tables within the same database with matching columns. In my situation the tables are in two different databases and do not have a matching column. That is why I have to retrieve the data from one database, manipulate the data and then insert it into the second database. The problem is figuring out how to properly switch between the two databases.

Re: Help with switching between 2 open databases.

Posted: Mon Aug 10, 2009 11:15 am
by pickle
As the docs state, mysql_connect() will use the last open connection if none is specified. So if you want to insert into your second database, you need to pass the handle to that database as the second argument to mysql_query().

Re: Help with switching between 2 open databases.

Posted: Tue Aug 11, 2009 10:31 am
by Columbo
pickle wrote:As the docs state, mysql_connect() will use the last open connection if none is specified. So if you want to insert into your second database, you need to pass the handle to that database as the second argument to mysql_query().
Thank you very much. That did it! Rather than $result=mysql_query($query); I used $result=mysql_query($query, $database2);

Thanks.