Help with sql update

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mbuna@yahoo.com
    #1

    Help with sql update

    I have the following php code:


    echo $iyear;

    $query = "UPDATE users set
    title='$ititle' ,
    lastname='$ilas tname',
    firstname='$ifi rstname',
    middleinit='$im iddleinit',
    year='$iyear',
    hs_lastname='$i hs_lastname',
    hs_firstname='$ ihs_firstname',
    street1='$istre et1',
    street2='$istre et2',
    city='$icity',
    state='$istate' ,
    postal='$iposta l',
    country='$icoun try',
    share_email='$i share_email',
    occupation='$io ccupation',
    marital_status= '$imarital_stat us',
    kids='$ikids',
    spouse_lastname ='$ispouse_last name',
    spouse_firstnam e='$ispouse_fir stname',
    spouse_middlein it='$ispouse_mi ddleinit',
    spouse_born_yea r='$ispouse_bor n_year',
    phone='$iphone' ,
    webpage='$iwebp age',
    comment='$icomm ent'
    WHERE user_name='$val id_user'";
    $query_result = @mysql_query($q uery);


    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_firstnam e varchar(40)
    spouse_middlein it char(1)
    spouse_born_yea r 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)

    Tags: None
    • NC
      #2
      Re: Help with sql update

      mbuna@yahoo.com wrote:[color=blue]
      >
      > I have the following php code:
      >
      > $query = "UPDATE users set
      > title='$ititle' ,[/color]
      ...[color=blue]
      > year='$iyear',[/color]
      ...[color=blue]
      > comment='$icomm ent'
      > WHERE user_name='$val id_user'";
      > $query_result = @mysql_query($q uery);
      >
      > $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?[/color]

      In the table definition:
      [color=blue]
      > year int(4) YES NULL[/color]

      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

        Comment

        • mbuna@yahoo.com
          #3
          Re: Help with sql update

          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.

            Comment

            • NC
              #4
              Re: Help with sql update

              m...@yahoo.com wrote:[color=blue]
              >
              > No luck on that. I'll also tried converting the field to char. with
              > the same results.
              >
              > Running: MySQL 4.1.9-standard[/color]

              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

                Comment

                • mbuna@yahoo.com
                  #5
                  Re: Help with sql update

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

                    Comment

                    • Jerry Sievers
                      #6
                      Re: Help with sql update

                      mbuna@yahoo.com writes:
                      [snip]
                      [color=blue]
                      > $query_result = @mysql_query($q uery);[/color]

                      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/

                        Comment

                        Previous template Next
                        Working...