PHP-autogenerated MySQL Create Table statement returns error

Question

mysql> CREATE TABLE metadata 
( 
id int, 
created_by int, 
modified_by int, 
deleted_by int, 
created_at datetime, 
modified_at datetime, 
deleted_at datetime, 
key varchar(45), 
definition_type varchar(1), 
metadata_type varchar(1), 
no_of_decimals int, 
is_required tinyint, 
min_range decimal, 
max_range decimal, 
entity varchar(10), 
sequence int, 
meta_group varchar(2), 
PRIMARY KEY (id) );

ERROR 1064 (42000): 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 'varchar(45), definition_type varchar(1), metadata_type varchar(1), no_of_decimal' at line 1

I'm not entirely sure how to correct this error, as these kinds of things don't typically have specific answers online. How can I fix this error?

Looks like you are missing the datatypes for the `created_at`, `modified_at` and `deleted_at` fields. — vikramls, Apr 06 '15 at 21:43

score 1 · Accepted Answer · answered Apr 06 '15 at 21:42

1

key is a reserved word, you should escape it with backticks ` in MySQL:

`key` varchar(45)

answered Apr 06 '15 at 21:42

potashin

44,205
11
83
107

Do you know if PHP has a function to check for reserved words? – Skyfactor_interns Apr 06 '15 at 21:43
@Skyfactor_interns: I don't think that there is a built-in solution in php, but you can escape all identifiers, no matter, reserved they are or not. – potashin Apr 06 '15 at 21:45

PHP-autogenerated MySQL Create Table statement returns error

1 Answers1