基于特定标准对数组中的列求和

Question

I am trying to make a page in PHP more efficient. The goal is to tally payments for jobs that were delivered between two dates. The payments are in a separate table but only have a payment date, not a delivery date. Previously, I had a while loop to select all records between two dates which then carried out another query on the payments table within the loop. This was painfully slow so i have done this in 2 queries and put the results into an array as shown below.

{ [20253]=> array(5) { ["contNo"]=> string(5) "20253" ["contDate"]=> string(19) "2017-01-25 15:15:34" ["hireStart"]=> string(10) "2017-01-27" ["hireEnd"]=> string(10) "2017-01-30" ["revenue"]=> array(2) { [0]=> array(2) { ["contNo"]=> string(5) "20253" ["revenue"]=> string(6) "197.85" } [1]=> array(2) { ["contNo"]=> string(5) "20253" ["revenue"]=> string(5) "70.22" } } }

What i would now like to do is sum the revenue when the hireStart date is between x and y dates and also sum the revenue when the contDate is between x and y date.

Does anyone have an idea how I can do this? Just to point out that is just one record in the array of 14000 results so efficiency is what i need most! The code used to generate the array is below.

while ($contNums = $result2->fetch_assoc()) {
    //for each contract in date range sum revenue
    $contNoArray[$contNums['contNo']]=$contNums;
}
mysqli_free_result($result2);

$stmt2="SELECT contNo, revenue FROM `payments`";
$result3 = $conn->query($stmt2);
while ($payments = $result3->fetch_assoc()) {
    $contNoArray[$payments['contNo']]['revenue'][]=$payments;
}
foreach($contNoArray AS $contract)
{
    This is where i am stuck!
}

Answer 1

Thanks to Jonathan that suggested the left join. Should have thought of this before! I would still like to know how (code wise) to do it as i was initially attempting if anyone does know. The solution to do it in the query is below...

SELECT sum(P.revenue) as revTotal
FROM contracts C LEFT JOIN payments P
ON P.contNo = C.contNo
where C.contDate BETWEEN '2017-01-01' AND '2018-12-31'