sql - When to use single quotes, double quotes, and backticks in MySQL

243

I am trying to learn the best way to write queries. I also understand the importance of being consistent. Until now, I have randomly used single quotes, double quotes, and backticks without any real thought.

Example:

$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';

Also, in the above example, consider thattable,col1,val1, etc. may be variables.

What is the standard for this? What do you do?

I've been reading answers to similar questions on here for about 20 minutes, but it seems like there is no definitive answer to this question.

714
578

Answer

Solution:

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list
831

Answer

Solution:

Backticks are generally used to indicate anidentifier and as well be safe from accidentally using the Reserved Keywords.

For example:

Use `database`;

Here the backticks will help the server to understand that thedatabase is in fact the name of the database, not the database identifier.

Same can be done for the table names and field names. This is a very good habit if you wrap your database identifier with backticks.

Check this answer to understand more about backticks.


Now about Double quotes & Single Quotes (Michael has already mentioned that).

But, to define a value you have to use either single or double quotes. Lets see another example.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);

Here I have deliberately forgotten to wrap thetitle1 with quotes. Now the server will take thetitle1 as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');

Now, in combination with PHP, double quotes and single quotes make your query writing time much easier. Let's see a modified version of the query in your question.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, using double quotes in the PHP, you will make the variables$val1, and$val2 to use their values thus creating a perfectly valid query. Like

$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

will make

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')
224

Answer

Solution:

In MySQL, these symbols are used to delimit a query` ," ,' and() .

  1. " or' are used for enclosing string-like values"26-01-2014 00:00:00" or'26-01-2014 00:00:00' . These symbols are only for strings, not aggregate functions likenow,sum, ormax.

  2. ` is used for enclosing table or column names, e.g.select `column_name` from `table_name` where id='2'

  3. ( and) simply enclose parts of a query e.g.select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh' .

996

Answer

Solution:

The string literals in MySQL and PHP are the same.

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

So if your string contains single quotes, then you could use double quotes to quote the string, or if it contains double quotes, then you could use single quotes to quote the string. But if your string contains both single quotes and double quotes, you need to escape the one that used to quote the string.

Mostly, we use single quotes for an SQL string value, so we need to use double quotes for a PHP string.

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2')";

And you could use a variable in PHP's double-quoted string:

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, '$val1', '$val2')";

But if$val1 or$val2 contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is whatmysql_real_escape_string is for. (Although a prepared statement is better.)

502

Answer

Solution:

There has been many helpful answers here, generally culminating into two points.

  1. BACKTICKS(`) are used around identifier names.
  2. SINGLE QUOTES(') are used around values.

AND as @MichaelBerkowski said

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is aMySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them.

EXAMPLE

123E10 is a valid identifier name but also a validINTEGER literal.

[Without going into detail how you would get such an identifier name], Suppose I want to create a temporary table named123456e6.

No ERROR on backticks.

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

However,123451a6 is a perfectly fine identifier name (without back ticks).

DB [XXX]> create temporary table 123451a6 (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

This is completely because1234156e6 is also an exponential number.

638

Answer

Solution:

In combination of PHP and MySQL, double quotes and single quotes make your query-writing time so much easier.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, suppose you are using a direct post variable into the MySQL query then, use it this way:

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";

This is the best practice for using PHP variables into MySQL.

623

Answer

Solution:

If table cols and values are variables then there are two ways:

With double quotes"" the complete query:

$query = "INSERT INTO $table_name (id, $col1, $col2)
                 VALUES (NULL, '$val1', '$val2')";

Or

 $query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.")
               VALUES (NULL, '".$val1."', '".$val2."')";

With single quotes'':

$query = 'INSERT INTO '.$table_name.' (id, '.$col1.', '.$col2.')
             VALUES (NULL, '.$val1.', '.$val2.')';

Use back ticks`` when a column/value name is similar to a MySQL reserved keyword.

Note: If you are denoting a column name with a table name then use back ticks like this:

`table_name`.`column_name` <-- Note: exclude. from back ticks.

808

Answer

Solution:

Single quotes should be used for string values like in the VALUES() list.

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

66

Answer

Solution:

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&A quite often.

In a nutshell; MySQL thinks you want to do math on its own table/column and interprets hyphens such as "e-mail" ase minusmail.


Disclaimer: So I thought I would add this as an "FYI" type of answer for those who are completely new to working with databases and who may not understand the technical terms described already.

948

Answer

Solution:

SQL servers and MySQL, PostgreySQL, Oracle don't understand double quotes("). Thus your query should be free from double quotes(") and should only use single quotes(').

Back-trip(`) is optional to use in SQL and is used for table name, db name and column names.

