Page 1 of 2
Why use ` around table names? php/mysql
Posted: Mon Nov 28, 2005 7:15 am
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
Posted: Mon Nov 28, 2005 7:23 am
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].
Re: Why use ` around table names? php/mysql
Posted: Mon Nov 28, 2005 7:28 am
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 (‘`’):
Posted: Mon Nov 28, 2005 8:04 am
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.
Posted: Mon Nov 28, 2005 8:55 am
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
Posted: Mon Nov 28, 2005 9:00 am
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
Posted: Mon Nov 28, 2005 9:44 am
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).
Posted: Mon Nov 28, 2005 5:09 pm
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
Posted: Mon Nov 28, 2005 6:00 pm
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

Posted: Tue Nov 29, 2005 1:02 am
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
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

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].
Posted: Tue Nov 29, 2005 4:42 am
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.
Posted: Tue Nov 29, 2005 6:52 am
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..
Posted: Tue Nov 29, 2005 7:41 am
by neophyte
I've never used backticks myself. But it's nice to learn what they are for....
Thanks all.
Posted: Tue Nov 29, 2005 8:34 am
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

Posted: Tue Nov 29, 2005 4:57 pm
by mikebr
Thanks again everyone for the input.
Cheers