Mysql: What Is Difference Between Type Of Quotes In Sql?

[Terry]

I am manually typing from the book, I am at the beginning of Section Two and am just beginning the Paypal project. I am typing manually because I learn better doing it than just reading it.

 

I did not enter the quotes as ` instead I used ' then I switched all the quotes to ` as I was getting errors and then I still got errors until I compared what was in the book and the downloaded project SQL to what I manually typed. How does MySQL treat ` versus ' ?

 

From experiencing this I gather the ' ' is in quoting something specific you want to enter into the database either as a value OR assigned to the format of the value, table field.

BUT, what does the side quotes mean to MySQL? why the difference between them?

 

 

Thanks.

[Jonathon]

As I understand it backticks ` are used to quote names such as the name of a db field and ' single quotes are for entering strings.

[Stuart]

The backtick is used to enclose query identifiers - a query identifier is basically either a table name, column name or function name. In general you can write your queries without enclosing your identifiers in backticks and they will function just fine, however it is considered best practice.

 

The reason this is best practice is that MySQL has a list of reserved words that cannot be used as identifiers unless quoted with backticks. In most cases you'd be unlikely to name your table or column after one of these words (e.g. NO_WRITE_TO_BINLOG) - however some are more common. The first time I encountered this problem was for a table called REFERENCES which I later found out to be a reserved word.

 

Something else I often now do is introduce a namespace into my database models e.g. prefixing all database tables with the value gs_ eliminates the issue of having table names with reserved words. The full list of reserved words can be found here: MySQL reserved words.

 

Finally regards single quotes these are used to denote a string value - for example you don't need to quote a value that should be an integer. But make sure it is an integer before using it in your query by typecasting the value:

 

$integer = (int)$_GET['integer'];

 

Single quotes may also be used in the SELECT part of your query to return a literal string - this is quite useful when using a UNION statement on mutliple queries to identify which query the results belong too.

[Terry]

Hey Thanks Guys,

 

I have been coding PHP and MySQL for a long time, you would think I would actually know or remember this. Looking at the code, in context it does make sense and the explanation both of you gave hit it home for me. What I like about this book is that it is actually teaching me better programming by real world usage and context, where many many other books just give a bunch of examples like reading php.net examples.

 

Going through this book I am trying to learn things that I missed or are missing in my knowledge bank. I didn't really understand the defines in the config file BEFORE, I have seen them many places like wordpress but I never really understood understood exactly what was going on. I saw it, I used the code before but mostly copy paste type of thing. Going through the first part of the book, right now, I get it now. Small things like this really helps, ya know?

 

A community like this that you can actually ask, maybe stupid questions and get great responses back is really appreciated.

 

Thanks.

[Larry]

Thanks to Stuart and Jonathon for helping, as always. Terry, I'm glad you like the book so far. Even though it requires basic knowledge of PHP and MySQL, it was my hope that even seasoned developers would learn a few things from it.