need help with mysqli update

P

propz

New Member
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?
----------------------------------------------------------------
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>
 
Cromewell

Cromewell

Administrator
Staff member
PHP: 
<?php
$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;
echo $amtpaid, "\n", $amtdue, "\n", $prevbal, "\n", $latechg, "\n", $secdep, "\n", $damage, "\n", $courtcost, "\n", $nsf, "\n", $hudpay;
?>
Outputs
Code: 
0
0
0
0
0
0
0
0
0
In otherwords, all your variables are being set to 0.

I'm pretty sure you want to set these variables to the result of your query. Since I don't really know your table I'm assuming it has the same number of columns as you have values you are setting. Note, the (int) cast is unnecessary if you are using numeric fields as opposed to text/varchar
PHP: 
$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)){
    $id=$row[0];
    $amtpaid = (int)$row[1];
    $amtdue = (int)$row[2];
    $prevbal = (int)$row[3];
    $latechg = (int)$row[4];
    $secdep = (int)$row[5];
    $damage = (int)$row[6];
    $courtcost = (int)$row[7];
    $nsf = (int)$row[8];
    $hudpay = (int)$row[9];
    $comments = $row[10];
    $paiddate = $row[11];
    $due = $amtdue + $prevbal + $latechg + $secdep + $damage + $courtcost + $nsf - $hudpay;

    
    if ($amtpaid < $due) {
      $prevbal = $amtdue - $amtpaid;
      $latechg = $latechg + 35.00;
      $amtpaid = 0;
      $secdep = 0;
      $damage = 0;
      $courtcost = 0;
      $nsf = 0;
      $hudpay = 0;
      $comments = ' ';
    }
    //continue on with the remaining logic
  }
}
 
P

propz

New Member
The below gets the following results.
----------------------------------------------
$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)){
$id=$row[0];
$amtpaid = (int)$row[1];
$amtdue = (int)$row[2];
$dueday = $row[3];
$prevbal = (int)$row[4];
$latechg = (int)$row[5];
$secdep = (int)$row[6];
$damage = (int)$row[7];
$courtcost = (int)$row[8];
$nsf = (int)$row[9];
$hudpay = (int)$row[10];
$comments = $row[11];
$paidday = $row[12];
$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;
$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;
$latechg = 0;
$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', dueday = DATE_ADD(dueday, INTERVAL 1 MONTH), prevbal = '$prevbal',
latechg = '$latechg', secdep = '$secdep', damage = '$damage', courtcost = '$courtcost', nsf = '$nsf',
hudpay = '$hudpay', comments = '$comments', paidday = '$paidday' WHERE amtpaid !=0";

if(mysqli_query($link, $sql)){ echo "record was updated successfully."; } // line 71
else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); }
// Close connection
mysqli_close($link);
}
}
?>
---------------------------------------
record was updated successfully.
Warning: mysqli_query(): Couldn't fetch mysqli in C:\xampp\htdocs\kirkplace\refreshpayments.php on
line 71

Warning: mysqli_error(): Couldn't fetch mysqli in C:\xampp\htdocs\kirkplace\refreshpayments.php on
line 72
ERROR: Could not able to execute UPDATE paytbl SET amtpaid = '0', dueday = DATE_ADD(dueday,
INTERVAL 1 MONTH),
prevbal = '0', latechg = '2058', secdep = '0', damage = '0', courtcost = '0', nsf = '0', hudpay = '0',
comments = ' ', paidday = '0.00' WHERE amtpaid !=0.
Warning: mysqli_close(): Couldn't fetch mysqli in C:\xampp\htdocs\kirkplace\refreshpayments.php on
line 74

Warning: mysqli_query(): Couldn't fetch mysqli in C:\xampp\htdocs\kirkplace\refreshpayments.php on
line 71

Warning: mysqli_error(): Couldn't fetch mysqli in C:\xampp\htdocs\kirkplace\refreshpayments.php on
line 72
ERROR: Could not able to execute UPDATE paytbl SET amtpaid = '0', dueday = DATE_ADD(dueday,
INTERVAL 1 MONTH),
prevbal = '0', latechg = '2058', secdep = '0', damage = '0', courtcost = '0', nsf = '0', hudpay = '0',
comments = ' ', paidday = '0.00' WHERE amtpaid !=0.
Warning: mysqli_close(): Couldn't fetch mysqli in C:\xampp\htdocs\kirkplace\refreshpayments.php on
line 74
 
Cromewell

Cromewell

Administrator
Staff member
This is usually closing the connection then trying to use it, but if the above is the complete code, then you aren't connecting to the database and that could be it. This bit from your original code.
PHP: 
<?php

$link = mysqli_connect("localhost", "root", "", "homedb");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
You must log in or register to reply here.
Top