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:
number | company | db1 count | db2 count |
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.
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.
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).
See Question&Answers more detail:
os