Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote: Personally I don't use any quotes for the numeric types, and single quotes for everything else. Ie: Thanks, Brandon. I understand then that quote type is a matter of taste. I always use double quotes in PHP and I've only recently started putting ticks around table and column names. I'll stick to your convention of no quotes around numerics and single quotes around everything else. Have a terrific week! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
myisamchk error (duplicate key records)
While running a -rq on a large table, I got the following error: myisamchk: warning: Duplicate key for record at 54381140 against record at 54380810 How do I find which records are duplicated (without doing the typical self-join or having cnt(*)1 query)? This table has 144 million rows, so that's not really feasible. myisamchk --block-search # looked promising, but I can't find any documentation on how to use it properly. I tried myisamchk -b 54381140 table-name but it really doesn't do anything. I posted this identical question here six years ago, and I have the same problem again. I still can't find ANY documentation on --block-search or how to use it. Has anything changed in six years? Thanks.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen dotanco...@gmail.com wrote:
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote:
Personally I don't use any quotes for the numeric types, and single
quotes
for everything else. Ie:
Thanks, Brandon. I understand then that quote type is a matter of
taste. I always use double quotes in PHP and I've only recently
started putting ticks around table and column names. I'll stick to
your convention of no quotes around numerics and single quotes around
everything else.
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 03:00, schrieb Hank:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error
what ugly style - if it is not numeric and you throw it to the database
you are one of the many with a sql-injection because if you are get
ivalid values until there you have done no sanitize before and do not here
$sql=INSERT into table VALUES ( . (int)$id . ,' .
mysql_real_escape_string($val) . ');
or using a abstraction-layer (simple self written class)
$sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val)
. ');
all other things in the context of hand-written queries are all the nice one we
read every
day in the news and should NOT recommended because the next beginner reading
this makes all
the mistakes again
signature.asc
Description: OpenPGP digital signature
