Connecting two mysql tables from different databases

[asi242]

Hello,

Good day to all...

I just want to ask.. How would you connect two tables from 2 different databases using PHP scripts? Because, one database that contains customer table is dedicated to one project, and then here comes another project that also uses the customer table...the clients dont want to reenter all of values from the first database to the second database. Dumping would not be an option cause they want the two projects to run in different databases... and whenever the user made changes to the customer table from the first database, it should also affect the customer table from the second database...


I need opinions now... I'm just starting to use php and mysql..


Thanks :)

[Lumpy]

I don't know if this would be the best way to do it, but I would look into developing two separate classes, one to work with each database. That way, anytime you would need to update the data, you could just send the data into each of the classes and each class would update the corresponding database. I know that you would be duplicating some code, and that kind of defeats the purpose of the class, but then again, the customer already is duplicating data in the databases and doesn't want to move to a single database.

[gregerly]

It's really not difficult to join seperate databases (assuming they reside on the same server) Just like you would specify fields using the "table.fiel d", you can also use "database.table .field" Below is an example of a two database join:

[PHP]$sql="SELECT db1.table1.some field, db2.table1.some field FROM db1.table1 INNER JOIN db2.table1 ON db1.table1.some id = db2.table1.some id WHERE db1.table1.some field = 'queryCrit';"[/PHP]

You simply write you query just like you would if you were working in one db, just use the dot notation to specify your databases as well.

Greg

[Lumpy]

That is a pretty neat function. Thank you for sharing. I am glad to have learned that it is possible to write out an SQL statement like that!

[gregerly]

That is a pretty neat function. Thank you for sharing. I am glad to have learned that it is possible to write out an SQL statement like that!

That's what were here for! Hope it helps!

Greg

[asi242]

hello...thanks for the help


unfortunately, they dont reside in the same server....

but i'll try your suggestions here..

thanks =)

[asi242]

hello, i have another question..


the first database already have some values stored in it..
how do you dump these values to the second database
using PHP scritps???



=)

[brandoo]

this script connects at two databases, each one located on a different server, and clones the source_table from the second database to the destination_tab le located on localhost (first database)


<?php
$host1="localho st"; // destination
$base1="first_d atabase";
$user1="root";
$password1="";

$host2="192.168 .0.1"; //source
$base2="second_ database";
$user2="root";
$password2="xxx ";



$conection1 = @mysql_connect( $host1, $user1, $password1)
or die("Error reaching destination<br> ".mysql_error() ." nr eroare: ".mysql_errno() );
print "Succesfuly connected 1! <br>";

$Db1 = @mysql_select_d b($base1, $conection1)
or die("Error reaching destination database:<br>" . mysql_error(). "<br>" . mysql_errno());
print "Database1 reached!<br>";




$conection2 = @mysql_connect( $host2, $user2, $password2)
or die("Error reaching source<br>".mys ql_error()." nr eroare: ".mysql_errno() );
print "Succesfuly connected 2!<br>";

$Db2 = @mysql_select_d b($baza2, $conexiune2)
or die("Error reaching source database:<br>" . mysql_error(). "<br>" . mysql_errno());
print "Database2 reached!!<br>";



$query = 'create table destination_tab le select * from second_database .source_table';
//echo "<br>".$query." <br>";
$result2 = mysql_query($qu ery2, $conection1) or die('Query failed: ' . mysql_error().' ||'.mysql_errno ());



mysql_close($co nection1);
mysql_close($co nection2);
?>

[iuri]

hi brandoo,

i tested you script. This thread is very good! Congratulations !

Although i get an error of mysql_fetch_arr ay when i try to use two databases.
With only one the script works pretty fine. But as soon as i add the second connection the error show up.

/##########
Warning: mysql_fetch_arr ay(): supplied argument is not a valid MySQL result resource
############/

The error is in the begin of the file

$dbhost1 = "xxxxx";
$dbhost2 = "localhost" ;
$dbuser = "xxx";
$dbpass = "xxxx";

$conn1 = mysql_connect($ dbhost1, $dbuser, $dbpass) or die('Error connecting to mysql');
$conn2 = mysql_connect($ dbhost2, $dbuser, $dbpass) or die('Error connecting to mysql');

$dbname1 = "db1";
$dbname2 = "db2";

mysql_select_db ($dbname1);
mysql_select_db ($dbname2);


$query = "SELECT * FROM artel.product_w ebcategory_x";
$result = mysql_query($qu ery);

while($row = mysql_fetch_arr ay($result, MYSQL_ASSOC))


i have no idea what could possibly be wrong. Could the reason be mysql setting turned off because of securioty issues?

iuri

[altugo]

Hi,
Here is my solution for multiple servers, connections and DBs.

- Two or more MySQL 5 db servers
- Two or more locations (local and/or anything else)
- Two or more different databases
- Two or more tables and fields

I tested this solution in a real life scenario. Works fine :-)

My original code was a little bit shorter and class based, but for the learning purposes I keep this example pure and simple. I think you can use and modify this very easily.

Cheers

<?php
 
//Define your database connections and select your database want to use. In this example I use two connections and two DBs. But you can use more than two.
 
//MySQL Server 1
$dbhost1 = "127.0.0.1";
$dbuser1 = "dbuser1";
$dbpassword1 = "dbpass1";
$db1 = "database1";
$connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());
mysql_select_db($db1,$connection1);
 
//MySQL Server 2
$dbhost2 = "xxx.xxx.xxx.xxx";
$dbuser2 = "dbuser2";
$dbpassword2 = "dbpass2";
$db2 = "database2";
$connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());
mysql_select_db($db2,$connection2);    
 
//The SQL statement
$sql =" SELECT database1.tablename1.fieldname1 AS field1, database2.tablename2.fieldname2 AS field2 FROM database1.tablename1,database2.tablename2";
 
//Execute query and collect results in $results
$results = mysql_query($sql);
 
//Print result until end of records
while($rows = mysql_fetch_array($results)){
    print $rows["field1"]." | ".$rows["field2"]."<br>";
}
 
?>