I have created a database application that prints out forms, manages invoices, and does a whole bunch of interesting stuff. To put it into detail without going too far into it... Basically I have Invoices. There are many invoices to a single application ID. The reason why there are multiple invoices for a single application ID is that each invoice represents a "days late" entry like NULL, 30, 60, 90, 91 (to represent >90 days late) This association in the invoice would look something like this:
Invoice ID: 17061 | App ID: 1221 | Days Late:
Invoice ID: 17062 | App ID: 1221 | Days Late: 30
Invoice ID: 16217 | App ID: 1000 | Days Late:
Invoice ID: 17063 | App ID: 1221 | Days Late: 60
Invoice ID: 17064 | App ID: 1221 | Days Late: 90
Invoice ID: 17065 | App ID: 1221 | Days Late: 91
Invoice ID: 16229 | App ID: 1000 | Days Late: 30
What I basically want to output is JUST a single invoice id for each app id. In this example. There are 2 app IDs in the table. 1221 and 1000. What it should output is only show the "latest" invoice. Basically, the higher the number the days late, the more recent that invoice is. So the end result should output the following invoice IDs.
Invoice IDs: 17065-16229
There has to be an easier way to go about this without involving multidimensional arrays. Perhaps an SQL entry, or maybe a fancy PHP function that can compare two arrays... I don't know. I can't wrap my head around it for some reason.
Okay. I came up with a solution based on an old request I've had in the past. I don't know why I can't seem to properly wrap my head around it, but after sitting on it I came up with the fix. so if anyone who sees this post also has this question, they have a resolution available.
//start script for pairing up the invoices by their application ids
$r=0;
$invoicearray = array();
$applicationidarray = array(); //two arrays to pair them up later and drop the useless ones
while(count($ids) > $r){
$sqlpairup = "SELECT * FROM INVOICE WHERE ID='$ids[$r]'";
$resultpairup = $conn->query($sqlpairup);
if ($resultpairup->num_rows > 0) {
$rowpairup = $resultpairup->fetch_assoc();
array_push($invoicearray, $rowpairup['ID']);
array_push($applicationidarray, $rowpairup['APPLICATION']);
}
$r++;
}
$invoicesByAppID = [];
$applicationidcount = count($applicationidarray);
for($i=0; $i < $applicationidcount; $i++){
$invoice = $invoicearray[$i];
$application = $applicationidarray[$i];
if(!isset($invoicesByAppID[$application])){
$invoicesByAppID[$application] = [];
}
$invoicesByAppID[$application][] = $invoice;
}
$finalids = array();
$groupedupinvoices = array_keys($invoicesByAppID);
for($i=0; $i < count($groupedupinvoices); $i++){
$invoicetoprint = max($invoicesByAppID[$groupedupinvoices[$i]]);
array_push($finalids, $invoicetoprint);
}
//end script for pairing up the invoices by their applicationg ids
What's happening is a multi dimensional array that groups up the invoices by application ID, then it spews out the final ids by grabbing the latest invoice by ID using the max function. I am also certain there is a much easier way to do this, but this is the way that I took it and it functions well.