Values of $variables are changing on UPDATE

Hey Guys,
This is really nutty.  I'll do my best not to confuse you guys.

I'm trying to harvest some data from MySQL and then UPDATE it.
The db value in "total_payments_ThisYear" is 602
BUT the results of echo line 11 are as follows:
amount_due=600
total_payments_ThisYear=1202        -- This value should be 602
ud_total_payments_ThisYear=1802  -- This value should be 1202

Line 8 presents the correct value of $ud_total_payments_ThisYear at 1202.
But it looks like the code on line 9 is changing the value of $ud_total_payments_ThisYear to 1802.
If I disable line 9 I get the correct values returned: 1202.
But of course I can't UPDATE the db without it.
What's with line 9???
I appreciate the help.
Thanks,
Sas
//GET & UPDATE total_payments_ThisYear
$amount_due="600";
$ThisYear = date("Y");
	$total_payments_ThisYear="SELECT total_payments$ThisYear FROM customers WHERE cusomerID= '".$cusomerID."'";
	$total_payments_ThisYear=mysql_query($total_payments_ThisYear)or die(mysql_error());
	$total_payments_ThisYear=mysql_fetch_array($total_payments_ThisYear)or die(mysql_error());
	$total_payments_ThisYear="$total_payments_ThisYear[0]";
	$ud_total_payments_ThisYear=$total_payments_ThisYear+$amount_due;
   mysql_query(" UPDATE customers SET total_payments$ThisYear = '".$ud_total_payments_ThisYear."'  WHERE cusomerID ='".$cusomerID."' ");

echo "line 11 
<BR>amount_due=$amount_due
<BR>total_payments_ThisYear=$total_payments_ThisYear
<BR>ud_total_payments_ThisYear=$ud_total_payments_ThisYear";

Open in new window

LVL 1
sasnaktivAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BradyPrincipal Data EngineerCommented:
You are reassigning the variable $total_payments_ThisYear over and over in your code.

Assign it once and try not to use the same variable name anywhere else.

Should use something like this

$query = "SELECT total_payments".$ThisYear." FROM customers WHERE cusomerID= '".$cusomerID."'";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
	$total_payments_this_year = $row['total_payments'.$thisYear];
	echo $total_payments_this_year.'<br />';
	}

Open in new window


That is a tidier way of doing a query. You should be using PDO or something similar as mysql is now depreciated.

In any case your problem is you are overwriting information previously set in a variable.
0
sasnaktivAuthor Commented:
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
0
sasnaktivAuthor Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Mark BradyPrincipal Data EngineerCommented:
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;");

Open in new window

0
Chris StanyonWebDevCommented:
If you only need to update your table by adding a new value to your existing value, then you can skip the SELECT statement and just run the UPDATE:

$amount_due = 600; // if this is a number, drop the quotes!
$ThisYear = date('Y');
$sqlStr = "UPDATE customers SET total_payments$ThisYear = total_payments$ThisYear + $amount_due WHERE cusomerID ='$cusomerID'";

Open in new window

This does assume your table columns are named total_payments2013, total_payments2014 etc (which is not a great design).

Also, as Elvin says drop using the mysql_* functions. Switch to PDO or mySQLi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sasnaktivAuthor Commented:
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
0
Mark BradyPrincipal Data EngineerCommented:
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

Open in new window

0
Chris StanyonWebDevCommented:
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);
?>

Open in new window

0
Ray PaseurCommented:
I think you may be running the wrong script.  It's a common mistake, probably confusion in the names of the test scripts or directories.  Look carefully at this error message:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

And look carefully at this line of code:

$total_payments_ThisYear=mysql_fetch_array($total_payments_ThisYear)or die(mysql_error());

For the moment, ignore the fact that the code is wrong, just note that the PHP message identifies a different function, so the message you showed us could not have come from the code you posted.

You really need to get something of a foundation in how PHP and MySQL work together.  Things like function return values and error visualization are vitally important.  I'm going to recommend two articles that can help you move forward with this project.

General foundation and learning resources:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Specific examples of how to use MySQL and how to convert to one of the supported extensions:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Best of luck, ~Ray
0
sasnaktivAuthor Commented:
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."' ");

Open 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

Open in new window

0
Ray PaseurCommented:
Do you mean "cusomerID" or "customerID?"
0
Ray PaseurCommented:
This code snippet, copied from http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28253967.html#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;"

Open 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.
http://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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
Chris StanyonWebDevCommented:
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.
0
Mark BradyPrincipal Data EngineerCommented:
Yeah I'm out on this one. I tried though :)
0
Ray PaseurCommented:
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."
0
sasnaktivAuthor Commented:
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
0
Ray PaseurCommented:
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!

http://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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.