Why use ` around table names? php/mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Why use ` around table names? php/mysql

Post by mikebr »

I can't seem to find out why some php scripts use an ` around mysql table names, also phpMyAdmin uses them with db dumps. I don't see them used in any tutorials either, can anyone enlighten me on this?
also maybe explain the difference to using a ` and not a ' 'single quote'?

I can't even find a name for them, ticks?

Thanks
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

These are used to escape the db/table/column names in MySQL. usually known as backticks. In Access/SQL Server the same is acheived by surrounding them with box brackets - like this [db/table/column name].
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Why use ` around table names? php/mysql

Post by Weirdan »

mikebr wrote: I can't even find a name for them, ticks?
Usually they are reffered to as `backticks`
mikebr wrote:I can't seem to find out why some php scripts use an ` around mysql table names, also phpMyAdmin uses them with db dumps. I don't see them used in any tutorials either, can anyone enlighten me on this?
MySQL Manual wrote: An identifier [table or field name, B.W.] may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it. For a list of reserved words, see Section 9.6, “Treatment of Reserved Words in MySQL”. Special characters are those outside the set of alphanumeric characters from the current character set, ‘_’, and ‘$’.

The identifier quote character is the backtick (‘`’):
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

An example of there use could be if you are using a reserved word as a table/column name.

create table `select`
(
`int` int;
)

And when inserting, selecting etc you would need to make sure you always enclose the names in backticks.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

And when inserting, selecting etc you would need to make sure you always enclose the names in backticks.
Can you explain why I would need to do this?

I have read the manual link as 'Weirdan' posted above, and it states:
The identifier quote character is the backtick (‘`’):
An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it.
and
The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible.
I am still a bit confused as to 'And when inserting, selecting etc you would need to make sure you always enclose the names in backticks.'. I also read somewhere that a scripter had to use back ticks or his server would not run the query, can this be right?

Thanks again
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

And when inserting, selecting etc you would need to make sure you always enclose the names in backticks.
He meant that this is true when you are using a keyword as a fieldname.

It was me that said my host required me to use backticks...well, that isn't entirely true.

A query such as...

Code: Select all

SELECT * FROM users WHERE id = '1'
...works with no problem.


but a more complex query such as this requires the backticks, otherwise it returns a syntax error

Code: Select all

SELECT `q`.`id` as `mainID`, `q`.`qgid`, `q`.`section`, `q`.`fieldType`, `a`.`qid`, `a`.`uid`, `a`.`answer` as `mainAnswer`

FROM `gradlife_co_uk_-_gradlife`.`questions` as `q`, `gradlife_co_uk_-_gradlife`.`answers` as `a`

INNER JOIN `gradlife_co_uk_-_gradlife`.`answers` ON `q`.`id` = `a`.`qid`

WHERE `q`.`section` = '2' AND `a`.`uid` = '".$userID."'

GROUP BY `q`.`id`"
Whereas, on my localhost, that query runs no problem without the backticks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

you really need the backticks (as someone suggested above) for reserved words and for operators.

ex:

if my table name was "create". I would HAVE to have the ticks around it. Likewise if I had some fields on my table named "my-table" those would have to be "ticked" as well (note the minus operator).
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Thanks for the info on this from everyone, just one more question and I think that will tie it all up:

Does it need to be back ticks? would single quotes be ok, in the manual it says:
The identifier quote character is the backtick (‘`’):
but does this mean for the purposes of their examples or the back tick must be used as the quote? the reason I ask is because I have seen single quotes being used also for this purpose.

Thanks again
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

mikebr wrote:Thanks for the info on this from everyone, just one more question and I think that will tie it all up:

Does it need to be back ticks? would single quotes be ok, in the manual it says:
The identifier quote character is the backtick (‘`’):
but does this mean for the purposes of their examples or the back tick must be used as the quote? the reason I ask is because I have seen single quotes being used also for this purpose.

Thanks again
It has to be backticks in MySQL at least... I've seen square brackets [table_name] used before (some other DB no doubt) but I can't remember where :?
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

mikebr wrote:but does this mean for the purposes of their examples or the back tick must be used as the quote? the reason I ask is because I have seen single quotes being used also for this purpose.
single quotes - ' ' - for quoting string data
pair o' backticks - ` ` - for quoting db/tbl/col names...
understood :wink:
d11wtq wrote:I've seen square brackets [table_name] used before (some other DB no doubt) but I can't remember where :?
in Access/SQL Server :wink: I said it before
I wrote:In Access/SQL Server the same is acheived by surrounding them with box brackets - like this [db/table/column name].
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

It's better practice to use backticks on all column/table/db headings as it prevents the DB serverthinking "Ah, this could be a function and not a name, let me check the list" for every heading. You'll only really notice the difference on a server that has 1,000+ concurrent users, but it all helps.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I prefer to avoid db/table names that are also (dbms specific) keywords.. That way i don't have to bother "escaping" my names..
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

I've never used backticks myself. But it's nice to learn what they are for....

Thanks all.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

timvw wrote:I prefer to avoid db/table names that are also (dbms specific) keywords.. That way i don't have to bother "escaping" my names..
Doesn't stop the RDBMS checking if they are a resevered word though :P
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Thanks again everyone for the input.

Cheers
Post Reply