If you are trying to write query in your back-end to call MySQL then you can use double quote(") or single quotes(') to assign query to a variable like:

let query = "select id, name from accounts";
//Or
let query = 'select id, name from accounts';

If ther's awhere statement in your query and/or trying toinsert a value and/or anupdate of value which is string use single quote(') for these values like:

let querySelect = "select id, name from accounts where name = 'John'";
let queryUpdate = "update accounts set name = 'John' where id = 8";
let queryInsert = "insert into accounts(name) values('John')";

//Please not that double quotes are only to be used in assigning string to our variable not in the query
//All these below will generate error

let querySelect = 'select id, name from accounts where name = "John"';
let queryUpdate = 'update accounts set name = "John" where id = 8';
let queryInsert = 'insert into accounts(name) values("John")';

//As MySQL or any SQL doesn't understand double quotes("), these all will generate error.

If you want to stay out of this confusion when to use double quotes(") and single quotes('), would recommend to stick with single quotes(') this will include backslash() like:

let query = 'select is, name from accounts where name = \'John\'';

Problem with double(") or single(') quotes arise when we had to assign some value dynamic and perform some string concatenation like:

let query = "select id, name from accounts where name = " + fName + " " + lName;
//This will generate error as it must be like name = 'John Smith' for SQL
//However our statement made it like name = John Smith

//In order to resolve such errors use
let query = "select id, name from accounts where name = '" + fName + " " + lName + "'";

//Or using backslash(\)
let query = 'select id, name from accounts where name = \'' + fName + ' ' + lName + '\'';

If need further clearance do follow quotes in JavaScript

449

Answer

Solution:

It is sometimes useful to not use quotes... because this can highlight issues in the code generating the query... For example:

Where x and y are should always be integers...

SELECT * FROMtable WHERE x= AND y=0

Is a SQL syntax error... a little lazy but can be useful...

945

Answer

Solution:

In Simple Words:

  • Quotes (Single and Double) are used around strings.

  • Backticks are used around table and column identifiers.

Achieving Single & Double Quotes Together:

~ If we want to achieve this,

Output

Try this,




People are also looking for solutions to the problem: ftp_put(): can't open that file: no such file or directory
Source

Share


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.


Similar questions

Find the answer in similar questions on our website.

517 mysql - Update row count in php mysqli not working when updating multiple query
770 mysql - Asking With Login PHP Mysqli
226 mysql - How to find last inserted id , transaction, commit, rollback from the following connection php/MySQLi
87 mysql - php sql error message changed database context
245 mysql - Displaying records using SQL query from 2 or more combinations in php
436 MySQL date comparison from PHP
838 Php MySQLI statement skips the 'if' statement to 'else' without any possible mistake in database
675 Joining three tables using MySQL
249 How to put the output query from mySQL to php int variable
974 php - JQUERY autocomplete not refreshing data from mysql database
819
614

Answer

Solution:

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list
732

Answer

Solution:

Backticks are generally used to indicate anidentifier and as well be safe from accidentally using the Reserved Keywords.

For example:

Use `database`;

Here the backticks will help the server to understand that thedatabase is in fact the name of the database, not the database identifier.

Same can be done for the table names and field names. This is a very good habit if you wrap your database identifier with backticks.

Check this answer to understand more about backticks.


Now about Double quotes & Single Quotes (Michael has already mentioned that).

But, to define a value you have to use either single or double quotes. Lets see another example.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);

Here I have deliberately forgotten to wrap thetitle1 with quotes. Now the server will take thetitle1 as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');

Now, in combination with PHP, double quotes and single quotes make your query writing time much easier. Let's see a modified version of the query in your question.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, using double quotes in the PHP, you will make the variables$val1, and$val2 to use their values thus creating a perfectly valid query. Like

$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

will make

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')
424

Answer

Solution:

In MySQL, these symbols are used to delimit a query` ," ,' and() .

  1. " or' are used for enclosing string-like values"26-01-2014 00:00:00" or'26-01-2014 00:00:00' . These symbols are only for strings, not aggregate functions likenow,sum, ormax.

  2. ` is used for enclosing table or column names, e.g.select `column_name` from `table_name` where id='2'

  3. ( and) simply enclose parts of a query e.g.select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh' .

902

Answer

Solution:

The string literals in MySQL and PHP are the same.

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

So if your string contains single quotes, then you could use double quotes to quote the string, or if it contains double quotes, then you could use single quotes to quote the string. But if your string contains both single quotes and double quotes, you need to escape the one that used to quote the string.

Mostly, we use single quotes for an SQL string value, so we need to use double quotes for a PHP string.

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2')";

And you could use a variable in PHP's double-quoted string:

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, '$val1', '$val2')";

