如何回显来自两个不同数据库的两个相似查询的合并结果的表? [英] How do I echo a table with the combined results of two similar queries from two different databases?
问题描述
我有两个不同(但几乎相同)的数据库，我试图生成一个表，该表将显示两个数据库上查询的组合结果. (在MySql工作台中，这些数据库是完全独立的，并且包含正在查询的数据库，我不确定这是否有所不同，但了解它可能很有用.)
I have two different (yet almost identical) databases and I and trying to produce a table which will display the combined results of a query on the two databases. (In MySql workbench, these databases are completely separate, and contain the databases that are being queried, I'm not sure if that makes a difference but it may be useful to know).
我需要结果表显示以下内容:
I need the results table to show the following:
编号|公司| db1计数| db2计数|
number | company | db1 count | db2 count |
Number和Company都在两个数据库中，两者之间唯一的区别是一个数据库中的计数不同.
Number and Company are in both databases, the only difference between the two is that the count in one is different.
**最终会有第五列显示这两个计数之间的差异，但最终我会讲到这一点.
**Eventually there will be a fifth column, which will show the difference between the two counts, but I will get to that eventually.
关于获得想要的结果，我已经考虑了许多不同的想法，但是我仍然真的不知道. 在db1和db2中的数字相同的地方，我需要显示每个的计数. 我目前拥有的代码是:
I've looked at many different ideas with regards to getting the result I want, but I still have no idea really. Where the number in db1 and db2 are the same, I need to display the count for each. The code I have at the moment is:
// Creating the connection
$conn1 = new mysqli($servername, $username, $password, $db1);
$conn2 = new mysqli($servername, $username, $password, $db2);
// Test connection
if ($conn1->connect_error) {
die ("Connection failed: " . $conn1->connect_error);
}
elseif ($conn2->connect_error){
die("Connection failed: " . $conn2->connect_error);
}
$sql1 = "SELECT num.number AS Number, com.name As company, count(*) As db1 count
FROM db1.db.job_processing AS jp
LEFT JOIN db1.db.number AS num ON num.id=jp.number_id
LEFT JOIN db1.db.company AS com on com.id=num.company_id
WHERE jp.show=1 AND jp.processing_complete=1
AND jp.call_start_time BETWEEN '2016-12-17' AND '2017-01-03'
GROUP BY Number
ORDER BY Number
LIMIT 20";
$result1 = $conn1->query($sql1);
$sql2 = "SELECT num.number AS Number, com.name AS company, COUNT(*) AS db2 Count
FROM db2.db.job_processing AS jp
LEFT JOIN db2.db.number AS num ON num.id=jp.number_id
LEFT JOIN db2.db.company AS com on com.id=num.company_id
WHERE jp.show=1 AND jp.processing_complete=1
AND jp.call_start_time BETWEEN '2016-12-17' AND '2017-01-03'
GROUP BY Number
LIMIT 20";
$result2 = $conn2->query($sql2);
if ($result1 = $conn1->query($sql1) && ($results2 = $conn2->query($sql1))) {
echo"<TABLE><caption>Total Call Count Overview</caption><TR>
<TH>Number</TH>
<TH>Company</TH>
<TH>db1 Count</TH>
<TH>db2 Count</TH></TR>";
//This is where I think my problems are arising
while ($row1 = $result1->fetch_assoc() && ($row2 = $result2->fetch_assoc())) {
echo"<TR><TD>". $row1["number"]. "</TD>";
echo"<TD>". $row1["company"]. "</TD>";
echo"<TD>". $row1["db1 Count"]. "</TD>";
echo"<TD>". $row2["db2 Count"]. "</TD></TR>";
}
echo"</TABLE>";
} else {
echo"O Results";
}
$conn1->close();
$conn2->close();
我认为这是我被困住的最后一个部分.我还查看了以下解决方案:
I think it's the end part where I am stuck. I have also looked at the following solution:
if ($result1 = $conn1->query($sql1)) {
echo"<TABLE><caption>Total Call Count Overview</caption><TR>
<TH>Number</TH>
<TH>Company</TH>
<TH>db1 Count</TH>
<TH>db2 Count</TH></TR>";
while ($row1 = $result1->fetch_assoc()) {
echo"<TR><TD>". $row1["number"]. "</TD>";
echo"<TD>". $row1["company"]. "</TD>";
echo"<TD>". $row1["db1 Count"]. "</TD>";
echo"<TD>". $row2["db2 Count"]. "</TD></TR>";
}
echo"</TABLE>";
} else {
echo"O Results";
}
if ($result2 = $conn2->query($sql2)) {
echo"<TABLE><caption>Total Call Count Overview</caption><TR>
<TH>Number</TH>
<TH>Company</TH>
<TH>db1 Count</TH>
<TH>db2 Count</TH></TR>";
while ($row_devel = $result_devel->fetch_assoc()) {
echo"<TR><TD>". $row1["number"]. "</TD>";
echo"<TD>". $row1["company"]. "</TD>";
echo"<TD>". $row1["db1 Count"]. "</TD>";
echo"<TD>". $row2["db2 Count"]. "</TD></TR>";
}
echo"</TABLE>";
} else {
echo"O Results";
}
这些只是我一直在尝试的两个解决方案. 我只是不知道如何将两个查询合并在一起，而且我意识到我可能在很多地方完全错了，但是我所能提供的任何帮助将不胜感激.
Those are just two of the solutions I've been trying. I just don't get how I can merge the two queries together, and I realise I have probably gone completely wrong in a number of places, but any assistance I could get would be much appreciated.
编辑 关于数据库，on是写入"数据库，另一个是"report"数据库.因此，每次呼叫号码时，都应将其添加到表中.我的查询应该计算一个数字出现的次数.我只想显示报告的内容(报告数据库)和实际的内容(写入数据库)之间是否有任何差异.
EDIT With regards to the databases, on is a 'write to' db, and the other is a 'report' db. So, every time a call is made to a number it should be added to the table. My query should be counting the number of times a number appears. I just want to display if there are any differences between what is reported (the report db) and what is actual (the write to db).
推荐答案
假设您的结果是这样的:
Assuming your results are like this :
写
number company db1 Count
1 SuperCorp 5
2 SuperCorp 10
报告
number company db2 Count
2 SuperCorp 10
3 SuperCorp 20
如果要显示这样的表
number company db1 Count db2 Count
1 SuperCorp 5 0
2 SuperCorp 10 10
3 SuperCorp 0 20
您需要先合并两个查询的结果，然后才能将其打印到屏幕上.这要归功于您的数字"列，该列可用作PHP数组的键.
You need to merge the results of the two queries before to print them to screen. This is possible thanks to your 'number' column which can be used as the key of your PHP array.
您还需要修改SQL查询，以包括代表其他数据库计数的伪列:
You also need to modify your SQL queries to include a fake column representing the other database count :
$sql1 = "SELECT num.number AS Number, com.name As company, count(*) As \"db1 count\", 0 As \"db2 count\"
FROM db1.db.job_processing AS jp
LEFT JOIN db1.db.number AS num ON num.id=jp.number_id
LEFT JOIN db1.db.company AS com on com.id=num.company_id
WHERE jp.show=1 AND jp.processing_complete=1
AND jp.call_start_time BETWEEN '2016-12-17' AND '2017-01-03'
GROUP BY Number
ORDER BY Number
LIMIT 20";
$sql2 = "SELECT num.number AS Number, com.name AS company, COUNT(*) AS \"db2 Count\", 0 As \"db1 count\"
FROM db2.db.job_processing AS jp
LEFT JOIN db2.db.number AS num ON num.id=jp.number_id
LEFT JOIN db2.db.company AS com on com.id=num.company_id
WHERE jp.show=1 AND jp.processing_complete=1
AND jp.call_start_time BETWEEN '2016-12-17' AND '2017-01-03'
GROUP BY Number
LIMIT 20";
然后将这两个结果组合在一起以显示它们，如下所示:
And then combine the 2 results before to display them, like this :
$results = array();
while($row = $result1->fetch_assoc()) {
//Adding all the 1st query results
$results[$row['number']] = $row;
}
while($row = $result2->fetch_assoc()) {
if(! isset($results[$row['number']]) {
//Mean's this row is not present in the 1st database, so add it
$results[$row['number']] = $row;
}else {
//Just merging the db2 Count from the 2nd database since other fields are the same
$results[$row['number']]['db2 Count'] = $row['db2 Count'];
}
}
if ($results) {
echo"<TABLE><caption>Total Call Count Overview</caption><TR>
<TH>Number</TH>
<TH>Company</TH>
<TH>db1 Count</TH>
<TH>db2 Count</TH></TR>";
foreach($results as $row) {
echo"<TR><TD>". $row["number"]. "</TD>";
echo"<TD>". $row["company"]. "</TD>";
echo"<TD>". $row["db1 Count"]. "</TD>";
echo"<TD>". $row["db2 Count"]. "</TD></TR>";
}
echo"</TABLE>";
} else {
echo"0 Results";
}
这篇关于如何回显来自两个不同数据库的两个相似查询的合并结果的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
