[Resolved] UPDATE not updating

rcoursey

I have an UPDATE query to edit records in a mysql db. It does not work for some reason. THe code with all of the variables to update is:

$owner = trim($owner);
$owner = addslashes($owner);
$bannerpath = trim($bannerpath);
$bannerpath = addslashes($bannerpath);
$bannerlink = trim($bannerlink);
$bannerlink = addslashes($bannerlink);
$bannerheight = trim($bannerheight);
$bannerheight = addslashes($bannerheight);
$bannerwidth = trim($bannerwidth);
$bannerwidth = addslashes($bannerwidth);
$bannerloc = trim($bannerloc);
$bannerloc = addslashes($bannerloc);
$impressions = trim($impressions);
$impressions = addslashes($impressions);
$impressremain = trim($impressremain);
$impressremain = addslashes($impressremain);
$impresshist = trim($impresshist);
$impresshist = addslashes($impresshist);
$active = trim($active);
$active = addslashes($active);
echo $mode; //All echo statements show values!
echo $owner;
echo $bannerpath;
echo $bannerlink;
echo $bannerloc;
echo $id;
$result = dbquery("UPDATE banners SET owner='$owner', bannerpath='$bannerpath', bannerlink='$bannerlink', bannerheight='$bannerheight', bannerwidth='$bannerwidth', bannerloc='$bannerloc', impressions='$impressions', impressremain='$impressremain', impresshist='$impresshist', active='$active' WHERE bannerid='$id'");

The dbquery function works for other updates so I don't think this is the problem.

All of the echo statements are displaying the correct record info prior to the update.

I know the update is a long statement but a lot of info is in this table.

Thanks,

leatherback

What is the columtype for your banner width and height. you now try to insert it as text. I assume you have set them to interger or something?

rcoursey

The non-string types are:

bannerheight int;
banner width int;
bannerloc tinyint;
impress* int;
active tinyint(1);

laserlight

Your code snippet looks correct.
It is ok, perhaps even advisable, to enter integer values enclosed with single quotes in a query.

Maybe the reason could be something as simple as the bannerid tested with doesnt even exist?

Is there an error returned?
If so, what is it?

leatherback

Hi,

Then I think you can solve your problem by removing the single quotes around those vars. Try:

$result = dbquery("UPDATE banners SET owner='$owner', bannerpath='$bannerpath', bannerlink='$bannerlink', bannerheight=$bannerheight, bannerwidth=$bannerwidth, bannerloc=$bannerloc, impressions=$impressions, impressremain=$impressremain, impresshist=$impresshist, active=$active WHERE bannerid=$id");

rcoursey

I have the echo statements prior to the update. One of the items displays the bannerid as the variable $id and it is correct for the record to be updated.

I don't understand about the single quotes. I think I have them in the code I posted.

still looking for the key....

rcoursey

Removing the single quotes is not the solution. The query failed.

I have updated other int values using this same method. Is there a limit to the length of an update query?

laserlight

What was the error message when the query failed?

rcoursey

This is the error message:

Query failed - UPDATE banners SET owner='Any one', bannerpath='../banners/468-15.gif', bannerlink='', bannerheight=0, bannerwidth=0, bannerloc=1, impressions=0, impressremain=0, impresshist=0, active=0 WHERE bannerid=

leatherback

Hi,

I do not see the bannerid defined in your query..

rcoursey

It is in the WHERE clause.

laserlight

darn it, you should have left the quotes there, then we can easily see if indeed $id is NULL.

Could it be that you mistook the value of $bannerloc for $id ?

leatherback

Hey Laserlight,

About those quotes.. I ALWAYS leave them out when submitting integers. Sometimes they give problems for me. Why would it be better to keep them in?

laserlight

Well, there is a possibility that the data entered by the user is not numeric.

leatherback

Hm.. Yeah. But youwill get an error anyway, right!?

So then it doesn't matter. (Except then.. I've had some insertproblems. Removing single quotes solved it then.. Any idea why!?)

laserlight

Not really.

The string could be interpreted as the name of a table.field to look up the value, and this could lead to rather interesting manipulation of your data

on the other hand, if you do get an error with the quotes on (perhaps unable to implicitly typecast or ignore the string), you can easily deal with it.

rcoursey

I am inserting variables containing integers and not integers specifically. I removed the single quotes around them as leatherback suggested and when the query was performed the error message occured that I posted a few posts back.

Anyway, I went back thru the code in the form that submitts the data and found a problem. I did not assign a hidden variable to the $id variable while in the form. I was seeing the bannerloc in the echo and not the $id (both were 1 for this record).

I made the correction and the query worked!

Again, thanks for all the suggestions and help. I really appreciate it and am looking forward to being able to give back to this community when I learn more.

laserlight

yay, my analysis of the problem was correct! 🙂

well, your idea of writing the values of the variables to check is a good one, but can be improved by using:

echo 'id: ' . $id . '<br>';

so that you would know which value is for what variable.

rcoursey

Yes you were correct😃

I will take your advice on better describing my test variables.

regards