DIFFERENT DATABASE TABLE COUPLING METHOD USING PDO

I don't know how to combine tables of different databases in the same server using PDO class, so I would appreciate it if you could give me some advice.
The development language is PHP and the database is MYSQL.

What I've done is to internally combine the id of the following two tables as keys and extract the member_name and group_name columns.

Database Name/Table Name
DB1/table1
DB2/table2

I think the description here in ↓↓ is wrong, but I don't know the correct description.
$stmt=$dbh1->prepare($sql);

As I am a beginner in programming, I am sorry if there is any missing information.
Thank you for your cooperation.

$dsn1='mysql:dbname=DB1; host=localhost; charset=utf8';
$user1 = 'root';
$password1 = '******';

$dsn2='mysql:dbname=DB2; host=localhost; charset=utf8';
$user2 = 'root';
$password2 = '******';

try{
  $dbh1 = new PDO($dsn1,$user1,$password1);
  $dbh1->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
  $dbh1->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

  $dbh2 = new PDO($dsn2,$user2,$password2);
  $dbh2->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
  $dbh2->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

  $sql=
    US>"SELECT tb1.member_name, tb2.group_name
     FROM DB1.table1tb1
     INNER JOIN DB2.table 2tb2 ON tb1.id=tb2.id";
  $stmt = $dbh1->prepare($sql);
  $stmt->execute();

}catch(PDOException$Exception){
    print "Error: ".$Exception->getMessage();
}

php mysql pdo

2022-09-30 14:21

1 Answers

First of all, there is only one connection to the database.Allow the connected user permissions on both databases.

After that, I think the following SQL statements can be used.

SELECT tb1.member_name, tb2.group_name
FROM DB1.table1A tb1, DB2.table2A tb2
INNER JOINTB2 ON tb1.id = tb2.id

2022-09-30 14:21

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656