SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Your opinion on inserting integers surrounded by single quotes

    Hey folks,

    I'm just curious as to what your opinion is regarding performing a query such as this:

    Code MySQL:
    INSERT INTO table SET id = '1' AND live = ' . $live . '

    verses this

    Code MySQL:
    INSERT INTO table SET id = 1 AND live = 1

    Personally the omission of single quotes makes more sense to me as the value being entered is an integer type, so it would seem bad practice (even though it works) to send it to the database as a string.

    I realise there are certain benefits to the first query such as the fact that if by some chance the $live field was not set then it would no error as the query string would remain intact, however I feel this should be thought of in the error checking.

    Does anyone else have any opinion and care to share their thoughts?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by yukimushu View Post
    Personally the omission of single quotes makes more sense to me as the value being entered is an integer type, so it would seem bad practice (even though it works) to send it to the database as a string.
    i agree with this

    using quotes relies upon implicit casting (from string to integer), which every programmer will tell you is a minefield

    also, not every database will do this, some will just give you an error message about a datatype mismatch

    another thing you should be careful with is using proprietary syntax

    INSERT SET works only in mysql and will fail in other database systems, so the code is not portable

    however, mysql also supports the standard sql syntax INSERT VALUES

    whenever mysql supports both the standard sql way of doing something and its own proprietary way, you should always use the standard sql way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    another thing you should be careful with is using proprietary syntax

    INSERT SET works only in mysql and will fail in other database systems, so the code is not portable

    however, mysql also supports the standard sql syntax INSERT VALUES

    whenever mysql supports both the standard sql way of doing something and its own proprietary way, you should always use the standard sql way
    I was not aware of this and is something I've often thought about but never investigated. I'll have to make an effort to change my habits to using INSERT VALUES from now on.


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
  •