But if$val1 or$val2 contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is whatmysql_real_escape_string is for. (Although a prepared statement is better.)

64

Answer

Solution:

There has been many helpful answers here, generally culminating into two points.

  1. BACKTICKS(`) are used around identifier names.
  2. SINGLE QUOTES(') are used around values.

AND as @MichaelBerkowski said

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is aMySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them.

EXAMPLE

123E10 is a valid identifier name but also a validINTEGER literal.

[Without going into detail how you would get such an identifier name], Suppose I want to create a temporary table named123456e6.

No ERROR on backticks.

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

However,123451a6 is a perfectly fine identifier name (without back ticks).

DB [XXX]> create temporary table 123451a6 (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

This is completely because1234156e6 is also an exponential number.

333

Answer

Solution:

In combination of PHP and MySQL, double quotes and single quotes make your query-writing time so much easier.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, suppose you are using a direct post variable into the MySQL query then, use it this way:

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";

This is the best practice for using PHP variables into MySQL.

40

Answer

Solution:

If table cols and values are variables then there are two ways:

With double quotes"" the complete query:

$query = "INSERT INTO $table_name (id, $col1, $col2)
                 VALUES (NULL, '$val1', '$val2')";

Or

 $query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.")
               VALUES (NULL, '".$val1."', '".$val2."')";

With single quotes'':

$query = 'INSERT INTO '.$table_name.' (id, '.$col1.', '.$col2.')
             VALUES (NULL, '.$val1.', '.$val2.')';

Use back ticks`` when a column/value name is similar to a MySQL reserved keyword.

Note: If you are denoting a column name with a table name then use back ticks like this:

`table_name`.`column_name` <-- Note: exclude. from back ticks.

206

Answer

Solution:

Single quotes should be used for string values like in the VALUES() list.

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

381

Answer

Solution:

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&A quite often.

In a nutshell; MySQL thinks you want to do math on its own table/column and interprets hyphens such as "e-mail" ase minusmail.


Disclaimer: So I thought I would add this as an "FYI" type of answer for those who are completely new to working with databases and who may not understand the technical terms described already.

98

Answer

Solution:

SQL servers and MySQL, PostgreySQL, Oracle don't understand double quotes("). Thus your query should be free from double quotes(") and should only use single quotes(').

Back-trip(`) is optional to use in SQL and is used for table name, db name and column names.

If you are trying to write query in your back-end to call MySQL then you can use double quote(") or single quotes(') to assign query to a variable like:

let query = "select id, name from accounts";
//Or
let query = 'select id, name from accounts';

If ther's awhere statement in your query and/or trying toinsert a value and/or anupdate of value which is string use single quote(') for these values like:

