Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Brandon Phelps]

Personally I don't use any quotes for the numeric types, and single quotes for everything
else.  Ie:

UPDATE mytable SET int_field = 5 WHERE id = 3;
SELECT id FROM mytable WHERE int_field = 5;
UPDATE mytable SET varchar_field = 'Test' WHERE id = 3;
SELECT id FROM mytable WHERE varchar_field = 'Test';
UPDATE mytable SET datetime_field = '2011-09-18 00:00:00' WHERE id = 3;

If you are using PHP you may need to escape the single quotes if your php string is in
single quotes:
$query = 'UPDATE mytable SET varchar_field = \'Test\' WHERE id = 3'

But if you are doing interpolation and your string is in double quotes, you should not
need to escape:
$query = "UPDATE $table_name SET varchar_field = 'Test' WHERE id = 3"

Some people prefer to use back quotes on field names such as:
$query = "UPDATE `mytable` SET `varchar_field` = 'Test' WHERE `id` = 3"

And some people prefer to put numeric fields in quotes as well, although it is not
necessary:
UPDATE mytable SET int_field = '5' WHERE id = '3';

On 9/18/11 5:00 AM, Dotan Cohen wrote:
> I am somewhat confused as to the proper way to place quotes around
> arguments in INSERT and SELECT statements. I also don't see where this
> is made explicit in the fine manual.
>
> If the column is type int, is it preferable to use single, double, or
> no quotes on INSERT from the mysql cli?
> If the column is type int, is it preferable to use single, double, or
> no quotes on SELECT from the mysql cli?
> If the column is type int, is it preferable to use single, double, or
> no quotes on INSERT from PHP?
> If the column is type int, is it preferable to use single, double, or
> no quotes on SELECT from PHP?
> Is it the same for decimal and float?
>
> If the column is type varchar, is it preferable to use single or
> double quotes on INSERT from the mysql cli?
> If the column is type varchar, is it preferable to use single or
> double quotes on SELECT from the mysql cli?
> If the column is type varchar, is it preferable to use single or
> double quotes on INSERT from PHP?
> If the column is type varchar, is it preferable to use single or
> double quotes on SELECT from PHP?
> Is it the same for text and blob?
> Also, in PHP often I see code examples with the variable wrapped in
> curly brackets, inside single quotes. What is the purpose of the curly
> brackets? Here is such an example:
> $query="INSERT INTO names (name) VALUE ('{$userName}')";
>
> If the column is type datetime, is it preferable to use single or
> double quotes on INSERT from the mysql cli?
> If the column is type datetime, is it preferable to use single or
> double quotes on SELECT from the mysql cli?
> If the column is type datetime, is it preferable to use single or
> double quotes on INSERT from PHP?
> If the column is type datetime, is it preferable to use single or
> double quotes on SELECT from PHP?
> What if I am using the NOW() function?
>
> If the column is type set, is it preferable to use single or double
> quotes on INSERT from the mysql cli?
> If the column is type set, is it preferable to use single or double
> quotes on SELECT from the mysql cli?
> If the column is type set, is it preferable to use single or double
> quotes on INSERT from PHP?
> If the column is type set, is it preferable to use single or double
> quotes on SELECT from PHP?
>
> Thanks.
>