[RESOLVED] need help with php update code

ckdoublenecks

Noone seems to be able to figure out why this does not update my database records. It says "records updated" but they aren't. The 5 records are there, the id's are unique and 2 of them should have been updated. At one time all records were updated but as I've followed suggestions and changed code it's gotten worse. Any help?

<?php 
// this code should update the database and gives a confirmation 
$ud_id=$_POST['ud_id']; 
$ud_amtpaid=$_POST['ud_amtpaid']; 
$ud_rentdue=$_POST['ud_rentdue']; 
$ud_prevbal=$_POST['ud_prevbal']; 
$ud_misc=$_POST['ud_misc']; 
$ud_tentpay=$_POST['ud_tentpay']; 
$ud_hudpay=$_POST['ud_hudpay']; 
$ud_datepaid=$_POST['ud_datepaid']; 
if(($ud_misc + $ud_prevbal + $ud_rentdue) == $ud_amtpaid) 
{ 
$misc = 0; 
$prevbal = 0; 
$amtpaid = 0; 
$tentpay = 0; 
$datepaid = " "; 
} 
mysql_connect(localhost,root,""); 
mysql_select_db(test) or die( "Unable to select database"); 
$query="UPDATE testtable SET amtpaid=".$ud_amtpaid.", prevbal=".$ud_prevbal.
", misc=".$ud_misc.", tentpay=".$ud_tentpay.", hudpay=".$ud_hudpay.", datepaid='".$ud_datepaid."'  WHERE id=".$ud_id." LIMIT 1";
echo $query."\n<br>";
mysql_query($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: " . mysql_error());

echo "Records Updated";
mysql_close();
?>

ixalmida

Why are you using "LIMIT 1"? And could you also post how the query outputs? It is helpful to see the actual data being updated.

Res

If it does not return an error but also does not update results, you probably have something in your where clause that is selecting 0 records. MySQL won't send an error if it updates with 0 results.

Easiest solution is to echo your sql statement before you run it. You can then inspect your where clause to make sure the ID is getting passed. You can then manually execute the query and change the data a little bit to see how your script needs to be adjusted.

The limit 1 shouldn't be an issue since you are trying to update only one record.

ckdoublenecks

Actually I am trying to update every record that meets the comparison. I removed the limit1 part; someone suggested it. Below is the error message - I don't know what it means. Below that is a copy of my database.

Parse error: syntax error, unexpected T_NS_SEPARATOR in C:\xampp\htdocs\hofiles\refreshprerent.php on line 23

apt dep name amtpaid rentdue prevbal misc tentpay hudpay datepaid

A1 name1 530 530 0 0 530 0 8/5/2010
A2 name2 150 500 350 0 150 0 8/4/2010
A3 name3 437 555 0 -118 437 0 8/2/2010
A4 name4 505 530 -25 0 505 0 8/1/2010
B1 name5 490 490 10 0 490 0 8/3/2010

Res

The error is something else in the script, something to do with name spaces. Not sure how that slipped in to your code if you're not using them, but check out the line it says, or go back to the version you posted here in the forum. Once you have it working let's see the output from the sql query that gets echoed when you make the update. The thought is that since it's telling you it succeed but nothing is happening, the id you are passing is '', 0 records match this so it updates nothing successfully.

ckdoublenecks

this eror when I put the limit1 back in

UPDATE testtable SET amtpaid=, prevbal=, misc=, tentpay=, hudpay=, datepaid='' WHERE id= LIMIT 1 
A fatal MySQL error occured. 
Query: UPDATE testtable SET amtpaid=, prevbal=, misc=, tentpay=, hudpay=, datepaid='' WHERE id= LIMIT 1
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' prevbal=, misc=, tentpay=, hudpay=, datepaid='' WHERE id= LIMIT 1' at line 1

bradgrafelman

There's your problem - the variables don't have values. You'll want to handle this gracefully, e.g. if the value is empty then assign a default value (NULL, 0, empty string, whatever is appropriate).

Also note that user-supplied data should never be placed directly into a SQL query string else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize the data with a function such as [man]mysql_real_escape_string/man (for string data).

ixalmida

Here's a useful bit of code I use for building query strings:

$query_string = "INSERT INTO table (fields) VALUES (";
// Or for update query...
// $query_string = "UPDATE table SET ";
foreach($_POST as $key => $value)
  {
  // Skip the submit button
  if($key == "submit") continue;
  if(is_numeric($value))
    {
    $query_string .= "$value,";
    // Or for update queries:
    // $query_string .= "$key = $value,";
    }
  else
    {
    $value = mysql_real_escape_string($value);
    $query_string .= "'$value',";
    // Or for update queries:
    // $query_string .= "$key = '$value',";
    }
  }
// Don't forget to remove extra comma...
$query_string = substr($query_string, 0, -1).")";
// Or for update query...
// $query_string = substr($query_string, 0, -1)." WHERE condition";

I believe SQL will accept numbers enclosed in quotes, so you don't even necessarily need to process numbers differently, but I find it helps me when I go about troubleshooting the query to see the numbers as numbers.

Oh...also, this also assumes you've named your form fields exactly the same as the database fields.

ckdoublenecks

This below is my latest attempt and below that the errors encountered. A copy of the database is at the bottom: there is no form

<?php 
error_reporting(E_ALL);
// this code should update the database and gives a confirmation 
$ud_id=$_POST['ud_id']; 
$ud_amtpaid=$_POST['ud_amtpaid']; 
$ud_rentdue=$_POST['ud_rentdue']; 
$ud_prevbal=$_POST['ud_prevbal']; 
$ud_misc=$_POST['ud_misc']; 
$ud_tentpay=$_POST['ud_tentpay']; 
$ud_hudpay=$_POST['ud_hudpay']; 
$ud_datepaid=$_POST['ud_datepaid']; 
if(($ud_misc + $ud_prevbal + $ud_rentdue) == $ud_amtpaid) 
{ 
$misc = 0; 
$prevbal = 0; 
$amtpaid = 0; 
$tentpay = 0; 
$datepaid = " "; 
} 
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 FROM  testtable";
$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;
}

