MySQL Query Is Not Updating Records

by on November 2nd, 2007

Did you wonder sometimes that why mySQL Update Command do not update records when the syntax of update query is perfectly alright? This is the problem I stuck with couple of times and I just wondered and looked for solution but seriously never found any solution to that anywhere untill I found the answer to this question and I was relieved.

The problem was that I was naming the variables used in mysql query which were used by mysql itself.
Suppose that if there are seven variables in total number and mistakenly you named just one of them which is used by mysql, even than mysql update query will not work also and will not update records in the database.

For example by, from, status etc.

Insert mysql query will run and add records in database but update query will not be running.

Incorrect Query
UPDATE expenditures set by='$by', amount='$amount', from='$from', purpose='$purpose' where id='$action';
Correct Query
UPDATE expenditures set bby='$bby', amount='$amount', ffrom='$ffrom', purpose='$purpose' where id='$action';
Testing Your Query

The trick is to echo the query which you are running and test it in phpMyAdmin query window. i.e.

$myquery="UPDATE expenditures set bby='$bby', amount='$amount', ffrom='$ffrom', purpose='$purpose' where id='$action'";
echo "$myquery";

By echoing that you will get something like this

UPDATE expenditures set bby='javed', amount='2000', ffrom='home', purpose='books' where id='2'
Finding Error

This is just an example. Alright now copy that query (echo result) and go to phpMyAdmin and open that database and i suggest that table also which is going to be updated and click query button. In query window write that result and try running that.
If its right it will update successfully but if that’s not right than it will give some error saying something like;

syntax error
You have some error near UPDATE expenditures set by=’$by’, amount=’$amount’, from=’$from’, purpose=’$purpose’ … in line 1

If it gives error than it will highlight those variables also which are not right and there you can guess that what’s wrong.

Always when your query is not running and giving error it is recommended that you echo that query and run that in phpMyAdmin query window and see cautiously the error message. Hope it will help.