mysql update error

Discussion in 'Web Design and Development' started by twoodcc, Mar 26, 2011.

  1. twoodcc macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #1
    i am trying to add a new column to my database, and using php have a page that updates that new column.

    basically what i have is as follows:

    "update data set likes = likes+1 where id = '$id'"

    and that gives me an error.

    but if i run the same command, except on another column, it works. any ideas on what is going on?
     
  2. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #2
    If you post the sql error it would be easier to debug.
     
  3. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #3
    well all it says is you have an error in your mysql syntax near "likes = likes +1 where id = '12'"

    but if i change likes to another field then it works. it just doesn't work with the new column that i added to the database that already has data in it. i made the column a bigint size 15.
     
  4. firestarter, Mar 27, 2011
    Last edited: Mar 27, 2011

    firestarter macrumors 603

    firestarter

    Joined:
    Dec 31, 2002
    Location:
    Green and pleasant land
    #4
    It's probably taken the value 'null' since it's a new column - and complaining 'cos you can't add 1 to null.

    Why not update every likes value to 0 before you set out?

    Any reason you chose bigint? Any reason you chose not to use 'not null'?
     
  5. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #5
    well when i first added the column, i put the default 0, and it didn't work. i then deleted it and added it again, without making the default 0, same result. i even put in a 1 for a few records, and the same thing happens.

    this is really bothering me that it doesn't work
     
  6. firestarter macrumors 603

    firestarter

    Joined:
    Dec 31, 2002
    Location:
    Green and pleasant land
    #6
    What do you get when you select likes from your table with Id='12'?
     
  7. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #7
    i get 0. and i get 1 for the one that i inserted 1 directly into the database.
     
  8. firestarter macrumors 603

    firestarter

    Joined:
    Dec 31, 2002
    Location:
    Green and pleasant land
    #8
    That's strange.

    How are you testing this... with code, or with an interactive tool?

    Just wondering if it's really null, but your test code interprets that as 1.

    I can't think of any other good reasons why the update would fail. You haven't put some sort of index on 'like' have you?
     
  9. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #9
    yeah i can't figure it out either. i kept looking at my code over and over, and don't see why it's failing.

    i'm using php for this. basically, i'm using GET with a link. you click the link, it pulls in the info from the database, and updates the 'like' field based on the 'id' from the link.

    i do not have an index on like.
     
  10. designguy79 macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #10
    Maybe "likes" is a reserved name? Try escaping that, so something like

    update data set `likes`=`likes`+1 where id = '$id'

    HTH
     
  11. twoodcc thread starter macrumors P6

    twoodcc

    Joined:
    Feb 3, 2005
    Location:
    Right side of wrong
    #11
    that was it! thanks! now where is that on my keyboard?
     

Share This Page