[RESOLVED] 2 database connections

[neroag]

Hi im having a problem connecting to 2 database one remote & one local
i need to be able to access tables of the same name on both databases as shown below however making the 2 connections when i try to access the 2nd database its giving the results of the 1st despite them being separate connections

db1 get results of table1
whilst collecting the data check the result against db2 table1
if the result isnt in db2 then added the result to db2
else move on to next item in db1

i can collect the data no issues its just the "check the result against db2 table1" is returning the same results as db1's table1

any help would really appreciated thanks

[bradgrafelman]

Can you show us the code? If you're using procedural code (e.g. not OOP) with two simultaneous connections, you'll want to pass the appropriate connection resource each time you query a DB.

Can't really say more without seeing the code you're using (you didn't even tell us which extension/class you're using to interface with the DBMS).

[neroag]

Thank you for the reply, i have truncated it slightly for you but you should be able to get the idea from this

thank you again for your time

<?php

$sql_host ='aaaaaaaaaaaa';
$sql_user ='aaaaaaaaaaa';
$sql_db ='aaaaaaaaaaa';
$sql_password ='aaaaaaaaa';
///////// START GET PRODUCT DETAILS /////////////////
$connection = mysql_connect($sql_host, $sql_user, $sql_password) or die(mysql_error());
	mysql_select_db($sql_db, $connection) or die(mysql_error());
$query = "SELECT * FROM products";
$result = mysql_query($query) or die(mysql_error());
$num=mysql_numrows($result);
	$affected_rows = mysql_num_rows($result);
$i=0;
while ($i < $num) {
$productid = mysql_result($result,$i,"productid");
$productcode = mysql_result($result,$i,"productcode");
$keywords = mysql_result($result,$i,"keywords");
$importid = mysql_result($result,$i,"importid");




////// SECOND CONNECTION /////
$sql_hostx ='xxxxxxxxxxxx';
$sql_userx ='xxxxxxxxxxxx';
$sql_dbx ='xxxxxxxxxxxxxx';
$sql_passwordx ='xxxxxxxxxxxx';
$connection2 = mysql_connect($sql_hostx, $sql_userx, $sql_passwordx) or die(mysql_error());
	mysql_select_db($sql_dbx, $connection2) or die(mysql_error());

$check_products = mysql_query("SELECT * FROM products where importid = '$importid'");
......

if the products in do this (add it) ....

else 


do this (update it ).....


$i++;

}
?>

[bradgrafelman]

See my reply above for the solution:

bradgrafelman;10996114 wrote:

If you're using procedural code (e.g. not OOP) with two simultaneous connections, you'll want to pass the appropriate connection resource each time you query a DB.

and then see the manual for [man]mysql_query/man to learn how to specify which connection should be used.

[neroag]

Thanks Brad

Ive added the connection to the each of the db connections i need & i still cant see why it doesnt work, I have also looked at the mysql_query() as you suggest but I cant seem to see anything that may be of help

[bradgrafelman]

Can you show us an updated version of your code now that you've made changes to it?

[neroag]

Thanks Brad, Ive Truncated again

///////// REMOTE CONNECTION /////////////////

$connection = mysql_connect('xxx.xxx.xxx.xxx', 'aaaaaaaaaa', 'aaaaaaaaaaa');
	mysql_select_db('aaaaaaaa', $connection) or die(mysql_error());
$query = "SELECT * FROM products"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
	echo $row['productid'];
	echo "<br />";
$remote_productid = $row['productid'];
$remote_importid = $row['importid'];


///////// LOCAL CONNECTION  /////////////////

$connection_local = mysql_connect('zzz.zzz.zzz.zzz', 'bbbbbbbbbbb', 'bbbbbbbbb');
	mysql_select_db('bbbbbbbb', $connection_local) or die(mysql_error());
$query_local = "SELECT * FROM products WHERE importid = $remote_importid"; 
$result_local = mysql_query($query_local) or die(mysql_error());
$row_local = mysql_fetch_array($result_local) or die(mysql_error());
$test = $row_local['productid'];

if ($test = ""){
echo "add";
}else{
echo "update";
}


}

[neroag]

Ahhhhh it would happen to be ..

$query_local = "SELECT * FROM DATABASE.products WHERE importid = $remote_importid"; 

instead of the ..

$query_local = "SELECT * FROM products WHERE importid = $remote_importid"; 

would it ?

[bradgrafelman]

The name of the database has nothing to do with which connection resource gets used when a query is issued.

Again, you should be passing the correct connection resource when calling [man]mysql_query/man (or any other mysql_*() function, really), yet you still aren't doing that. Take a look at the manual page for [man]mysql_query/man, especially the optional second parameter.

[neroag]

Thanks Brad

I found it in the end ..

$result_local = mysql_query($query_local,$connection) or die(mysql_error()); 

instead of ..

$result_local = mysql_query($query_local) or die(mysql_error()); 

All works a treat now, thank you again.

[johanafm]

However, if you are using two separate schemas (usually referred to as databases) on the same server (usually referred to as database), then you don't need two connections as long as you either specify which schema to use in your queries as you yourself suggested above

schemaname.table.field

or switch the default schema to use before querying the other one

-- either SQL code
USE schemaname

-- or using php functions
$mysqli->select_db()

[Derokorian]

Also note, you should no longer be using the mysql extension but should instead be using mysqli (the i stands for improved) or PDO.