SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    HAHA!
    Join Date
    Mar 2006
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Mysql data type error

    I've created this column:

    Code:
    paylines TINYINT( 50 ) NOT NULL ,
    this will contain numeric values from 1-20 at least.

    However when I added "3" as a value in that column I got this mysql error :

    Code:
    #
    
    Error: 1366 SQLSTATE: HY000 (ER_TRUNCATED_WRONG_VALUE_FOR_FIELD)
    
    Message: Incorrect %s value: '%s' for column '%s' at row %ld
    I don't understand shouldn't TINYINT work for that field?
    Cheap web hosting directory listing the cheapest web hosting

    Submit articles to an article directory

  2. #2
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you using sprintf() or something with '%s' denoting a string value?
    You should be using an integer value for a TINYINT column, NOT a string.
    Echo the SQL query and see what value it's trying to insert.

  3. #3
    HAHA!
    Join Date
    Mar 2006
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well I'm not putting this in via php.

    I set up this table using:

    Code:
    CREATE TABLE blah(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    htmldesc TEXT NOT NULL ,
    type VARCHAR ( 50 ) NOT NULL ,
    paylines TINYINT ( 50 ) NOT NULL ,
    screen VARCHAR( 150 ) NOT NULL
    )
    I put in "3", an integer, via the phpmyadmin insert interface and got above error code?
    Cheap web hosting directory listing the cheapest web hosting

    Submit articles to an article directory

  4. #4
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have thought that PHPMyAdmin would have coped with this, but maybe it's something to do with the 'type' field. As that's a reserved word, it would need to be quoted with backticks.

    Does it show the query (if so, could you post that for me to look at)? Try running it directly in the SQL tab rather than using the insert form and see if that gives anything useful.

  5. #5
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ignore that - I was wrong, type isn't a reserved word after all, so it's not likely to be anything to do with that. I'd still be interested in seeing the query though.

  6. #6
    HAHA!
    Join Date
    Mar 2006
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    will rename the type and get back to you on whether or not that changed anything.

    ah ok ignore above then

    I'll try that tomorrow and give you an update and show you the query.
    Cheap web hosting directory listing the cheapest web hosting

    Submit articles to an article directory

  7. #7
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're not adding quotes around the values, are you?

    I created a test table with the same schema and did an insert in PHPMyAdmin and it worked fine. The only way I could get it to fail was with quotes around the 3, like this: "3" - in which case I got the warning that I would have expected for a string:
    Warning: #1366 Incorrect integer value: '"3"' for column 'paylines' at row 1

  8. #8
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how can a tinyint be 50 characters long, with a max value of 127 ?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the number in parentheses after an integer datatype has nothing to do with the range of values it can hold

    the number in parentheses specifies how many digits to display when using ZEROFILL

    TINYINT can hold values only up to 127 (or 255 if UNSIGNED) regardless of the number in parentheses

    a value of 3 in a TINYINT(50) column with ZEROFILL would display like this:
    00000000000000000000000000000000000000000000000003

    i agree, TINYINT(50) just sounds too weird, although it is quite legal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    HAHA!
    Join Date
    Mar 2006
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahh, ok I'm beginning to see the light. So for a max value of 50 I would only need TINYINT (2) ?
    Cheap web hosting directory listing the cheapest web hosting

    Submit articles to an article directory

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, that's not right at all

    50 is only 2 digits wide, but TINYINT(2) will still allow you to store 127 (or 255 if UNSIGNED)

    you want to restrict the highest value that you can store in the TINYINT column?

    there are 3 ways to do it

    1. with a CHECK constraint -- unfortunately, mysql doesn't support these (boo, hiss)

    2. with application code -- wrong place for it, but it works nicely

    3. with a FK to a table that has all the numbers from 1 to 50 in it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    HAHA!
    Join Date
    Mar 2006
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no I don't want to restrict the highest value to 50 I just want to make sure that the data type setting is as efficient as possible for something that will in all likelihood not exceed 50 in the future.
    Cheap web hosting directory listing the cheapest web hosting

    Submit articles to an article directory

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Hostpitable View Post
    I just want to make sure that the data type setting is as efficient as possible for something that will in all likelihood not exceed 50 in the future.
    so use TINYINT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    HAHA!
    Join Date
    Mar 2006
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that did the trick. No errors anymore. Thanks for everyone's input!
    Cheap web hosting directory listing the cheapest web hosting

    Submit articles to an article directory

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    just for the record, TINYINT(2) and TINYINT(50) do not produce errors either

    they don't do what you might have thought they do, but they are still valid

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •