Need help with mysqli update

1Tsme1941

Howdy folks, my new code is below. I'm trying to update any records that have paid(amtpaid !=0, if
that's correct). I'm not sure how to code line 28-38 but I think it's wrong. The if statement at
line 45 is intended for records that have paid (amtpaid), as is everything. The duedate is not
advanced. I have tried to resolve several of these issues for some time. There is no error. Anyone

want to help/advise?

the code:

<html>
   <head>
      <title>Refresh payment database file</title>
   </head>
   <body>
   <?php

$link = mysqli_connect("localhost", "root", "", "homedb");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

 //MySqli Select Query
  $sql = "select * FROM paytbl where amtpaid !=0";
$result = mysqli_query($link,$sql);
if (!$result) {
    printf("Error: %s\n", mysqli_error($link));
    exit();
}else{
	$results = array();
	while($row = mysqli_fetch_array($result)){
		$results[] = $row;
	} 
 $id='id';
$amtpaid = (int)'amtpaid'; // line 28
$amtdue = (int)'amtdue';
$prevbal = (int)'prevbal';
$latechg = (int)'latechg';
$secdep = (int)'secdep';
$damage = (int)'damage';
$courtcost = (int)'courtcost';
$nsf = (int)'nsf';
$hudpay = (int)'hudpay';
$comments = 'comments';
$paiddate = 'paiddate'; // line 38
$due= 0;
// ---------------------------------------- 

$due = $amtdue + $prevbal + $latechg + $secdep + $damage + $courtcost + $nsf - $hudpay;
 
 /* if no pay or part pay, add $35 to latechg field and amount not paid to prevbal field */
    if ($amtpaid < $due) { $prevbal = $amtdue - $amtpaid; $latechg = $latechg + 35.00; // line 45 
$amtpaid = 0; $secdep = 0; $damage = 0;  $courtcost = 0;
$nsf = 0; $hudpay = 0; $comments = ' ';	}
/* if over-payment subtract over-payment from prevbal */
    if ($amtpaid > $due)  { $prevbal = $amtpaid - $amtdue;
$amtpaid = 0; $secdep = 0; $damage = 0;  $courtcost = 0;
$nsf = 0; $hudpay = 0; $comments = ' ';	}

     // Perform a query, check for error
$sql = "UPDATE paytbl SET 
amtpaid = '$amtpaid', duedate = DATE_ADD(duedate, INTERVAL 1 MONTH), prevbal = '$prevbal', 
latechg = '$latechg', secdep = '$secdep', damage = '$damage', courtcost = '$courtcost', nsf = '$nsf',  

hudpay = '$hudpay', comments = '$comments' WHERE amtpaid !=0";
 
if(mysqli_query($link, $sql)){ echo "record was updated successfully."; } 
else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); }
 // Close connection
mysqli_close($link); 
   }
?>
   </body></html>

NogDog

1Tsme1941 FYI: I edited your post to wrap your code block in the forum's [code]...[/code] tags.

Weedpacket

1Tsme1941 35.00

I hope these fields are decimal and not float.

NogDog

Not sure what your expectation is: do you want to do this calculation on each row returned by the DB query? If so, then all that stuff would typically go inside the while() loop where you do the mysql_fetch_array(). At that point, then you could refer to the value of a given field with $amtpaid = (int) $row['amtpaid']; for example (since $row is an array).

Weedpacket

The UPDATE will need to be inside the loop as well, and you need some way of distinguishing which row you're updating each time, using the table's primary key or some other unique property (at the moment, every row with amtpaid!=0 will end up containing the exact same values).

You'll also want to use prepared statements precisely because you'll be running the same UPDATE statement over and over again.

Then again, I think the entire thing, from SELECT, to loop, to calculations, to UPDATE statements, could probably be collapsed down into a single UPDATE statement that can be sent to MySQL and have it do all the work. Maybe two if you don't want to get clever with CASE expressions.

UPDATE paytbl SET
	amtpaid = 0,
	duedate = DATE_ADD(duedate, INTERVAL 1 MONTH),
	prevbal = (amtdue - amtpaid) * (CASE WHEN amtpaid < amtdue + prevbal + latechg + secdep + damage + courcost + nsf - hudpay THEN 1 ELSE -1 END),
	latechg = latechg + (CASE WHEN amtpaid < amtdue + prevbal + latechg + secdep + damage + courcost + nsf - hudpay THEN 35 ELSE 0 END),
	secdep = 0,
	damage = 0,
	courtcost = 0,
	nsf = 0,
	hudpay = 0,
	comments = ' '
WHERE amtpaid <> 0

pbismad

Regardless of this being a programming class assignment or if you were doing this for real, you should INSERT a new row of data for every transaction that affects an account balance. This will provide an audit trail, to let you determine if a programming mistake, accidental click/key-press, or nefarious activity caused an amount to change or to let you produce itemized accounting reports and customer/client billing.