473,692 Members | 2,028 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Connecting two mysql tables from different databases

3 New Member
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 :)
Oct 11 '07 #1
18 50741
Lumpy
69 New Member
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.
Oct 12 '07 #2
gregerly
192 Recognized Expert New Member
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
Oct 12 '07 #3
Lumpy
69 New Member
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!
Oct 12 '07 #4
gregerly
192 Recognized Expert New Member
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
Oct 12 '07 #5
asi242
3 New Member
hello...thanks for the help


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

but i'll try your suggestions here..

thanks =)
Oct 13 '07 #6
asi242
3 New Member
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???



=)
Oct 13 '07 #7
brandoo
2 New Member
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);
?>
Nov 29 '07 #8
iuri
1 New Member
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
Aug 28 '08 #9
altugo
1 New Member
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

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. //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.
  4.  
  5. //MySQL Server 1
  6. $dbhost1 = "127.0.0.1";
  7. $dbuser1 = "dbuser1";
  8. $dbpassword1 = "dbpass1";
  9. $db1 = "database1";
  10. $connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());
  11. mysql_select_db($db1,$connection1);
  12.  
  13. //MySQL Server 2
  14. $dbhost2 = "xxx.xxx.xxx.xxx";
  15. $dbuser2 = "dbuser2";
  16. $dbpassword2 = "dbpass2";
  17. $db2 = "database2";
  18. $connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());
  19. mysql_select_db($db2,$connection2);    
  20.  
  21. //The SQL statement
  22. $sql =" SELECT database1.tablename1.fieldname1 AS field1, database2.tablename2.fieldname2 AS field2 FROM database1.tablename1,database2.tablename2";
  23.  
  24. //Execute query and collect results in $results
  25. $results = mysql_query($sql);
  26.  
  27. //Print result until end of records
  28. while($rows = mysql_fetch_array($results)){
  29.     print $rows["field1"]." | ".$rows["field2"]."<br>";
  30. }
  31.  
  32. ?>
Dec 3 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1928
by: UgoWeb | last post by:
Is it possible to execute a query between tables on different databases? thanx in advance
2
7351
by: Kenneth Fosse | last post by:
Hi. I'm currently working on a project which involves the creation of a web page which reports selected data to customers from two back-end systems. I use ASP script language on IIS, the server is MS SQL 2000. Now I'm struggling with combining two tables from the different databases. I'm sure it's simple enough, but I'm a little short on the SQL expertise. I've got two databases, db1 and db2, and then two tables, db1.t1 and
1
4201
by: Prakash RudraRaju | last post by:
Hi, I have recently migrated MSAccess Tables (nearly 120) to MySQL. To test successful migration I want to compare all tables. I have linked MySQL tables through ODBC connection. I want to create a report that compares all records between all tables in both databases. I am looking for a report with differences in number of records and differences in fields if 2 records are different. Probabaly it can be acheived through query, but I...
1
2631
by: nights | last post by:
hi! i'm just a newbie with php and mysql... and i'm having problems joining tables from different databases... can you please help me? here's my query: SELECT database1.table1.field1, database1.table1.field2, database1.table1.field3, database1.table1.field4, database1.table1.field5, database2.table1.field1, database2.table1.field2
2
2959
by: =?Utf-8?B?VGVycnk=?= | last post by:
Is it possible to join tables from different databases? If so, what is the format of the select statement? Both databases are on the same server. TIA, -- Terry
0
1519
by: data monkey | last post by:
My company's web application uses mySQL databases. I have linked these tables (Read only) into Access 2007 so I can create queries and reports. Now I need to extract some of the content from these linked tables so that I may do a mail merge into Word. Unfortunately, I find that many of the fields are being truncated. I tried importing the tables and still find the fields are truncated. So neither importing or linking is bringing in the...
3
3366
by: alter.fire | last post by:
Hey im trying to do the following in PHP: Connect to 2 different databases, one named complete and one incomplete. My goal is to find whats missing in the "incomplete" database. The fields i want to compare in the database/table is named 'name' in both. This field is comtaining unique names
4
4097
by: sganeshsvk | last post by:
sir, i want to store the same data values in two different databases at that same time in mysql using php programming. suppose any one databases data will lose then we use the other databases. suppose there is any query for store the same data values in two different databases at the same time. for eg: databases sample1
2
4126
by: neerja khattar | last post by:
I have total 3 databases.One is mysql and other 2 are of access type. I have 10 tables in each of the databases. I want to sync 2 tables from both access databases with mysql tables. whatever updates are happening in access databases tables it should come in mysql tables 1. student- table name 2. school - table name both are in database 1
0
8600
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8535
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9079
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8954
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8794
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8796
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7626
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6458
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
2968
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.