Im creating a display table in PHP that uses multiple SQL databases and tables. There are 2 major tables called "Attempt Payments" and "Payments Made". I created classes and put all of the data returned into an associative array. In my display table "Attempt Payments" and "Payments Made" fields are connected based on case number and title. I want it to display so that the payments made only shows once with the closest attempt payment date ( also based on case number and title). Right now the Payments Made fields are repeating with each attempt payment. Here's an example of the code.

$myvariable = $DArray[$myShowCourt];

$sqlmain = "select * FROM mconline_master.PaymentHandoff

INNER JOIN mconline_master.main ON mconline_master.PaymentHandoff.Court =

mconline_master.main.ClientID WHERE (mconline_master.PaymentHandoff.HandoffDateTime >= '".$sdate. "' AND

mconline_master.PaymentHandoff.HandoffDateTime <= '".$edate."') AND Court IN ('".$myvariable."') ORDER BY `Case` ASC, HandoffDateTime ASC";

$resultmain = $conn->query($sqlmain);

echo $mysqli->error;

//BEGINNING OF PAYMENTHANDOFF TABLE

if($resultmain->num_rows >0){

//echo "205::non zero rows after sqlmain query

";

while ($loAPaymentRow = $resultmain->fetch_assoc()){

$lcThisIndex=$loAPaymentRow["PaymentHandoffID"];

//echo "209::payment row fetch lcThisIndex=[".$lcThisIndex."]

";

$gaPaymentHandoff[ $lcThisIndex ] = new AttemptPayment; //extend the array by appending this object

//populate this critter

$gaPaymentHandoff[ $lcThisIndex ]->title=$loAPaymentRow["title"];

$gaPaymentHandoff[ $lcThisIndex ]->PaymentHandoffID=$loAPaymentRow["PaymentHandoffID"];

$gaPaymentHandoff[ $lcThisIndex ]->Court=$loAPaymentRow["Court"];

$gaPaymentHandoff[ $lcThisIndex ]->Amount=$loAPaymentRow["Amount"]+0.0; //force type to numeric by adding the additive identity element

$gaPaymentHandoff[ $lcThisIndex ]->HandoffDateTime=$loAPaymentRow["HandoffDateTime"];

$gaPaymentHandoff[ $lcThisIndex ]->Case=$loAPaymentRow["Case"];

}//END OF WHILE LOOP $loAPaymentRow

}//END OF IF STATEMENT $resultApayment

else{

echo " ";

}

$sql = "select * FROM mconline_master.main WHERE mconline_master.main.ProductionSite='1' ORDER BY title";

$result = $conn->query($sql);

echo $mysqli->error;

if ($result->num_rows>0){

//echo "154:: non zero num rows

";

while($row = $result->fetch_assoc()){

//echo "156: result 1 fetch assoc

";

$lcdbname = $_POST['ShowCourt'];

$sql2 = "select * FROM " . $lcdbname . ".Payment INNER JOIN

".$lcdbname.".CaseM ON ".$lcdbname.".Payment.PayorReferenceNumber = ".$lcdbname.".CaseM.UniqueKey

INNER JOIN mconline_master.PaymentHandoff ON ".$lcdbname.".CaseM.CaseNum = mconline_master.PaymentHandoff.Case

INNER JOIN mconline_master.main ON mconline_master.PaymentHandoff.Court = mconline_master.main.ClientID

WHERE (PaymentEffectiveDate >= '".$sdate. "') AND

(PaymentEffectiveDate <= '".$edate."') ORDER BY PaymentEffectiveDate ASC";

$result2 = $conn->query($sql2);

echo $mysqli->error;

if($result2->num_rows >0){

while ($loPaymentRow = $result2->fetch_assoc()){

//echo "168: result 2 fetch assoc

";

$lcCaseNumLookupQuery="select CaseNum from " . $lcdbname . ".CaseM where UniqueKey=\"".$loPaymentRow["PayorReferenceNumber"]."\" or ".

" CaseNum=\"".$loPaymentRow["PayorReferenceNumber"]."\"";

//echo "396:: lcCaseNumLookupQuery=$lcCaseNumLookupQuery

";

$lcCaseNumLookupResult=$conn->query($lcCaseNumLookupQuery);

//echo "398:: howdy.

";

$lcCaseNumLookupRow=$lcCaseNumLookupResult->fetch_assoc();

$lcThisCaseNum=$lcCaseNumLookupRow['CaseNum'];

$lcThisIndex=$lcdbname.":".$lcThisCaseNum;

//$lcThisIndex=$lcdbname.":".$loPaymentRow["PayorReferenceNumber"];

$gaPaymentsMade[ $lcThisIndex ] = new PaymentMade; //extend the array by appending this object

//populate this critter

$gaPaymentsMade[ $lcThisIndex ]->PayorReferenceNumber=$loPaymentRow["PayorReferenceNumber"];

$gaPaymentsMade[ $lcThisIndex ]->ClientID=$loPaymentRow["ClientID"];

$gaPaymentsMade[ $lcThisIndex ]->AmountDue=$loPaymentRow["AmountDue"]+0.0; //force type to numeric by adding the additive identity element

$gaPaymentsMade[ $lcThisIndex ]->ConvenienceFee=$loPaymentRow["ConvenienceFee"];

$gaPaymentsMade[ $lcThisIndex ]->PaymentAmount=$loPaymentRow["PaymentAmount"];

$gaPaymentsMade[ $lcThisIndex ]->PaymentEffectiveDate=$loPaymentRow["PaymentEffectiveDate"];

$gaPaymentsMade[ $lcThisIndex ]->PaymentMethod=$loPaymentRow["PaymentMethod"];

$gaPaymentsMade[ $lcThisIndex ]->ConfirmationID=$loPaymentRow["ConfirmationId"];

}

}

}

}

