同一个查询涉及两个数据库时如何执行PDO语句? [英] How to execute PDO statement when there are two databases involve in the same query?
问题描述
我需要执行这个 SQL 查询:
I need to execute this SQL query:
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 = 'email_sent';
您可能已经注意到我正在查询不同的数据库:archive_pfizer 和 pdone_legacy.我知道 DSN 需要一个数据库名称来创建 PDO 对象，然后如标题所说:当同一查询涉及两个数据库时，如何执行 PDO 语句?
As you may already notice I am querying to different databases: archive_pfizer and pdone_legacy. I know that DSN needs a DB name for create the PDO object then as title say: How do I execute a PDO statement when there are two databases involve in the same query?
非常重要我正在询问如何使用 PDO 从 PHP 实现这一点，我能够从 MySQL/MariaDB 命令行和/或使用任何 GUI 成功执行查询.
Very important I am asking how to achieve this from PHP using PDO, I was able to execute the query successfully from MySQL/MariaDB command line and/or using any GUI.
更新
这是我根据 @RyanVincent 回答编写的代码:
Here is the code I was working on based on @RyanVincent answer:
$config = parse_ini_file('config.ini', true);
define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
$DBASE = $config['database']['DBASE'][0];
$pdo = setupDB($config, $DBASE);
$sql = '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 = "email_sent"';
foreach($pdo->query($sql) as $row) {
var_export($row);
echo EOL;
}
但是我收到了这个错误:
But I got this error:
PHP 致命错误:带有消息的未捕获异常PDOException"'SQLSTATE [42S02]:未找到基表或视图:1146 表'pdone_legacy.session_brand_presentation' 不存在'
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'pdone_legacy.session_brand_presentation' doesn't exist'
显然，当它是数据库时，它会将 pdone_legacy.session_brand_presentation 作为表，在提供的示例中与 testmysql 相同，有什么建议吗?
Apparently it's taking pdone_legacy.session_brand_presentation as a table when it's a database, in the example provided is the same as testmysql, any advice?
更新 2
尝试相同的 SQL 查询但使用别名也不起作用:
Trying the same SQL query but using aliases didn't work either:
$sql = 'SELECT DISTINCT
lg.hcp_id,
lg.rep_id,
lg.type,
sbp.ID,
sbp.brand_id,
sbp.createdAt,
sbp.modifiedAt
FROM
archive_pfizer.logger AS lg
JOIN
pdone_legacy.session_brand_presentation AS sbp ON lg.session_id = sbp.local_session_id
WHERE
lg.type = "email_sent"';
遇到了和以前完全相同的问题.
Got exactly the same issue as before.
更新 3
好吧，说不定我会被杀，但一直都是我的错.我正在连接到数据库 pdone_legacy 存在但该数据库实际上没有 session_brand_presentation 表的服务器，这就是 PDO 一直在说的.无论如何感谢这里的任何人，这两个查询都是有效的.
Ok, perhaps I will got killed after say this but was my bad all the time. I was connecting to a server where DB pdone_legacy exists but that DB in fact hasn't session_brand_presentation table and that is what PDO was saying all the time. Anyway thanks to anyone here and both queries are valid.
推荐答案
这是使用 PDO 从两个独立的 mysql 数据库连接的代码.
Here is the code to join from two separate mysql databases using PDO.
限制:
- 两个mysql数据库必须在同一台服务器上
- 只使用一个连接.
- 数据库用户必须对两个数据库都具有必要的权限
使用 archive_pfizer (logger) 和 pdone_legacy (session_brand_presentation) 进行查询
Query using archive_pfizer (logger) and 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();
输出:
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);
测试我们是否可以访问单独的数据库...
Test that we can access the separate databases...
1)archive_pfizer(记录器)
1) archive_pfizer (logger)
/* ----------------------------------------------------------
* 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)
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>';
这篇关于同一个查询涉及两个数据库时如何执行PDO语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
