Solutionabc
asked on
Mysql How to insert into two different tables located in two different databases?
Hi,
I have been looking online and cannot find the answer as how to insert into two different tables located in two different databases.
I am using PHP and mySQL, any help is appreciated.
thanks.
I have been looking online and cannot find the answer as how to insert into two different tables located in two different databases.
I am using PHP and mySQL, any help is appreciated.
thanks.
you can do it with php, just create 2 connection scripts and make the connection for every db insert the record and then make the same with the other.
ASKER
can you give an example?
I tried but I am getting an error.
I tried but I am getting an error.
Something like this:
//-----CONNECT TO THE DATABASE
$dbh=mysql_connect ("YOUR HOST", "USER", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("YOURDB");
//------END CONNECT
$queryupclkls="UPDATE lsw_tsites SET nHitsInTotal = '$clickhitsintot' WHERE nId = '$row[nId]' ";
mysql_query($queryupclkls) or die("Data error:<br>\nSQL: <b>$queryupclkls</b>");
//-----CONNECT TO THE DATABASE
$dbh=mysql_connect ("YOUR HOST", "USER", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("YOURDB");
//------END CONNECT
$queryupclkls="UPDATE lsw_tsites SET nHitsInTotal = '$clickhitsintot' WHERE nId = '$row[nId]' ";
mysql_query($queryupclkls)
you want something like this:
// replace the following values with your own
$host1 = 'db1.host.com';
$user1 = 'user1';
$pass1 = 'pass1';
$db_name1 = 'db_name1';
$host2 = 'db2.host2.com';
$user2 = 'user2';
$pass2 = 'pass2';
$db_name2 = 'db_name2';
$db = mysql_connect($host1, $user1, $pass1);
mysql_select_db($db_name1) ;
$query = "insert into table1 (field1, field2, field3) values ('value1', 'value2', 'value3')";
$result = mysql_query($query);
$db = mysql_connect($host2, $user2, $pass2);
mysql_select_db($db_name2) ;
$query = "insert into table2 (field1, field2, field3) values ('value1', 'value2', 'value3')";
$result = mysql_query($query);
// replace the following values with your own
$host1 = 'db1.host.com';
$user1 = 'user1';
$pass1 = 'pass1';
$db_name1 = 'db_name1';
$host2 = 'db2.host2.com';
$user2 = 'user2';
$pass2 = 'pass2';
$db_name2 = 'db_name2';
$db = mysql_connect($host1, $user1, $pass1);
mysql_select_db($db_name1)
$query = "insert into table1 (field1, field2, field3) values ('value1', 'value2', 'value3')";
$result = mysql_query($query);
$db = mysql_connect($host2, $user2, $pass2);
mysql_select_db($db_name2)
$query = "insert into table2 (field1, field2, field3) values ('value1', 'value2', 'value3')";
$result = mysql_query($query);
ASKER
So your saying that I would use each code twice but different db username and password?
yes merwetta1 comment is right
ASKER
I tried that and it says:
Table 'singers.dance' doesn't exist
Table 'singers.dance' doesn't exist
ASKER
It is looking in the wrong database for the table.
I tested the code and it seems to work for me. Please post your version of my code with passwords "X"d out. Are you using 2 different databases on the same db server, or different hosts?
ASKER
This is what I have.
$con2 = mysql_connect('localhost', 'user2', 'pass2');
if (!$con2)
{
die('Could not connect: ' . mysql_error());
}
$con = mysql_connect('localhost', 'user', 'pass');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_query("UPDATE ORDERS
SET Status = 'Accepted' WHERE Username = '$slusername' AND Order_ID = '".$id."' ")
or die(mysql_error());
mysql_query("INSERT INTO $slusername (start_date, end_date, username, user_defined) VALUES('$due','$due','$slusername','1')")
or die(mysql_error());
ASKER
oh I forgot to include this in the posting:
mysql_select_db("db1", $con);
mysql_select_db("db2r", $con2);
mysql_select_db("db1", $con);
mysql_select_db("db2r", $con2);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you have the same username and password you should be able to use just one connection by prefixing table name with the database name.
i.e.
INSERT INTO db1.table1 (field1, field2, field3) VALUES (value1, value2, value3)
INSERT INTO db2.table2 (field1, field2, field3) VALUES (value1, value2, value3)
Where db1 is your first database name and db2 is the other database.
i.e.
INSERT INTO db1.table1 (field1, field2, field3) VALUES (value1, value2, value3)
INSERT INTO db2.table2 (field1, field2, field3) VALUES (value1, value2, value3)
Where db1 is your first database name and db2 is the other database.
both Databases on the same server?... you could just switch the working database...
mysql_select_db ("Database_Name");
mysql_select_db ("Database_Name");
This shows how to select different data bases on the same DB server. You can install it and run it to see the code in action. Important man page here:
http://us3.php.net/manual/en/function.mysql-select-db.php
HTH, ~Ray
http://us3.php.net/manual/en/function.mysql-select-db.php
HTH, ~Ray
<?php // RAY_mysql_example_3.php
error_reporting(E_ALL);
echo "<pre>\n"; // READABILITY
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
}
echo "DB CONNECTION: ";
var_dump($db_connection);
// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB LIST: ";
echo "<br/> $errmsg <br/>";
die();
}
while ($row = mysql_fetch_object($db_list))
{
$db_names[] = $row->Database ;
}
echo "LIST OF DB NAMES: ";
var_dump($db_names);
// SELECT ONE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
$db_name = $db_names[0];
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// SHOW THE TABLES ON THIS DATA BASE
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res)
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
while ($row = mysql_fetch_assoc($res))
{
var_dump($row);
}
// SELECT ANOTHER MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
$db_name = $db_names[1];
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// SHOW THE TABLES ON THIS DATA BASE
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res)
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
while ($row = mysql_fetch_assoc($res))
{
var_dump($row);
}
// END
die('DONE');