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 :)
18 50703
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.
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.field", you can also use "database.table.field" Below is an example of a two database join:
[PHP]$sql="SELECT db1.table1.somefield, db2.table1.somefield FROM db1.table1 INNER JOIN db2.table1 ON db1.table1.someid = db2.table1.someid WHERE db1.table1.somefield = '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
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 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
hello...thanks for the help
unfortunately, they dont reside in the same server....
but i'll try your suggestions here..
thanks =)
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???
=)
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_table located on localhost (first database)
<?php
$host1="localhost"; // destination
$base1="first_database";
$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_db($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>".mysql_error()." nr eroare: ".mysql_errno());
print "Succesfuly connected 2!<br>";
$Db2 = @mysql_select_db($baza2, $conexiune2)
or die("Error reaching source database:<br>" . mysql_error(). "<br>" . mysql_errno());
print "Database2 reached!!<br>";
$query = 'create table destination_table select * from second_database.source_table';
//echo "<br>".$query."<br>";
$result2 = mysql_query($query2, $conection1) or die('Query failed: ' . mysql_error().'||'.mysql_errno());
mysql_close($conection1);
mysql_close($conection2);
?>
hi brandoo,
i tested you script. This thread is very good! Congratulations!
Although i get an error of mysql_fetch_array 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_array(): 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_webcategory_x";
$result = mysql_query($query);
while($row = mysql_fetch_array($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
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>";
-
}
-
-
?>
hi Altugo,
I actually liked your solution.. but its not exactly working out in my case...
Like you, I am also connecting to 2 mySQL databaes on 2 different servers..but in my Query, I need to Select from one database and insert into another using the below SQL
REPLACE INTO db1.table1 SELECT * FROM db2.table2 WHERE table2.WhenCreated > table2.LastSynchedTimeStamp ;
but when I am running my PHP, its appending the database name twice and hence failing...
here is my PHP code
$insertQuery = "REPLACE INTO ". $database2.$row2[0] . " SELECT * FROM " . $database1.$row2[0] . " WHERE WhenCreated > LastSynchedTimeStamp" ;
Please help
if there are to different database (servers), there is no way to do that in a single query.
Hi Dormilich,
Can you please suggest how to do this in one script? If not single query, is this possible with more queries?
As written above we have a requirement to connect multiple databases & many tables in each database from one host server and transmit data to different server tables. In this process we want to pass database names & table names dynamically.
Any suggestions or pointers to achieve this will be really appreciated.
you can create a database connection variable/instance for every/as many database servers as you have … after that it’s just passing data around
Yes I am doing same.. Creating database connection variable for each database server and trying to use these connections in a query..
It is not allowing to use two database server connections in single query..
It is not allowing to use two database server connections in single query..
that is absolutely correct.
@pkul3003
You must to use FEDERATED tables if you want to make JOIN with tables in different servers. You can search for this in MySQL docs( http://dev.mysql.com/doc/refman/5.0/...rated-use.html)
But be careful! The performance is not good. If you really need to do that you must prepare your servers rightly. Read the docs and share it with the infrastructure staff and network.
Cheers!
Well - nuff said by all the others but how about adding real mysql replication if all you wanna do is to sync records with different timestamps from server 1 to server 2?
Or - if this won't be possible in your infrastructure, go the SLOW way withoud federated tables and do something like this:
1) find a way to store a "last run" timestamp
2) select all records from server 1 where the timestamp is > the last run timestamp
3) in the while( ... ) loop execute your replace into ... to server 2 sql with the data read from server 1.
Depending on the amount of records and the frequency of synch ... but i would always prefer the build in replication (master/slave)
just you have to create two handle with two connection. If you can to select two databases with two different handle. If you want to access the particular db value depending upon the handle
Sign in to post your reply or Sign up for a free account.
Similar topics
by: UgoWeb |
last post by:
Is it possible to execute a query between tables on different databases?
thanx in advance
|
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...
|
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...
|
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,...
|
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
|
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...
|
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...
|
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...
|
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...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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,...
|
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...
| |