英文:
Single query for two database connection on SSP Class PHP
问题
以下是翻译的部分：
server_processing.php
<?php
$table = 'Customer'; // 要使用的数据库表
$primaryKey = 'id'; // 数据表的主键
// `db`参数表示数据库中的列名，而`dt`参数表示DataTables列标识符。在这种情况下，使用简单的索引
$columns = array(
array('db' => '`c`.`id`', 'dt' => 'id', 'field' => 'id'),
array('db' => '`r`.`Status`', 'dt' => 'Status', 'field' => 'Status')
);
$sql_details = array(); // SQL连接详细信息
require('ssp.customized.class.php');
$joinQuery = "FROM `Customer` AS `c`
LEFT JOIN `Receipt` AS `r` ON (`c`.`id` = `r`.`Card_No`)";
$where = "`r`.`Status` = 'PAID'";
echo json_encode(
SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where)
);
?>
你尝试的修改中，你添加了以下的左连接：
LEFT JOIN `systemB.History` AS `b` ON (`c`.`id` = `b`.`id_number`)
这导致了一个SQL错误，因为它在系统A的数据库中查找名为systemB.History的表，这个表不存在。你的目标是从不同数据库中检索数据，但是你需要使用不同的连接来实现这一点。
在server_processing.php文件中，你已经有一个连接到系统A数据库的查询，但是要访问系统B数据库中的History表，你需要另外建立一个数据库连接，并在相应的连接中执行查询。如果你需要更多帮助，可以提供更多的信息。
英文:
I want to retrieve data from 2 database which is from the same server on single query.
So here's query I want to implement.
SELECT Customer.id, Receipt.Status,
(SELECT b.created_date FROM systemB.History as b WHERE b.id_number LIKE Customer.id
ORDER BY id DESC LIMIT 1) as Date
FROM Customer
LEFT JOIN Receipt ON Receipt.Card_No = Customer.id
WHERE Receipt.Status = 'PAID
and it give result as below.
'+--------+--------+--------------+
| id | Status | Date |
+---------+--------+--------------+
| 1 | PAID | 2023-02-01 |
| 2 | PAID | 2022-07-05 |
+--------+---------+--------------+
systemA database for table --> Customer, Receipt
systemB database for table --> History
Basically, both connection on the same server with same username & password. How do I to implement below query on server_processing.php
(SELECT b.created_date FROM systemB.History as b WHERE b.id_number LIKE Customer.id
ORDER BY id DESC LIMIT 1) as Date
server_processing.php
<?php
$table = 'Customer'; // DB table to use
$primaryKey = 'id'; // Table's primary key
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => '`c`.`id`', 'dt' => 'id', 'field' => 'id' ),
array( 'db' => '`r`.`Status`', 'dt' => 'Status', 'field' => 'Status' )
);
$sql_details = array( ); // sql connection
require('ssp.customized.class.php' );
$joinQuery = "FROM `Customer` AS `c`
LEFT JOIN `Receipt` AS `r` ON (`c`.`id` = `r`.`Card_No`)";
$where = "`r`.`Status` = 'PAID'";
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where)
); ?>
I've been trying like below. It's give me this error.
$joinQuery = "FROM `Customer` AS `c`
LEFT JOIN `Receipt` AS `r` ON (`c`.`id` = `r`.`Card_No`)
LEFT JOIN `systemB.History` AS `b` ON (`c`.`id` = `b`.`id_number`)
";
An SQL error occurred: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'systemA.systemB.History' doesn't exist
How to implement the query? Any help would be appreciated.
答案1
得分: 1
在MySQL中，反引号（backtick）是标识符引用字符。这意味着反引号内的任何内容都被视为一个标识符。只有在标识符中包含特殊字符时才需要使用反引号。
在您的查询中，您有：
`systemB.History`
这意味着MySQL正在查找当前（systemA）数据库中的表systemB.History。
您可以改为使用：
systemB.History
或者如果您想要使用反引号，那么可以使用：
`systemB`.`History`
英文:
In MySQL the backtick is the identifier quote character. This means that anything inside the backticks is treated as one identifier. The backticks are only needed if you have special characters in the indentifier.
In your query you have:
`systemB.History`
Which means that MySQL is looking for a table systemB.History from the current (systemA) database.
You can instead use:
systemB.History
Or if you want to use backticks, then:
`systemB`.`History`
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论