let querySelect = "select id, name from accounts where name = 'John'";
let queryUpdate = "update accounts set name = 'John' where id = 8";
let queryInsert = "insert into accounts(name) values('John')";

//Please not that double quotes are only to be used in assigning string to our variable not in the query
//All these below will generate error

let querySelect = 'select id, name from accounts where name = "John"';
let queryUpdate = 'update accounts set name = "John" where id = 8';
let queryInsert = 'insert into accounts(name) values("John")';

//As MySQL or any SQL doesn't understand double quotes("), these all will generate error.

If you want to stay out of this confusion when to use double quotes(") and single quotes('), would recommend to stick with single quotes(') this will include backslash() like:

let query = 'select is, name from accounts where name = \'John\'';

Problem with double(") or single(') quotes arise when we had to assign some value dynamic and perform some string concatenation like:

let query = "select id, name from accounts where name = " + fName + " " + lName;
//This will generate error as it must be like name = 'John Smith' for SQL
//However our statement made it like name = John Smith

//In order to resolve such errors use
let query = "select id, name from accounts where name = '" + fName + " " + lName + "'";

//Or using backslash(\)
let query = 'select id, name from accounts where name = \'' + fName + ' ' + lName + '\'';

If need further clearance do follow quotes in JavaScript

10

Answer

Solution:

It is sometimes useful to not use quotes... because this can highlight issues in the code generating the query... For example:

Where x and y are should always be integers...

SELECT * FROMtable WHERE x= AND y=0

Is a SQL syntax error... a little lazy but can be useful...

390

Answer

Solution:

In Simple Words:

  • Quotes (Single and Double) are used around strings.

  • Backticks are used around table and column identifiers.

Achieving Single & Double Quotes Together:

~ If we want to achieve this,

Output

Try this,




People are also looking for solutions to the problem: mark bundle as not supporting multiuse
Source

Share


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.


Similar questions

Find the answer in similar questions on our website.

606 mysql - MySQLi Syntax Error (PHP) on INSERT using Variables
422 php - mysqli query with mysql function inside
47 php - Laravel 4 artisan migration unable to connect to remote mysql db [HY000] [1450] Access denied for user 'myuser'@'mypc' (using password: YES)
854 php - check if a child is a descendant using a single mysql query
152 mysqli - Getting the output of a msqli prepared statement as a variable in PHP
410 php - Laravel Request data when submitting a form is lost when running our project in IIS, what are the possible causes?
589 mysql - insert into multiple tables in php within a single form
748 mysql - Error trying to check if user exists in the database PHP
503 Use PHPExcel to store spreadsheets in a MySQL table and display said table on a PHP page with the spreadsheets in the generated tables
228 php - sql server query equivalent to mysql
84
820

Answer

Solution:

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list
737

Answer

Solution:

Backticks are generally used to indicate anidentifier and as well be safe from accidentally using the Reserved Keywords.

For example:

Use `database`;

Here the backticks will help the server to understand that thedatabase is in fact the name of the database, not the database identifier.

Same can be done for the table names and field names. This is a very good habit if you wrap your database identifier with backticks.

Check this answer to understand more about backticks.


Now about Double quotes & Single Quotes (Michael has already mentioned that).

But, to define a value you have to use either single or double quotes. Lets see another example.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);

Here I have deliberately forgotten to wrap thetitle1 with quotes. Now the server will take thetitle1 as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');

Now, in combination with PHP, double quotes and single quotes make your query writing time much easier. Let's see a modified version of the query in your question.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, using double quotes in the PHP, you will make the variables$val1, and$val2 to use their values thus creating a perfectly valid query. Like

$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

will make

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')
363

Answer

Solution:

In MySQL, these symbols are used to delimit a query` ," ,' and() .

  1. " or' are used for enclosing string-like values"26-01-2014 00:00:00" or'26-01-2014 00:00:00' . These symbols are only for strings, not aggregate functions likenow,sum, ormax.

  2. ` is used for enclosing table or column names, e.g.select `column_name` from `table_name` where id='2'

  3. ( and) simply enclose parts of a query e.g.select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh' .

663

Answer

Solution:

The string literals in MySQL and PHP are the same.

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

So if your string contains single quotes, then you could use double quotes to quote the string, or if it contains double quotes, then you could use single quotes to quote the string. But if your string contains both single quotes and double quotes, you need to escape the one that used to quote the string.

Mostly, we use single quotes for an SQL string value, so we need to use double quotes for a PHP string.

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2')";

And you could use a variable in PHP's double-quoted string:

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, '$val1', '$val2')";

