yukimushu — 2011-07-28T10:02:11-04:00 — #1
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 . '
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 — 2011-07-28T10:12:55-04:00 — #2
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 — 2011-07-28T11:05:42-04:00 — #3
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.