1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Negative values in INTEGER column?

Discussion in 'MySQL' started by fatabbot, Feb 1, 2007.

  1. #1
    Hi,

    I'm trying to add a value of -100 in an integer field, like this:


    insert into mytable (field1, field2) values ("x", -100);


    But everytime the value converts to 0.
    Value out of range, data truncated ....

    When i manually enter -100 in the field, it works. But not via an sql query...
    What could be the problem here?
     
    fatabbot, Feb 1, 2007 IP
  2. ForumJoiner

    ForumJoiner Active Member

    Messages:
    762
    Likes Received:
    32
    Best Answers:
    0
    Trophy Points:
    83
    #2
    There is a free program, called SQL Manager Lite. You can find a link here.

    Open your database using this program and try to do a query. If it still does not work, please export the table structure and post it here, to replicate the problem.
     
    ForumJoiner, Feb 8, 2007 IP
  3. drewbe121212

    drewbe121212 Well-Known Member

    Messages:
    733
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    125
    #3
    1.) Make sure the table column is not "UNSIGNED". Unsigned columns can increase their maximum size by starting at the value of 0 and above rather then going negative.

    2.) Make sure the integeter you are trying to insert has the quotes around it. '-100'.
     
    drewbe121212, Feb 9, 2007 IP
  4. LeopardAt1

    LeopardAt1 Well-Known Member

    Messages:
    880
    Likes Received:
    126
    Best Answers:
    0
    Trophy Points:
    135
    #4
    Can I see your exact sql statement that produces the '0' value.

    I had the same exact problem last night programming a script of mine.

    I fixed it by putting double quotee around the number vs. single quotes.

    So :

    INSERT INTO db (my_int) VALUES ("-100")


    Cheers
     
    LeopardAt1, Feb 9, 2007 IP
  5. fatabbot

    fatabbot Well-Known Member

    Messages:
    559
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    138
    #5
    I found the problem, the field was checked as UNSIGNED. That way the absolute value was stored |-5| = 5 ...
     
    fatabbot, Feb 10, 2007 IP
  6. ForumJoiner

    ForumJoiner Active Member

    Messages:
    762
    Likes Received:
    32
    Best Answers:
    0
    Trophy Points:
    83
    #6
    How did you manage to store -100 in an unsigned field?


    That means that it actually did not work correctly even manually in the first place, because you could not store -100, but 100, which is very different?
     
    ForumJoiner, Feb 10, 2007 IP
  7. fatabbot

    fatabbot Well-Known Member

    Messages:
    559
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    138
    #7
    The field was unsigned so i was unable to store negative values. -100 became 100.
    Now i changed the field to SIGNED, and i can store -100.
     
    fatabbot, Feb 11, 2007 IP