Values of $variables are changing on UPDATE

Thanks for responding Elvin
But unfortunately the "echo $total_payments_this_year." on line 6 of your code results in the following:
"Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource"


Line 8 of my code presents the correct value of $ud_total_payments_ThisYear at 1202.
But I think line 9 is changing the value of $ud_total_payments_ThisYear to 1802.
If I delete line 9 the value of $ud_total_payments_ThisYear is correct at 1202.

Any ideas on how to fix this?
Sas

Sorry Elvin I made some errors applying your code to my page.
I've got it working without errors now, but now I'm not getting any value in "total_payments_ThisYear"
It's empty, but the db is 602

So we're still not there yet.
Sas

Ok well I think I need to know a bit more about your table design.

This line might be giving a problem
total_payments".$ThisYear."

What is the actual field (column) name you are trying to retrieve? The way you had it it would be

total_payments2013

Is that a valid field name in your table?

Try this

$amount_due = '600';
$ThisYear = date('Y');

$query = "SELECT total_payments" .$ThisYear. " FROM customers WHERE cusomerID = '".$cusomerID."'";

$result = mysql_query($query)or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
    $total_payments_ThisYear = $row['total_payments'.$ThisYear];
}

$ud_total_payments_ThisYear = ($total_payments_ThisYear + $amount_due);

mysql_query("UPDATE customers SET total_payments." .$ThisYear. " = '".$ud_total_payments_ThisYear."'  WHERE cusomerID = '".$cusomerID."' LIMIT 1;");

Select allOpen in new window

Hi Guys,
No matter how I skin this thing. (Yes I'm getting the correct value now with Elvin's code.)
It is NOT updating the db!
Neither Elvin's or Chris Stanyon's updates the db.

The code makes sense, but it's beyond me why it's not updating.

Also, I don't understand how to implement "drop using the mysql_* functions. Switch to PDO or mySQLi ".

And yes folks the column is named "total_payments2013".
Sas

Well you query looks fine but you need to make sure what you have set in your variables is actually what you are expecting. Try doing this next line and post the result in here. Put this line directly before your query to do the update

echo "UPDATE customers SET total_payments." .$ThisYear. " = '".$ud_total_payments_ThisYear."'  WHERE cusomerID = '".$cusomerID."' LIMIT 1;"

die(); // that will kill the script

Select allOpen in new window

It's difficult to know why your database isn't updating since we're only seeing part of your code. Here's a simple example of how to do it using the PDO library:

<?php 
//Display all errors
error_reporting(E_ALL);
ini_set('display_errors', 1);

//connect to the database
$dbh = new PDO("mysql:host=localhost;dbname=yourDB", 'yourUsername', 'yourPassword');

//prepare the SQL Statement
$myQuery = $dbh->prepare("UPDATE customers SET total_payments" . date('Y') . " = total_payments" . date('Y') . " + :amountDue WHERE customerID = :customerID");

//set your data here
$data = array(
	'amountDue' => 600,
	'customerID' => 2
);
   
//execute the query
$myQuery->execute($data);
?>

Select allOpen in new window

Hi Guys,
First let me say that I appreciate the help and attention. I've tried every suggestion that I understand.
But so far, none of the solutions offered seem to solve the problem that's been driving me nuts for the past 2 days.

So I'd like you to take another look at my original code (simplified a bit).
Lines 1 through 5 deliver the proper values. They function perfectly.
Line 6 does not UPDATE the db, OR maybe it's UPDATING it with the original value found in the column "total_payments". I just can't tell.

The code all seems very logical (even the solutions you guys present) but I'm not getting anywhere.
Please help,
Sas

	$total_payments="SELECT total_payments FROM customers WHERE customerID= '".$customerID."'";
	$total_payments=mysql_query($total_payments)or die(mysql_error());
	$total_payments=mysql_fetch_array($total_payments)or die(mysql_error());
	$total_payments="$total_payments[0]";
	$ud_total_payments=$total_payments+$amount_due;
   mysql_query(" UPDATE customers SET total_payments' = '".$ud_total_payments."'  WHERE customerID ='".$customerID."' ");

