Solved

Mysql How to insert into two different tables located in two different databases?

Posted on 2010-08-17
15
354 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:Solutionabc
  • 6
  • 3
  • 3
  • +3
15 Comments
 
LVL 3

Expert Comment

by:mpvvee
ID: 33459846
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.
0
 

Author Comment

by:Solutionabc
ID: 33460072
can you give an example?

I tried but I am getting an error.
0
 
LVL 3

Expert Comment

by:mpvvee
ID: 33460191
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>");
0
 
LVL 6

Expert Comment

by:merwetta1
ID: 33460233
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);
0
 

Author Comment

by:Solutionabc
ID: 33460251
So your saying that I would use each code twice but different db username and password?
0
 
LVL 3

Expert Comment

by:mpvvee
ID: 33460343
yes merwetta1 comment is right
0
 

Author Comment

by:Solutionabc
ID: 33460362
I tried that and it says:

 Table 'singers.dance' doesn't exist

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Solutionabc
ID: 33460369
It is looking in the wrong database for the table.
0
 
LVL 6

Expert Comment

by:merwetta1
ID: 33460433
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?
0
 

Author Comment

by:Solutionabc
ID: 33460653
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());

Open in new window

0
 

Author Comment

by:Solutionabc
ID: 33460700
oh I forgot to include this in the posting:

mysql_select_db("db1", $con);
mysql_select_db("db2r", $con2);
0
 
LVL 6

Accepted Solution

by:
merwetta1 earned 500 total points
ID: 33460740
It's easiest if you connect to the databases one at a time. The mysql_query() command will use whatever database you connected to last. Try something like this:
$con = mysql_connect('localhost', 'user', 'pass');
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db("db1", $con);
## connected to first database, now do queries
mysql_query("UPDATE ORDERS 
SET Status = 'Accepted' WHERE Username = '$slusername' AND Order_ID = '".$id."' ") 
or die(mysql_error());

$con2 = mysql_connect('localhost', 'user2', 'pass2');
if (!$con2) die('Could not connect: ' . mysql_error());
mysql_select_db("db2r", $con2);
## connected to second database, now do queries
mysql_query("INSERT INTO $slusername (start_date, end_date, username, user_defined) VALUES('$due','$due','$slusername','1')")
or die(mysql_error());

Open in new window

0
 
LVL 1

Expert Comment

by:sspraggs
ID: 33461005
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.

0
 
LVL 2

Expert Comment

by:Tiller79188231
ID: 33466951
both Databases on the same server?... you could just switch the working database...

 mysql_select_db ("Database_Name");

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33467190
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
<?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');

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 24
How do I put a session for a radio button? 4 12
datetime in sql 6 25
Why my select dropdown does not work? 8 25
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now