Help with switching between 2 open databases.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Columbo
Forum Newbie
Posts: 5
Joined: Sun Aug 09, 2009 1:55 pm

Help with switching between 2 open databases.

Post 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.
Aravinthan
Forum Commoner
Posts: 84
Joined: Mon Jan 28, 2008 6:34 pm

Re: Help with switching between 2 open databases.

Post by Aravinthan »

Columbo
Forum Newbie
Posts: 5
Joined: Sun Aug 09, 2009 1:55 pm

Re: Help with switching between 2 open databases.

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Help with switching between 2 open databases.

Post 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().
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Columbo
Forum Newbie
Posts: 5
Joined: Sun Aug 09, 2009 1:55 pm

Re: Help with switching between 2 open databases.

Post 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.
Post Reply