Please help; MySQL can't create table

db_newbie · #1 (**permalink**) 12-25-10, 03:12

Hello there people, I'm sorry if this is a noob question.

I've recently installed Xampp 1.7.3a (on Ubuntu 10.04 64bit) and configured it. Everything was fine, I even went into MySQL root to set the privileges for other user.

After that, I re-logged in with the other user and created a database which was fine BUT when I tried to create table within it, I can't seem to do it so.
Following is my query statement and for the life of me, I can't figure out what's wrong with it

Quote:

CREATE TABLE 'hits' ('id' int, 'song' varchar(255), 'artist' varchar(255), 'year' (int), 'genre' varchar(255));

The error I get is

Quote:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''hits' ('id' int, 'song' varchar(255), 'artist' varchar(255), 'year' (int), 'gen' at line 1

It's really doing my head in

and I don't know how to sort it out.
I mean in Oracle, this query statement would've worked!

So, please help me out.
I would greatly appreciate it.

Thank you very much.

Regards,
db_newbie

r937 · #2 (**permalink**) 12-25-10, 06:32

Quote:

Originally Posted by db_newbie

I mean in Oracle, this query statement would've worked!

absolutely not, no, it wouldn't've

this is invalid --

Code:

CREATE TABLE 'hits' ...

this is valid (but not in oracle) --

Code:

CREATE TABLE `hits` ...

this is valid (in all databases) --

Code:

CREATE TABLE hits ...

you can't use a string as a table or column name

and the error message tells you ~exactly~ where the problem is, doesn't it

db_newbie · #3 (**permalink**) 12-25-10, 06:37

Quote:

Originally Posted by r937

absolutely not, no, it wouldn't've

you can't use a string as a table or column name

and the error message tells you ~exactly~ where the problem is, doesn't it

Oh, I see. I keep thinking I have to use single quotes in MySQL but in actual fact, they're not single quotes! It's a symbol beside number 1, right?

Well, thank you very much, sir. I've just tried it and it worked

Cheers and regards.

r937 · #4 (**permalink**) 12-25-10, 06:49

they're called backticks, and no, you don't have to use them, and you shouldn't

best practise is not to name your tables or columns with a name that requires escaping, like if the name contains a special character or blank, or the name is a reserved word

db_newbie · #5 (**permalink**) 12-27-10, 09:40

Quote:

Originally Posted by r937

they're called backticks, and no, you don't have to use them, and you shouldn't

best practise is not to name your tables or columns with a name that requires escaping, like if the name contains a special character or blank, or the name is a reserved word

Yes, that totally makes sense.

Next time, I'll work out what the error msg is saying before letting out my frustration

Again, thank you.

Internet Services

Web Development

Digital Marketing

Consumer Tech