CODEWITHSUNDEEP
phpmysqlsqltypesphpmyadmin
Syed Raza
Syed Raza

Reputation: 1100

how to set Year Datatype in MySQL

I can't understand what wrong in this sql statement

INSERT INTO tbl_car_model_year_rate
SET car_model_id =4,
FROM =2008,
TO =2011,
with_driver =1,
per_day =1000,
ten_days =10000,
twenty_days =20000,
thirty_days =30000,
image =  '1303166512test.jpg',
created_at = NOW( ) ,
created_by =1

Datatype 

car_model_id    int(11) 
image   text        
from    year(4)
to  year(4)
with_driver tinyint(1)          
per_day int(11) 
ten_days    int(11) 
twenty_days int(11)
thirty_days int(11)
created_at datetime 
created_by int(11)

Error Messsage

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 'from  =2008, to   =2011, with_driver =1, per_day  =1000, ten_days =10000,     twenty' at line 1

Upvotes: 0

Views: 1094

Answers (4)

TH Afridi
TH Afridi

Reputation: 432

TO and FROM are reserved words you should put it in a single qoute like 'FROM', 'TO'.

and remember avoid using reserved words for columns while generating your tables see the below link for complete reference for the reserved words for mysql

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Upvotes: 1

Johan
Johan

Reputation: 76641

if you change your code to:

INSERT INTO tbl_car_model_year_rate SET car_model_id =4,
`FROM` =2008,
`TO` =2011,
with_driver =1,
per_day =1000,
ten_days =10000,
twenty_days =20000,
thirty_days =30000,
image =  '1303166512test.jpg',
created_at = NOW( ) ,
created_by =1

It will work. If you add your field and/or table names in backticks "`" then you can use just about any name for them.

Reason for failure
FROM is a reserved word (as in select *FROMtablename)
You are confusing MySQL by using FROM as a column name (select * FROM FROM WHERE FROM = AND)

My Recommendation
Do not use reserved words for fieldnames.
Change the column name from FROM to YearFrom or StartYear
and from TO to YearTo or EndYear.

Using reserved words as column names is just plain confusing and adding backticks around everything just makes stuff hard to read and ugly and whilst still being confusing.

Upvotes: 4

bruno.zambiazi
bruno.zambiazi

Reputation: 1482

Here's an example correct use of year field in MySQL:

MySQL: adding current year as a default value for a field 'year'

Upvotes: 0

quantme
quantme

Reputation: 3657

You're using a reserved word for a column name.

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.

Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Schema Object Names”:

For a more detailed list of reserved words, including differences between versions, see Reserved Words in MySQL 5.5.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Upvotes: 4

Related Questions