Your opinion on inserting integers surrounded by single quotes

[yukimushu]

Hey folks,

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

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

verses this

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?

[r937]

@yukimushu

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

[yukimushu]

@r937

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.