But if$val1 or$val2 contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is whatmysql_real_escape_string is for. (Although a prepared statement is better.)

799

Answer

Solution:

There has been many helpful answers here, generally culminating into two points.

  1. BACKTICKS(`) are used around identifier names.
  2. SINGLE QUOTES(') are used around values.

AND as @MichaelBerkowski said

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is aMySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them.

EXAMPLE

123E10 is a valid identifier name but also a validINTEGER literal.

[Without going into detail how you would get such an identifier name], Suppose I want to create a temporary table named123456e6.

No ERROR on backticks.

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

However,123451a6 is a perfectly fine identifier name (without back ticks).

DB [XXX]> create temporary table 123451a6 (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

This is completely because1234156e6 is also an exponential number.

470

Answer

Solution:

In combination of PHP and MySQL, double quotes and single quotes make your query-writing time so much easier.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, suppose you are using a direct post variable into the MySQL query then, use it this way:

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";

This is the best practice for using PHP variables into MySQL.

45

Answer

Solution:

If table cols and values are variables then there are two ways:

With double quotes"" the complete query:

$query = "INSERT INTO $table_name (id, $col1, $col2)
                 VALUES (NULL, '$val1', '$val2')";

Or

 $query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.")
               VALUES (NULL, '".$val1."', '".$val2."')";

With single quotes'':

$query = 'INSERT INTO '.$table_name.' (id, '.$col1.', '.$col2.')
             VALUES (NULL, '.$val1.', '.$val2.')';

Use back ticks`` when a column/value name is similar to a MySQL reserved keyword.

Note: If you are denoting a column name with a table name then use back ticks like this:

`table_name`.`column_name` <-- Note: exclude. from back ticks.

58

Answer

Solution:

Single quotes should be used for string values like in the VALUES() list.

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

922

Answer

Solution:

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&A quite often.

In a nutshell; MySQL thinks you want to do math on its own table/column and interprets hyphens such as "e-mail" ase minusmail.


Disclaimer: So I thought I would add this as an "FYI" type of answer for those who are completely new to working with databases and who may not understand the technical terms described already.

826

Answer

Solution:

SQL servers and MySQL, PostgreySQL, Oracle don't understand double quotes("). Thus your query should be free from double quotes(") and should only use single quotes(').

Back-trip(`) is optional to use in SQL and is used for table name, db name and column names.

If you are trying to write query in your back-end to call MySQL then you can use double quote(") or single quotes(') to assign query to a variable like:

let query = "select id, name from accounts";
//Or
let query = 'select id, name from accounts';

If ther's awhere statement in your query and/or trying toinsert a value and/or anupdate of value which is string use single quote(') for these values like:

let querySelect = "select id, name from accounts where name = 'John'";
let queryUpdate = "update accounts set name = 'John' where id = 8";
let queryInsert = "insert into accounts(name) values('John')";

//Please not that double quotes are only to be used in assigning string to our variable not in the query
//All these below will generate error

let querySelect = 'select id, name from accounts where name = "John"';
let queryUpdate = 'update accounts set name = "John" where id = 8';
let queryInsert = 'insert into accounts(name) values("John")';

//As MySQL or any SQL doesn't understand double quotes("), these all will generate error.

