Help with sql update

[mbuna]

I have the following php code:
echo $iyear;

$query = "UPDATE users set
title='$ititle',
lastname='$ilastname',
firstname='$ifirstname',
middleinit='$imiddleinit',
year='$iyear',
hs_lastname='$ihs_lastname',
hs_firstname='$ihs_firstname',
street1='$istreet1',
street2='$istreet2',
city='$icity',
state='$istate',
postal='$ipostal',
country='$icountry',
share_email='$ishare_email',
occupation='$ioccupation',
marital_status='$imarital_status',
kids='$ikids',
spouse_lastname='$ispouse_lastname',
spouse_firstname='$ispouse_firstname',
spouse_middleinit='$ispouse_middleinit',
spouse_born_year='$ispouse_born_year',
phone='$iphone',
webpage='$iwebpage',
comment='$icomment'
WHERE user_name='$valid_user'";
$query_result = @mysql_query($query);
All of the $ixxxx vars are coming from a web form.

$iyear echo's correctly with what the user has entered into the forum,
however it is not updated in the table. All other fields are updated
correctly, but year is emptied.

Any ideas on where I can look?


Here is the table it's trying to update.

describe users;

MemberID int(11) PRI NULL auto_increment
user_name varchar(100)
title varchar(10)
lastname varchar(40)
firstname varchar(40)
middleinit char(1)
year int(4) YES NULL
hs_lastname varchar(40)
hs_firstname varchar(40)
street1 varchar(40)
street2 varchar(40)
city varchar(40)
state char(2)
postal varchar(9)
country char(2)
share_email char(1)
occupation varchar(100)
marital_status char(1)
kids char(1)
thenphoto varchar(200)
nowphoto varchar(200)
spouse_lastname varchar(40)
spouse_firstname varchar(40)
spouse_middleinit char(1)
spouse_born_year varchar(4)
spouse_photo varchar(200)
phone varchar(20)
webpage varchar(200)
comment text
Password varchar(40)
PassQuestion varchar(100)
LastUpdated date 0000-00-00
PassAnswer varchar(100)
Active char(1) N
ActiveCode varchar(15)

[NC]

mb***@yahoo.com wrote:

I have the following php code:

$query = "UPDATE users set
title='$ititle', ... year='$iyear', ... comment='$icomment'
WHERE user_name='$valid_user'";
$query_result = @mysql_query($query);

$iyear echo's correctly with what the user has entered into
the forum, however it is not updated in the table. All other
fields are updated correctly, but year is emptied.

Any ideas on where I can look?
In the table definition:
year int(4) YES NULL

Earlier versions of MySQL interpret single quotes around
a value as an indication of that value being a text. Since
you are trying to update a numeric field, you might try to
remove single quotes around $iyear...

Cheers,
NC

[mbuna]

No luck on that. I'll also tried converting the field to char. with
the same results.

Running: MySQL 4.1.9-standard

The edit page loads fine and displays the current year correctly. When
you submit it then displays the updated year correctly but the field
ends up empty in the database. So it is trying to store something but
failing.

[NC]

[e-mail] wrote:

No luck on that. I'll also tried converting the field to char. with
the same results.

Running: MySQL 4.1.9-standard

OK, scratch that... There is a MySQL function called YEAR(),
so if you have a field called year, you must refer to it as
`year` (that is, include it in backticks)...

Cheers,
NC

[mbuna]

hmm, that was probably it. I renamed the field to "classyear" and it's
working now. Thanks for the help!

[Jerry Sievers]

mb***@yahoo.com writes:
[snip]

$query_result = @mysql_query($query);

Get rid of that @ before the above call if you want to see
error/warning messages (if any).

Then either check your error logs and/or browser window if
display_errors is ON.

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/