$stmt = $d->prepare("select loan_ball, amt_paid_wkly, sum(total_loan_bal) from loan_main t1 join collection t2 on
t1.loan_id = t2.loan_id where t1.loan_id = :loan_id"); // check for loan balance and amount to be paid weekly
for($a=0;$a<count($loan_id);$a++) { /*main for loop open here */
$stmt->bindparam(':loan_id', $loan_id[$a],PDO::PARAM_INT);
$stmt->execute();
while($row = $stmt->fetch()){ // we generate the loan balance and weekly payment /*secondary while loop*/
$loan_ball_main_gen[$a] = $row['loan_ball']; // loan ballance generated from the loan_main table
$amt_paid_wkly_gen[$a] = $row['amt_paid_wkly']; // amount to be paid weekly from the database
$loan_ball_coll_gen[$a] = $row['sum(total_loan_bal)']; // loan ballance generated from the collection table
}
if($amt_paid[$a] > $loan_ball_main_gen[$a]){ // if amount given is greater than loan balance we echo and exit
echo "boss " .$amt_paid[$a]. " is greater than the loan balance of " .$loan_ball_main_gen[$a];
exit();
}
if($loan_ball_coll_gen[$a] == 0){
$loan_ba[$a] = $loan_ball_main_gen[$a] - $amt_paid[$a]; // the new loan balance we need to store in the main_loan and collection
$amt[$a] = $amt_paid[$a] - $amt_paid_wkly_gen[$a]; // EQUATION TO DETERMINE ADVANCE AND OVERDUE //
if($amt[$a] < 0){ // if amt is less than zero then we have overdue "so we multiply by -1 to make the value positive"
$overdue[$a] = (-1 * $amt[$a]);
$advance[$a] = 0;
$total_advance[$a] = 0;
$total_overdue[$a] = (-1 * $amt[$a]);
} elseif($amt[$a] > 0){ // if amt is graeter than 0 we have an advance
$advance[$a] = ($amt[$a] * 1);
$overdue[$a] = 0;
$total_overdue[$a] = 0;
$total_advance[$a] = ($amt[$a] * 1);
} elseif($amt[$a] == 0){ // if amt is 0 we have the exact value we needed
$advance[$a] = 0;
$overdue[$a] = 0;
$total_overdue[$a] = 0;
$total_advance[$a] = 0;
}
// echo $advance[$a]."<br/>";
/**********/
$stmt = $d->prepare("update collection set amt_paid =:amt_paid,advance=:advance,total_advance=:total_advance,overdue =:overdue,
total_overdue =:total_overdue,total_loan_bal =:total_loan_bal,date_coll=:date_coll, who_cre=:who_cre where coll_id=:coll_id");
for($b=0;$b<count($coll_id);$b++) {
$stmt->bindparam(':amt_paid',$amt_paid[$b],PDO::PARAM_INT); $stmt->bindparam(':advance',$advance[$b],PDO::PARAM_INT);
$stmt->bindparam(':total_advance',$total_advance[$b],PDO::PARAM_INT); $stmt->bindparam(':overdue',$overdue[$b],PDO::PARAM_INT);
$stmt->bindparam(':total_overdue',$total_overdue[$b],PDO::PARAM_INT); $stmt->bindparam(':total_loan_bal',$loan_ba[$b],PDO::PARAM_INT);
$stmt->bindparam(':date_coll',$date_coll[$b],PDO::PARAM_INT); $stmt->bindparam(':who_cre',$who_cre[$b],PDO::PARAM_INT);
$stmt->bindparam(':coll_id',$coll_id[$b],PDO::PARAM_INT);
$who_cre[$b] = 123; $date_coll[$b] = date("Y-m-d H:i:s");
$stmt->execute();
if($stmt == true){
echo "saved successfully";
}
}
}
/**********/
}Since the error mentioned in the title is likely occurring at the UPDATE query, either the length of the $loan_id and $coll_id arrays are not the same or they don’t have matching index values.
Unfortunately, since you are using bindparam(), which uses a reference to the variable, php won’t help you by reporting undefined variables/indexes. You need to switch to use implicit binding, by supplying an array of values to the execute call.
Actually, after cleaning up the formatting of the code, you are looping over the $loan_id values, then at the end of processing each one, you are looping over all the $coll_id values. I suspect you actually want to loop over all the $loan_id values, then you want to loop over all the $coll_id values? Or perhaps at the end of the processing for one $loan_id value, you want to perform the UPDATE query for the matching $coll_id?
Here’s the cleaned up code -
// there is apparently an array $loan_id
// there is apparently an array $amt_paid
// there is apparently an array $coll_id
// build sql query statements in a variable
$sql = "select loan_ball, amt_paid_wkly, sum(total_loan_bal)
from loan_main t1
join collection t2 on t1.loan_id = t2.loan_id
where t1.loan_id = :loan_id";
$stmt = $d->prepare($sql); // check for loan balance and amount to be paid weekly
// for($a=0;$a<count($loan_id);$a++) { /*main for loop open here */
// just use a foreach loop
foreach(array_keys($loan_id) as $a)
{
//$stmt->bindparam(':loan_id', $loan_id[$a],PDO::PARAM_INT);
// just use implicit binding
$stmt->execute([
':loan_id'=>$loan_id[$a]
]);
// if the query didn't match any data, the original while loop code at this point will be skipped and the variables won't exist
// while($row = $stmt->fetch()){ // we generate the loan balance and weekly payment /*secondary while loop*/
// don't use a loop to fetch only one row. just directly fetch the data
$row = $stmt->fetch();
$loan_ball_main_gen[$a] = $row['loan_ball']; // loan ballance generated from the loan_main table
$amt_paid_wkly_gen[$a] = $row['amt_paid_wkly']; // amount to be paid weekly from the database
$loan_ball_coll_gen[$a] = $row['sum(total_loan_bal)']; // loan ballance generated from the collection table
// }
if($amt_paid[$a] > $loan_ball_main_gen[$a]){ // if amount given is greater than loan balance we echo and exit
echo "boss " .$amt_paid[$a]. " is greater than the loan balance of " .$loan_ball_main_gen[$a];
exit();
}
if($loan_ball_coll_gen[$a] == 0){
$loan_ba[$a] = $loan_ball_main_gen[$a] - $amt_paid[$a]; // the new loan balance we need to store in the main_loan and collection
$amt[$a] = $amt_paid[$a] - $amt_paid_wkly_gen[$a]; // EQUATION TO DETERMINE ADVANCE AND OVERDUE //
if($amt[$a] < 0){ // if amt is less than zero then we have overdue "so we multiply by -1 to make the value positive"
$overdue[$a] = (-1 * $amt[$a]);
$advance[$a] = 0;
$total_advance[$a] = 0;
$total_overdue[$a] = (-1 * $amt[$a]);
} elseif($amt[$a] > 0){ // if amt is graeter than 0 we have an advance
$advance[$a] = ($amt[$a] * 1);
$overdue[$a] = 0;
$total_overdue[$a] = 0;
$total_advance[$a] = ($amt[$a] * 1);
} elseif($amt[$a] == 0){ // if amt is 0 we have the exact value we needed
$advance[$a] = 0;
$overdue[$a] = 0;
$total_overdue[$a] = 0;
$total_advance[$a] = 0;
}
// echo $advance[$a]."<br/>";
/**********/
$sql = "update collection set
amt_paid =:amt_paid,advance=:advance,total_advance=:total_advance,overdue =:overdue,
total_overdue =:total_overdue,total_loan_bal =:total_loan_bal,date_coll=:date_coll,
who_cre=:who_cre
where coll_id=:coll_id";
$stmt = $d->prepare($sql);
// if the following two values are not being used elsewhere in the code, don't make array entries for them
$date_time = date("Y-m-d H:i:s");
$who_create = 123;
// for($b=0;$b<count($coll_id);$b++) {
// just use a foreach loop
foreach(array_keys($coll_id) as $b)
{
//$stmt->bindparam(':amt_paid',$amt_paid[$b],PDO::PARAM_INT);
//$stmt->bindparam(':advance',$advance[$b],PDO::PARAM_INT);
//$stmt->bindparam(':total_advance',$total_advance[$b],PDO::PARAM_INT);
//$stmt->bindparam(':overdue',$overdue[$b],PDO::PARAM_INT);
//$stmt->bindparam(':total_overdue',$total_overdue[$b],PDO::PARAM_INT);
//$stmt->bindparam(':total_loan_bal',$loan_ba[$b],PDO::PARAM_INT);
//$stmt->bindparam(':date_coll',$date_coll[$b],PDO::PARAM_INT);
//$stmt->bindparam(':who_cre',$who_cre[$b],PDO::PARAM_INT);
//$stmt->bindparam(':coll_id',$coll_id[$b],PDO::PARAM_INT);
// provide two literal values
// if the following array entries are not being used elsewhere in the code, no need to be creating unnecessary arrays
//$who_cre[$b] = 123;
//$date_coll[$b] = date("Y-m-d H:i:s");
// just use implicit binding
$stmt->execute([
':amt_paid'=>$amt_paid[$b],
':advance'=>$advance[$b],
':total_advance'=>$total_advance[$b],
':overdue'=>$overdue[$b],
':total_overdue'=>$total_overdue[$b],
':total_loan_bal'=>$loan_ba[$b],
':date_coll'=>,$date_time,
':who_cre'=>$who_create,
':coll_id'=>$coll_id[$b]
]);
// if($stmt == true){
// this means that the prepare call was successful, not that the query actually executed
// based on the error in the title, it is likely that exceptions are being used for errors
// if exceptions are being used, if you are at this point, then the update query was successful
// no conditional logic is necessary to display this message
echo "saved successfully";
// }
} // this is the end of the $coll_id loop
} // this is the end of $loan_ball_coll_gen[$a] == 0
/**********/
} // this is the end of the $loan_id loopthanks for the support but only the first record saves but the rest of the records dont below is the error
( ! ) Notice: Undefined offset: 1 in C:\wamp64\www\eddy\test5.php on line 80
( ! ) Notice: Undefined offset: 1 in C:\wamp64\www\eddy\test5.php on line 81
( ! ) Notice: Undefined offset: 1 in C:\wamp64\www\eddy\test5.php on line 82
( ! ) Notice: Undefined offset: 1 in C:\wamp64\www\eddy\test5.php on line 83
( ! ) Notice: Undefined offset: 1 in C:\wamp64\www\eddy\test5.php on line 84
( ! ) Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘advance’ cannot be null in C:\wamp64\www\eddy\test5.php on line 87
actually those are are from adavance, total_advance, overdue, total_overdue and loan_ba
The above information confirms what I already found -
At the point of processing one $loan_id value, there is only one entry with a 0 index in the $advance[$b], $total_advance[$b], $overdue[$b], $total_overdue[$b], and $loan_ba[$b] arrays. If the $coll_id array has more than one entry in it, the current code will attempt to access a 1, 2, … index in those arrays, which doesn’t exist.
You need to determine what the overall processing and logic should be. I gave two possibilities -
actually it was basically logic . I rewrote the code and now its ok