If you want to stay out of this confusion when to use double quotes(") and single quotes('), would recommend to stick with single quotes(') this will include backslash() like:

let query = 'select is, name from accounts where name = \'John\'';

Problem with double(") or single(') quotes arise when we had to assign some value dynamic and perform some string concatenation like:

let query = "select id, name from accounts where name = " + fName + " " + lName;
//This will generate error as it must be like name = 'John Smith' for SQL
//However our statement made it like name = John Smith

//In order to resolve such errors use
let query = "select id, name from accounts where name = '" + fName + " " + lName + "'";

//Or using backslash(\)
let query = 'select id, name from accounts where name = \'' + fName + ' ' + lName + '\'';

If need further clearance do follow quotes in JavaScript

877

Answer

Solution:

It is sometimes useful to not use quotes... because this can highlight issues in the code generating the query... For example:

Where x and y are should always be integers...

SELECT * FROMtable WHERE x= AND y=0

Is a SQL syntax error... a little lazy but can be useful...

570

Answer

Solution:

In Simple Words:

  • Quotes (Single and Double) are used around strings.

  • Backticks are used around table and column identifiers.

Achieving Single & Double Quotes Together:

~ If we want to achieve this,

Output

Try this,




People are also looking for solutions to the problem: sqlstate[hy000] [1698] access denied for user 'root'@'localhost'
Source

Share


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.


Similar questions

Find the answer in similar questions on our website.

555 mysql - Forgot Password script PHP mysqli
425 php - "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'logs.user_ID' in 'on clause'" - when using LEFT JOIN
862 mysqli - With PHP MYSQL Prepared Statements and IFNULL() to return all records if value = ""
21 php - Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource and no database selected
41 mysql - SQL php query past, present, not future
435 mysql - Can't use method return value in write context in PHP
298 php - mysqli_real_escape_string makes program useless
119 php - Getting value from array in a for loop to use in MySQL
525 php - Constantly receiving error 235 in Laravel framework when deleting user from admin panel
997 Compare mysql datetime with php YY
363
251

Answer

Solution:

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list
693

Answer

Solution:

Backticks are generally used to indicate anidentifier and as well be safe from accidentally using the Reserved Keywords.

For example:

Use `database`;

Here the backticks will help the server to understand that thedatabase is in fact the name of the database, not the database identifier.

Same can be done for the table names and field names. This is a very good habit if you wrap your database identifier with backticks.

Check this answer to understand more about backticks.


Now about Double quotes & Single Quotes (Michael has already mentioned that).

But, to define a value you have to use either single or double quotes. Lets see another example.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);

Here I have deliberately forgotten to wrap thetitle1 with quotes. Now the server will take thetitle1 as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');

Now, in combination with PHP, double quotes and single quotes make your query writing time much easier. Let's see a modified version of the query in your question.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, using double quotes in the PHP, you will make the variables$val1, and$val2 to use their values thus creating a perfectly valid query. Like

$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

will make

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')
7

Answer

Solution:

In MySQL, these symbols are used to delimit a query` ," ,' and() .

  1. " or' are used for enclosing string-like values"26-01-2014 00:00:00" or'26-01-2014 00:00:00' . These symbols are only for strings, not aggregate functions likenow,sum, ormax.

  2. ` is used for enclosing table or column names, e.g.select `column_name` from `table_name` where id='2'

  3. ( and) simply enclose parts of a query e.g.select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh' .

121

Answer

Solution:

The string literals in MySQL and PHP are the same.

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

So if your string contains single quotes, then you could use double quotes to quote the string, or if it contains double quotes, then you could use single quotes to quote the string. But if your string contains both single quotes and double quotes, you need to escape the one that used to quote the string.

Mostly, we use single quotes for an SQL string value, so we need to use double quotes for a PHP string.

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2')";

And you could use a variable in PHP's double-quoted string:

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, '$val1', '$val2')";

But if$val1 or$val2 contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is whatmysql_real_escape_string is for. (Although a prepared statement is better.)

792

Answer

Solution:

