not sure re. WHERE clause

[ckdoublenecks]

I could use some help with this code.  I am trying to update this database table directly (no form). It said "5 records updated". There are 5 records in the database and I checked and all 5 had been changed. Problem is that only 3 should have been changed. The WHERE clause in the SELECT statement must have been ignored. Also how do I SET the datepaid to " " instead of 0?

 

<?php
if (!$conn = @mysql_connect(localhost,root,"")) {
echo 'could not connect to MYSQL: ' .mysql_error();
exit;
}
if(!@mysql_select_db('test')) {
   echo 'Unable to select database: ' .mysql_error();
   exit;
}

// mysql_query returns false on failure
  $sql = "SELECT amtpaid, rentdue, prevbal, misc, tentpay, hudpay, datepaid FROM  testtable WHERE misc + prevbal + rentdue = amtpaid";

$result = @mysql_query($sql);
if(!$result) {
    echo "could not run query (sql); " .mysql_error();
   exit;
}
// if we expect rows, let's make sure some exist
if(mysql_num_rows($result) < 1) {
   echo "no rowsmatch your query, Please try again";
   exit;
}
$query="UPDATE testtable SET amtpaid=0, prevbal=0, misc=0,tentpay=0, hudpay=0, datepaid=0 ";

// Run query (with verbose error message)...
mysql_query($query) or die(mysql_error()."<br />\n".$query);

// Get number of affected rows (if any)...
echo mysql_affected_rows($conn)." Records Updated";
mysql_close();
?>

 

 

 

 

[trq]

Of course it was ignored, your don't use any of the data retrieved from the SELECT.

[ckdoublenecks]

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("test", $con);
// for every tenant record, if the rentdue, prevbal & misc equals the 
// amtpaid, clear the misc, prevbal, amtpaid, tentpay and datepaid
mysql_query("UPDATE testtable SET amtpaid = '0',  prevbal = '0', misc = '0', tentpay = '0', datepaid = ' '
WHERE misc + prevbal + rentdue = amtpaid");
mysql_close($con);
?> 

[cliffdodger]

On a side note you do know right if this is on a server open to any outside access you should create a mysql user and not use the mysql root user? Standard best practice.

[ckdoublenecks]

yes, thanks, this is local