Why does my query break when I have spaces in my column names?

Question

I keep getting this error:

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 'Name int(10) NOT NULL, Middle Name varchar(10) NOT NULL, Last Name varch' at line 2

And I am unsure what mistake I made ...

<?php 
mysql_connect('klaic.db.11427125.hostedresource.com', 'klaic', '*******')
or die (mysql_error());
mysql_select_db('klaic')
or die (mysql_error());
mysql_query("create table account(
   Fist Name int(10) NOT NULL, 
   Middle Name varchar(10) NOT NULL, 
   Last Name varchar(10) NOT NULL,
   Email varchar(55) NOT NULL,
   Comfirm Email varchar(55) NOT NULL,
   D.O.B varchar(10) NOT NULL,
   ID int(11) NULL,  
   PRIMARY KEY (ID)
)") or die (mysql_error());
echo "Complete.";
?>

Fled names shouldn't have spaces - they will work only if you add backticks `` — Pekka, Jul 15 '13 at 15:02
Don't get me wrong, but where did you read that column names can contain spaces or dots? If it's a tutorial, can you please share the link? — N.B., Jul 15 '13 at 15:04
Column names should use underscores to separate words also it is probably not a good idea to use punctuation in the column name. — Joe W, Jul 15 '13 at 15:06

score 4 · Accepted Answer · answered Jul 15 '13 at 15:03

4

If you're going to have spaces in your column names, which is not a good idea, you need to wrap them in ticks:

mysql_query("create table account(
   `Fist Name` int(10) NOT NULL, 
   `Middle Name` varchar(10) NOT NULL, 
   `Last Name` varchar(10) NOT NULL,
   `Email` varchar(55) NOT NULL,
   `Comfirm Email` varchar(55) NOT NULL,
   `D.O.B` varchar(10) NOT NULL,
   `ID` int(11) NULL,  
   PRIMARY KEY (ID)
)") or die (mysql_error());

answered Jul 15 '13 at 15:03

John Conde

217,595
99
455
496

I think a better idea is to steer clear of spaces altogether. Personally, backticks drive me nuts. – Strawberry Jul 15 '13 at 15:08
I agree with @Strawberry. In fact, backticks aren't even present in every keyboard layout (case in point: Italian layout hasn't them) and to get a backtick I need to type `Alt+0096` for each one of them. In this query I'd need 56 keystrokes to delimit strings in place of 3 underscores. – STT LCU Jul 15 '13 at 15:12
I don't think anyone would argue that spaces in column names are a bad thing. I even said so in the first line of my answer. It's up to the user to decide if they think this is a good solution for them or if they should change their column naming conventions. – John Conde Jul 15 '13 at 15:16
@STTLCU Really? How horrendous!!! – Strawberry Jul 15 '13 at 15:16
@Strawberry Yeah really. Even the slash `/` must be reached through `Shift+7` key combination. Italian layout is dreadful to code with. – STT LCU Jul 15 '13 at 15:19

score 2 · Answer 2 · answered Jul 15 '13 at 15:10

As documented under Schema Object Names:

Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers.
[ deletia ]
Identifiers are converted to Unicode internally. They may contain these characters:

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

Extended: U+0080 .. U+FFFF

Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

ASCII: U+0001 .. U+007F

Extended: U+0080 .. U+FFFF
[ deletia ]
The identifier quote character is the backtick (“`”):

Therefore, to include spaces/dots/etc in a column name (which is generally a bad idea), one must quote the identifier in backticks:

CREATE TABLE account (
   `Fist Name` int(10) NOT NULL, 
   `Middle Name` varchar(10) NOT NULL, 
   `Last Name` varchar(10) NOT NULL,
   `Email` varchar(55) NOT NULL,
   `Comfirm Email` varchar(55) NOT NULL,
   `D.O.B` varchar(10) NOT NULL,
   `ID` int(11) NULL,  
   PRIMARY KEY (ID)
)

Wiktor Mociun · Answer 3 · 2013-07-15T15:14:54.870

1

Use accents ` in columns names in SQL statement if you use spaces. Correct SQL statement is:

SELECT * FROM `account` WHERE create table account(
   `First Name` int(10) NOT NULL, 
   `Middle Name` varchar(10) NOT NULL, 
   `Last Name` varchar(10) NOT NULL,
   `Email` varchar(55) NOT NULL,
   `Comfirm Email` varchar(55) NOT NULL,
   `D.O.B` varchar(10) NOT NULL,
   `ID` int(11) NULL,  
   PRIMARY KEY (ID)
)

Tips:

Don't use spaces and big letters in column names!
ID should be "auto-incremented"
Should First Name (you lost one letter in your statement ;) ) be INT?

edited Jul 15 '13 at 15:14

answered Jul 15 '13 at 15:09

Wiktor Mociun

702
6
12

1

2. not necessarily: It may be related to another table somehow. (Anyway I'd still think it should be autoincremental in this example) – STT LCU Jul 15 '13 at 15:16
I think the same, so I pointed it out there. – Wiktor Mociun Jul 15 '13 at 15:21

score 0 · Answer 4 · answered Jul 15 '13 at 15:05

Dear just keep in mind when you are giving column name donot give space in it. For example if you want to make table field name "First Name" it would be either "First_name" or "FirstName" so your could would be like that

<?php 
mysql_connect('klaic.db.11427125.hostedresource.com', 'klaic', '*******')
or die (mysql_error());
mysql_select_db('klaic')
or die (mysql_error());
mysql_query("create table account(
   Fist_Name int(10) NOT NULL, 
   Middle_Name varchar(10) NOT NULL, 
   Last_Name varchar(10) NOT NULL,
   Email varchar(55) NOT NULL,
   Comfirm_Email varchar(55) NOT NULL,
   D.O.B varchar(10) NOT NULL,
   ID int(11) NULL,  
   PRIMARY KEY (ID)
)") or die (mysql_error());
echo "Complete.";
?>

Why does my query break when I have spaces in my column names?

4 Answers4