There has been many helpful answers here, generally culminating into two points.

  1. BACKTICKS(`) are used around identifier names.
  2. SINGLE QUOTES(') are used around values.

AND as @MichaelBerkowski said

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is aMySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them.

EXAMPLE

123E10 is a valid identifier name but also a validINTEGER literal.

[Without going into detail how you would get such an identifier name], Suppose I want to create a temporary table named123456e6.

No ERROR on backticks.

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

However,123451a6 is a perfectly fine identifier name (without back ticks).

DB [XXX]> create temporary table 123451a6 (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

This is completely because1234156e6 is also an exponential number.

324

Answer

Solution:

In combination of PHP and MySQL, double quotes and single quotes make your query-writing time so much easier.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, suppose you are using a direct post variable into the MySQL query then, use it this way:

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";

This is the best practice for using PHP variables into MySQL.

587

Answer

Solution:

If table cols and values are variables then there are two ways:

With double quotes"" the complete query:

$query = "INSERT INTO $table_name (id, $col1, $col2)
                 VALUES (NULL, '$val1', '$val2')";

Or

 $query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.")
               VALUES (NULL, '".$val1."', '".$val2."')";

With single quotes'':

$query = 'INSERT INTO '.$table_name.' (id, '.$col1.', '.$col2.')
             VALUES (NULL, '.$val1.', '.$val2.')';

Use back ticks`` when a column/value name is similar to a MySQL reserved keyword.

Note: If you are denoting a column name with a table name then use back ticks like this:

`table_name`.`column_name` <-- Note: exclude. from back ticks.

356

Answer

Solution:

Single quotes should be used for string values like in the VALUES() list.

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

167

Answer

Solution:

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&A quite often.

In a nutshell; MySQL thinks you want to do math on its own table/column and interprets hyphens such as "e-mail" ase minusmail.


Disclaimer: So I thought I would add this as an "FYI" type of answer for those who are completely new to working with databases and who may not understand the technical terms described already.

542

Answer

Solution:

SQL servers and MySQL, PostgreySQL, Oracle don't understand double quotes("). Thus your query should be free from double quotes(") and should only use single quotes(').

Back-trip(`) is optional to use in SQL and is used for table name, db name and column names.

If you are trying to write query in your back-end to call MySQL then you can use double quote(") or single quotes(') to assign query to a variable like:

let query = "select id, name from accounts";
//Or
let query = 'select id, name from accounts';

If ther's awhere statement in your query and/or trying toinsert a value and/or anupdate of value which is string use single quote(') for these values like:

let querySelect = "select id, name from accounts where name = 'John'";
let queryUpdate = "update accounts set name = 'John' where id = 8";
let queryInsert = "insert into accounts(name) values('John')";

//Please not that double quotes are only to be used in assigning string to our variable not in the query
//All these below will generate error

let querySelect = 'select id, name from accounts where name = "John"';
let queryUpdate = 'update accounts set name = "John" where id = 8';
let queryInsert = 'insert into accounts(name) values("John")';

//As MySQL or any SQL doesn't understand double quotes("), these all will generate error.

If you want to stay out of this confusion when to use double quotes(") and single quotes('), would recommend to stick with single quotes(') this will include backslash() like:

let query = 'select is, name from accounts where name = \'John\'';

Problem with double(") or single(') quotes arise when we had to assign some value dynamic and perform some string concatenation like:

let query = "select id, name from accounts where name = " + fName + " " + lName;
//This will generate error as it must be like name = 'John Smith' for SQL
//However our statement made it like name = John Smith

//In order to resolve such errors use
let query = "select id, name from accounts where name = '" + fName + " " + lName + "'";

//Or using backslash(\)
let query = 'select id, name from accounts where name = \'' + fName + ' ' + lName + '\'';

If need further clearance do follow quotes in JavaScript

344

Answer

Solution:

It is sometimes useful to not use quotes... because this can highlight issues in the code generating the query... For example:

Where x and y are should always be integers...

SELECT * FROMtable WHERE x= AND y=0

Is a SQL syntax error... a little lazy but can be useful...

People are also looking for solutions to the problem: javascript - How do I change the colour of the first LI value that is added through a XMLHttpRequest?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.