// We know $result is valid and rows exist to fetch
while ($row = mysql_fetch_assoc($result)) {
   print_r($row);
}

$query = sprintf( 
   "UPDATE testtable 
   SET 
      amtpaid='%s', 
      prevbal='%s', 
      misc='%s', 
      tentpay='%s', 
      hudpay='%s', 
      datepaid='%s' 
   WHERE id=%d", 
   mysql_real_escape_string($ud_amtpaid), 
   mysql_real_escape_string($ud_prevbal), 
   mysql_real_escape_string($ud_misc), 
   mysql_real_escape_string($ud_tentpay), 
   mysql_real_escape_string($ud_hudpay), 
   mysql_real_escape_string($ud_datepaid), 
   (int) $ud_id 
); 
// $result = mysql_query($query) or die(mysql_error()."<br />\n$query"); 
// $query="UPDATE testtable SET id=".$ud_id.", amtpaid=".$ud_amtpaid.", prevbal=".$ud_prevbal.", // misc=".$ud_misc.", tentpay=".$ud_tentpay.", hudpay=".$ud_hudpay.", datepaid='".$ud_datepaid."'  // WHERE id=".$ud_id.";
// echo $query."\n<br>";
// on numeric values you can delete the single quotes. 


mysql_query($query); 
echo "Records Updated"; 
mysql_close(); 
?>

Notice: Undefined index: ud_id in C:\xampp\htdocs\hofiles\refreshprerent.php on line 4

Notice: Undefined index: ud_amtpaid in C:\xampp\htdocs\hofiles\refreshprerent.php on line 5

Notice: Undefined index: ud_rentdue in C:\xampp\htdocs\hofiles\refreshprerent.php on line 6

Notice: Undefined index: ud_prevbal in C:\xampp\htdocs\hofiles\refreshprerent.php on line 7

Notice: Undefined index: ud_misc in C:\xampp\htdocs\hofiles\refreshprerent.php on line 8

Notice: Undefined index: ud_tentpay in C:\xampp\htdocs\hofiles\refreshprerent.php on line 9

Notice: Undefined index: ud_hudpay in C:\xampp\htdocs\hofiles\refreshprerent.php on line 10

Notice: Undefined index: ud_datepaid in C:\xampp\htdocs\hofiles\refreshprerent.php on line 11
could not run query (sql); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM testtable' at line 1

apt dep name amtpaid rentdue prevbal misc tentpay hudpay datepaid

A1 name1 530 530 0 0 530 0 8/5/2010
A2 name2 150 500 350 0 150 0 8/4/2010
A3 name3 437 555 0 -118 437 0 8/2/2010
A4 name4 505 530 -25 0 505 0 8/1/2010
B1 name5 490 490 10 0 490 0 8/3/2010

ckdoublenecks

Thanks for your help. I am running this locally on one machine. Please take a look at this last attempt:
The error is below the code:

