下面是使用 PDO 连接两个独立 mysql 数据库的代码。
Pastebin:经过测试的代码
限制:
- 两个 mysql 数据库必须位于同一服务器上
- 仅使用一个连接。
- 数据库用户必须对两个数据库都具有必要的权限
使用 archive_pfizer (logger) 和 pdone_legacy (session_brand_presentation) 进行查询
$sqlBoth = 'SELECT DISTINCT
logger.hcp_id,
logger.rep_id,
logger.type,
session_brand_presentation.ID,
session_brand_presentation.brand_id,
session_brand_presentation.createdAt,
session_brand_presentation.modifiedAt
FROM
archive_pfizer.logger
JOIN
pdone_legacy.session_brand_presentation
ON logger.session_id = session_brand_presentation.local_session_id
WHERE
logger.type = :lg_type';
$stmt = $dbTest->prepare($sqlBoth);
$stmt->bindValue(':lg_type', 'email_sent', PDO::PARAM_STR);
$stmt->execute();
$resultBoth = $stmt->fetchAll();
$stmt->closeCursor();
Output:
pdone_legacy and archive_pfizer
Array
(
[0] => Array
(
[hcp_id] => hcp_id_01
[rep_id] => rep_od_01
[type] => email_sent
[ID] => ID_01
[brand_id] => brand_id_01
[createdAt] => 2015-09-24 01:42:51
[modifiedAt] =>
)
)
数据库连接:
/**
* must have access rights to both db's
*/
// db connection to archive_pfizer and pdone_legacy
$dsn = 'mysql:host=localhost;dbname=pdone_legacy';
$username = 'pfizer';
$password = 'pfizer';
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$dbTest = new PDO($dsn, $username, $password, $options);
$dbTest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
测试我们是否可以访问单独的数据库......
1)archive_pfizer(记录器)
/* ----------------------------------------------------------
* Query archive_pfizer (logger)
*/
$sqlArchive = 'SELECT hcp_id, rep_id, type, session_id, createdAt, modifiedAt
FROM archive_pfizer.logger
WHERE session_id = :a_session_id';
$stmt = $dbTest->prepare($sqlArchive);
$stmt->bindValue(':a_session_id', 'session_id_01', PDO::PARAM_STR);
$stmt->execute();
$resultArchive = $stmt->fetchAll();
$stmt->closeCursor();
echo '<br />', 'archive_pfizer.logger', '<br />';
echo '<pre>';
print_r($resultArchive);
echo '</pre>';
2) pdone_legacy (session_brand_presentation)
/* --------------------------------------------------
* Query pdone_legacy (session_brand_presentation)
*/
$sqlPDone = 'SELECT ID,
local_session_id,
brand_id,
createdAt,
modifiedAt
FROM pdone_legacy.session_brand_presentation
WHERE local_session_id = :sbp_session_id';
$stmt = $dbTest->prepare($sqlPDone);
$stmt->bindValue(':sbp_session_id', 'session_id_01', PDO::PARAM_STR);
$stmt->execute();
$resultPDone = $stmt->fetchAll();
$stmt->closeCursor();
echo '<br />', 'pdone_legacy.session_brand_presentation', '<br />';
echo '<pre>';
print_r($resultPDone);
echo '</pre>';