else{

echo "Oops...Mistake for Show Court ";

}

foreach($gaPaymentHandoff as $lcThisKey => $loThisPaymentHandoff)

{

$lcThisDB=$lcDBList[$loThisPaymentHandoff->Court];

$lcThisIndex=$lcThisDB.":".$loThisPaymentHandoff->Case;//$loPaymentRow["PayorReferenceNumber"];

$loThisPaymentHandoff->PaymentEffectiveDate=$gaPaymentsMade[ $lcThisIndex ]->PaymentEffectiveDate;

//echo "587:: lcThisKey=$lcThisKey loThisPaymentHandoff->Court=[".$loThisPaymentHandoff->Court."] lcThisDB==$lcThisDB lcThisIndex=$lcThisIndex

";

$loThisPaymentHandoff->PaymentAmount=$gaPaymentsMade[ $lcThisIndex ]->PaymentAmount;

$loThisPaymentHandoff->ConfirmationID=$gaPaymentsMade[ $lcThisIndex ]->ConfirmationID;

$loThisPaymentHandoff->PaymentMethod=$gaPaymentsMade[ $lcThisIndex ]->PaymentMethod;

$loThisPaymentHandoff->ConvenienceFee=$gaPaymentsMade[ $lcThisIndex ]->ConvenienceFee;

$loThisPaymentHandoff->PayorReferenceNumber=$gaPaymentsMade[ $lcThisIndex ]->PayorReferenceNumber;

}

//Table

echo '

';

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

$sum = 0;

foreach($gaPaymentHandoff as $lcThisKey => $loThisPaymentHandoff){

$sum += $loThisPaymentHandoff->PaymentAmount;

$CourtTitle = $loThisPaymentHandoff->title;

echo "

";

echo "

";

echo "

";

echo "

";

echo "

";

//echo "loThisPaymentHandoff[".$lcThisKey."] =>{

";

echo "

";

echo "

";

echo "

";

echo "

";

//echo $record;

}

echo "

echo "

Title	Case	Payment Method	Payment Date	Paid Amount	CaseM ID	ConfirmationID	Attempt Payment	Attempt Payment Date
"; echo $loThisPaymentHandoff->title; echo "	"; echo $loThisPaymentHandoff->Case; echo "	"; echo $loThisPaymentHandoff->PaymentMethod; echo "	"; echo $loThisPaymentHandoff->PaymentEffectiveDate; echo "	"; echo "$".$loThisPaymentHandoff->PaymentAmount; echo "	"; echo $loThisPaymentHandoff->PayorReferenceNumber; echo "	"; echo $loThisPaymentHandoff->ConfirmationID; echo "	"; echo "$".$loThisPaymentHandoff->Amount.".00"; echo "	"; echo $loThisPaymentHandoff->HandoffDateTime; echo "

";

echo 'Total Actual Payment Amount: $'.number_format($sum,2)." ";

}

}