Select allOpen in new window

Oh yes, Elvin's suggestion returns a value of "1"  That's it.

echo "UPDATE customers SET total_payments." .$ThisYear. " = '".$ud_total_payments_ThisYear."'  WHERE cusomerID = '".$cusomerID."' LIMIT 1;"

die(); // that will kill the script

Select allOpen in new window

Do you mean "cusomerID" or "customerID?"

This code snippet, copied from https://www.experts-exchange.com/questions/28253967/Values-of-variables-are-changing-on-UPDATE.html?anchorAnswerId=39537655#a39537655 does not echo "1" at all.  It contains a PHP parse error.  Taken together, the different outputs you're getting when compared to what we get when we copy your code and run it -- that all leads me to believe even more strongly that you're not testing the right script.  Either that or you're not showing us the true code and the true output.

<?php // RAY_temp_sasnaktiv.php
error_reporting(E_ALL);

echo "UPDATE customers SET total_payments." .$ThisYear. " = '".$ud_total_payments_ThisYear."'  WHERE cusomerID = '".$cusomerID."' LIMIT 1;"

Select allOpen in new window

There are right ways and wrong ways of handling queries, and your latest example is almost the dictionary picture of doing it all wrong.  In fact, I have used samples like this in the classroom as a pop quiz: "Tell me why this is wrong."  I do not have time to give you all of the answers about why this is the wrong way of doing things, but I think you can agree if it takes you more than a few minutes to write these few lines of code, some of the "best practices" may have been lost.

Here is the article showing what's wrong.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html

Here is the article showing how to do the queries the right way, including error checking that will immediately tell you what went wrong, if anything.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

The code you've been posting is littered with errors (or at least discrepencies), so to get to the bottom of this, bin it and start again!

Throughout this post, you've said that your column name is total_payments2013, but in the code that you say works you're selecting and updating a column called total_payments. You also have discrepencies in the column names and variables used in the WHERE clause cusomerID and customerID. You need to be precise about these things.

Create a file and copy in the PDO code that I've posted. You will need to change the details to match your own database, username and password, and you will need to manually enter a customerID to match something in your database. Run that script and check the database - it will have updated a record (asssuming table names and column names are correct)

If you do all of this and it still doesn't work, report back here with any error messages generated, an exact copy of the code you've used (don't include your username and password), and the table structure of your customer table.

Yeah I'm out on this one. I tried though :)

I'm out, too.  It's probably wise to stop work and hire a professional programmer.  The only alternative would be to take the time and study necessary to become a professional programmer.  That will take a couple of years, so hiring the professional is the shortest and surest path to success.

As the great oil well fire fighter Red Adair once said, "If you think it's expensive to hire a professional, just wait till you hire an amateur."

Okay, I solved the problem.
Thanks for the attention everyone.

One question though, Elvin says drop using the mysql_* functions. Switch to PDO or mySQLi
It seems as though PDO is much too advanced for me, and mySQLi is very similar to mysql.
Ray's article makes sense, but changing hundreds of files from mysql to mysqli seems next to impossible !!!

Could it be as simple as a search & replace the "mysql_* functions" with "mysqli_* functions"? Or is that too much to ask?
Sas

Could it be as simple...

No, sorry, it is not that simple.  If you have hundreds of files with MySQL functions you are the poster child for why PHP is trying to get away from the procedural, cowboy-hacker code and move to the object-oriented design.  MySQLi is easiest to implement, but it is not at all like the MySQL extension.  Choose the MySQLi object-oriented code designs and you will have much less programming to rewrite and retest.  And make sure that you have good test scripts before you start changing the code base!

https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

And good luck! ~Ray