How do I echo a table with the combined results of

Question

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).

Answer 1

Edit :

Assuming your results are like this :

Write

number company    db1 Count
1      SuperCorp  5
2      SuperCorp  10

Report

number company    db2 Count
2      SuperCorp  10
3      SuperCorp  20

If you want to display a table like this

number company    db1 Count db2 Count
1      SuperCorp  5         0
2      SuperCorp  10        10
3      SuperCorp  0         20

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.

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";
}