Page 1 of 1

I need help with update

Posted: Wed Feb 23, 2011 10:48 am
by ckdoublenecks
This program is to be used at the end of each month to begin
the month with fresh values. When executed there are no
errors, the message "Records have been updated" is displayed,
but no records are changed?

Code: Select all

<?php
$stat = mysql_connect("localhost","root","");
$stat = mysql_select_db("prerentdb");
$query = "SELECT name FROM payments Where apt='$apt'";
$stat = @mysql_fetch_assoc(mysql_query($query));
echo $stat["name"];
$result= mysql_query("select * from payments");
while($row=mysql_fetch_array($result))
{
$id=$row['id'];
$paidsum=$row['paidsum'];
$rentdue=$row['rentdue'];
$prevbal=$row['prevbal'];
$latechg=$row['latechg'];
// if no payment or partial payment, add $10 to latechg field
// and amount not paid to prevbal field
if ($paidsum < $rentdue)
{
$latechg = $latechg + 10;
$owe = $rentdue - $paidsum;
$prevbal = $prevbal + $owe;
}
// if over-payment and late field not "L", subtract over-payment
// from prevbal field
if ($paidsum > $rentdue && $late |= 'L')
{
$sub = $paidsum - $rentdue;
$prevbal = $prevbal - $sub;
}
// refresh every record - give every record the below values
$amtpaid = 0;
$hudpay = 0;
$tentpay = 0;
$datepaid = ' ';
$late = ' ';
$paidsum = 0;
$comments = ' ';

$sql = "UPDATE payments SET
$amtpaid, $prevbal, $hudpay, $tentpay, $datepaid, $late, $comments, $paidsum";

mysql_query($sql) ;
}
echo "Records have been updated";
?>

Re: I need help with update

Posted: Wed Feb 23, 2011 11:19 am
by mikecampbell
You probably need to surround some of the values in the SQL with single quotes. You could have other errors in your SQL, so you might want to add error checking.

Code: Select all

$sql = "UPDATE payments SET $amtpaid, $prevbal, $hudpay, $tentpay, '$datepaid', '$late', '$comments', $paidsum";

mysql_query($sql) ;
						
$err = mysql_error();
if ($err != "") {
   echo "Error in $sql: $err\n";
}