SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Must I use an integer column type instead of a varchar column for numbers?

    Hello;

    I am working on a MySQL database table for baseball game scores.

    Does anybody know why I need to use one of the integer column types to store numbers?

    If I do a SELECT clause and apply the gettype() function to numbers extracted from an integer column types they come out as type "string" - not type "integer."

    I am trying to get an integer column to have a default value of empty. However, the table automatically inserts a "0" in an empty column. It displays on the page as a "0" when there should be a blank space there (before the games are played it needs to be an empty space since there's not yet a score).

    If I use a varchar column type it will default to an empty cell. If I extract the number from the varchar column type using a select function is also comes out as type "string."

    Basically, varchar does what I need with a number. The integer column type does not.

    I am trying to figure out if I use varchar column types for the numerical scores, instead of the integer type, will it cause problems later on?

    I would appreciate any information that someone might have.

    Thanks.
    .

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Volitics View Post
    Does anybody know why I need to use one of the integer column types to store numbers?
    because that's the best datatype for whole numbers (like scores... unless you can be awarded a half of a point, for example)


    Quote Originally Posted by Volitics View Post
    If I do a SELECT clause and apply the gettype() function to numbers extracted from an integer column types they come out as type "string" - not type "integer."
    that would be a problem with whatever language this "gettype" function belongs to

    presumably it's php, in which case you should ask that question in the php forum


    Quote Originally Posted by Volitics View Post
    I am trying to get an integer column to have a default value of empty. However, the table automatically inserts a "0" in an empty column.
    you need to declare the column with NULL instead of NOT NULL


    in summary, don't use VARCHAR, use SMALLINT (unless your scores can exceed 32768, then use INTEGER)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937;

    Thanks for responding to my question. NULL is what I needed to know. I set the column default setting to NULL and it eliminated the zeros.

    Thanks again.
    .


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
  •