PHP - 缩短从不同服务器运行查询表的时间

I have 2 tables with different databases and servers. Table 1 in Server 1 holds the destination and invoice no columns, and Table 2 in Server 2 holds Invoice No and Total columns.

I need to sum up total of all destination. The only matching value of both tables is invoice no, where WS10001 = 10001. The method I'm doing it, is select all values from Server 1 and Table 1, using foreach to loop the values and run the query in Table 2 in Server 2 to sum up the total.

$sql ="select * from table1 group by destination";
$result=mysql_query($sql) or die(mysql_error());

while($myrow=MySQL_fetch_array($result,MYSQL_ASSOC))
{
    extract($myrow);
    $invno=$myrow[2];

    sql2 = "select total from table2 where invoice_no like '%$invno%'";
    $result2 = mysqli_query($conn, $sql);

    while ($row=mysqli_fetch_row($result2)) {
        $value = $row[0];
    }   

    if (destination == "Washington")
    { $wstotal += $value;}
    else if (destination == "Hawaii")
    { $wstotal += $value;}
    else if (destination == "Budapest")
    { $wstotal += $value;}
}

This method works pretty quick when I use a sample table to run it with 100 records, however when i apply it to the actual tables which has more than 10000 records, it runs for hours to complete it because it has to run a query for every foreach loop.
Is there any ways that can shorten the time to complete it, or can I save down all values from both tables, sum up in php ?

You can use GROUP BY and a SUM, to get a sum per destination:

SELECT SUM(total) as total 
FROM table2 
WHERE destination IN ('Washington','Hawaii','Budapest') 
      AND invoice_no LIKE '%$invno%'
GROUP BY destination

This way, the DB doesnt need to remember the whole dataset, just the sum grouped, which in this case is max 3 results (this is not 100% accurate, but enough for now).

You also have a IN() which reduces the dataset further, IN() is a whole lot faster than LIKE.

If you dont have an index on 'destination' (or another column which have various different values which you use a lot in queries): Add a index on it. This'll improve the lookup times.

Maybe just use 2 query !

$sql ="select * from table1 group by destination";
$result=mysql_query($sql) or die(mysql_error());

// Create an empty query
$sql = "";

while($myrow=MySQL_fetch_array($result,MYSQL_ASSOC))
{
    extract($myrow);
    $invno=$myrow[2];

    // Now you add each query to you "global" query : don't forget the ';' at the end
    sql2 .= "select total from table2 where invoice_no like '%$invno%';";
}

Now you just run this query ONCE using mysqli_multi_query (the documentation) and get each result using mysqli_next_result (the documentation).

I'm sorry can't write the code I use PDO, but the logic is :

1/ You create a "big query" with all your subquery

2/ You execute this "big query"

3/ You fetch all the result and do your "math" as you did before

4/ Then you test if there is a next_result

5/ You fetch the next set of result and do your "math"

6/ Repeat step 4 and 5 untill there is no more next_resut

Try it but it should be better than have one query executed for each loop !

Hope it's clear enough?