<?php 
// this code should update the database and gives a confirmation 
$ud_id=$_POST['ud_id']; 
$ud_amtpaid=$_POST['ud_amtpaid']; 
$ud_rentdue=$_POST['ud_rentdue']; 
$ud_prevbal=$_POST['ud_prevbal']; 
$ud_misc=$_POST['ud_misc']; 
$ud_tentpay=$_POST['ud_tentpay']; 
$ud_hudpay=$_POST['ud_hudpay']; 
$ud_datepaid=$_POST['ud_datepaid']; 
if(($ud_misc + $ud_prevbal + $ud_rentdue) == $ud_amtpaid) 
{ 
$misc = 0; 
$prevbal = 0; 
$amtpaid = 0; 
$tentpay = 0; 
$datepaid = " "; 
} 
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 * FROM  testtable";
$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;
}
// We know $result is valid and rows exist to fetch
while ($row = mysql_fetch_assoc($result)) {
   print_r($row);
}
$query="UPDATE testtable SET amtpaid=$ud_amtpaid, prevbal=$ud_prevbal, misc=$ud_misc, tentpay=$ud_tentpay, hudpay=$ud_hudpay, datepaid='$ud_datepaid' WHERE id=$ud_id";
echo "Records Updated"; 
mysql_close(); 
?>

Array ( [id] => 1 [dep] => [name] => name1 [apt] => [amtpaid] => 530 [rentdue] => 530 [prevbal] => 0 [misc] => 0 [tentpay] => 530 [hudpay] => 0 [datepaid] => 8/5/2010 ) Array ( [id] => 2 [dep] => [name] => name2 [apt] => [amtpaid] => 150 [rentdue] => 500 [prevbal] => 350 [misc] => 0 [tentpay] => 150 [hudpay] => 0 [datepaid] => 8/4/2010 ) Array ( [id] => 3 [dep] => [name] => name3 [apt] => [amtpaid] => 437 [rentdue] => 555 [prevbal] => 0 [misc] => -118 [tentpay] => 437 [hudpay] => 0 [datepaid] => 8/2/2010 ) Array ( [id] => 4 [dep] => [name] => name4 [apt] => [amtpaid] => 505 [rentdue] => 530 [prevbal] => -25 [misc] => 0 [tentpay] => 505 [hudpay] => 0 [datepaid] => 8/1/2010 ) Array ( [id] => 5 [dep] => [name] => name5 [apt] => [amtpaid] => 490 [rentdue] => 490 [prevbal] => 10 [misc] => 0 [tentpay] => 490 [hudpay] => 0 [datepaid] => 8/3/2010 ) Records Updated
__________________

ixalmida

That's not an error. That's exactly the output you'd expect.

I would get rid of the code to list the current contents of the table since it is irrelevant to anything that's going on. I'd rather see a print out of what's in $query at the end to see if the data is valid.

ckdoublenecks

Everytime I run a test I go back and, thru phpmyadmin, look at the table to see if there have been changes; it says "records updated" but none are. I got the below idea from a book - can it possiblly work if I code it right? As it is, I get the same results.

<?
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, 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 ";
 mysql_query($sql) or die("Update query failed.");
echo "Records Updated"; 
mysql_close(); 
?>

ixalmida

Firstly, you are using two variables to hold your query: $query and $sql. Where you are trying to update, you are actually running a select query from before. I would pick a variable name and stick with it.

Secondly, when you are troubleshooting a query, you need more verbose information than just "Update query failed" or "Records updated." Try this instead:

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, 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()." Records Updated";
mysql_close();

ckdoublenecks

Warning: mysql_num_rows() expects exactly 1 parameter, 0 given in C:\xampp\htdocs\hofiles\updatetest.php on line 30
Records Updated - was displayed. When I checked the database table, all were changed?

ixalmida

Sorry, change the following line to:

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

That line tells you the number of affected rows from the previous insert, update, or delete query. If it says, "0 Records Updated" then you know nothing changed.

ckdoublenecks

OK, we're getting closer than ever. 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 and that was what I was questioning, also how do I SET the datepaid to " " instead of 0?

ixalmida

If datepaid is a numeric field (i.e. - INT), you can't set it equal to a string. You may, however, be able to set it to NULL if the field doesn't have a NOT NULL constraint on it.

In PHP, you can use if(empty($row['datepaid'])) to test for NULL, 0, or an empty string.

I can't troubleshoot the WHERE clause without seeing the query. Please echo out the query string and paste it here and I'll see if I can find the issue.

ckdoublenecks

I'm sorry, I'm really fighting my way thru this. Can you give me an example?

ixalmida

My suggestion was meant for future reference. In other words, you needn't worry whether the database stores values as NULL, 0, or an empty string because when you pull the value out of MySQL (with mysql_fetch_assoc), you can check for any of the three values using the empty() statement.

Please add...

echo $query;

...to the end of your code and paste the results here so I can help you with your query.

ckdoublenecks

5 Records UpdatedUPDATE testtable SET amtpaid=0, prevbal=0, misc=0,tentpay=0, hudpay=0, datepaid=0