Error creating table: You have an error in your SQL syntax near 'order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ' at line 1
Solution 1
You need to escape reserved words like order
with backticks
CREATE TABLE `order` ( ...
or better use another name instead.
Solution 2
order is keyword used by mysql like (select from tbl_name order by id ASC) so for escaping from using keywords you have to use quotes `` to avoid my sql error
so your query should by
$sql="CREATE TABLE `order` (
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
transaction_id VARCHAR(19) NOT NULL,
payment_status VARCHAR(15) NOT NULL,
payment_amount DECIMAL(6,2) UNSIGNED NOT NULL,
payment_date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (order_id),
FOREIGN KEY (user_id) REFERENCES user (user_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8";
enjoy :D
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on June 12, 2022Comments
-
Admin about 2 years
I am trying to create 2 tables in the same MySQL database with a PHP-script: table 'user' with primary key 'user_id' and table 'order' with primary key 'order_id' and foreign key 'user_id' from the 'user' table (1 to many relationship).
Table user creates successfully without problems:
$sql="CREATE TABLE user( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, type ENUM('member','admin') NOT NULL, username VARCHAR(30) NOT NULL, email VARCHAR(80) NOT NULL, pass VARBINARY(32) NOT NULL, first_name VARCHAR(40) NOT NULL, last_name VARCHAR(40) NOT NULL, date_expires DATE NOT NULL, date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, date_modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (user_id), UNIQUE (username), UNIQUE (email) )ENGINE=InnoDB DEFAULT CHARSET=utf8";
However, I am not able to create table order:
$sql="CREATE TABLE order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, transaction_id VARCHAR(19) NOT NULL, payment_status VARCHAR(15) NOT NULL, payment_amount DECIMAL(6,2) UNSIGNED NOT NULL, payment_date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (order_id), FOREIGN KEY (user_id) REFERENCES user (user_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8";
I get the following error:
Error creating table: 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 'order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ' at line 1
Already checked the syntax and cannot find the mistake. Could you please advise what went wrong? Thanks a lot.
-
Admin over 10 yearsShould I always declare my table 'names' in this way? My textbook didn't care so far...
-
Mark Baker over 10 years@Neo83 - it's a good habit to get into, same with column names
-
juergen d over 10 years@Neo83: That is up to you. Personally I find it confusing having backticks everywhere. Do as you like.
-
Admin over 10 yearsJust changed the table name to orders and now it works. Wouldn't accept 'order'. Thanks for your help.
-
Amal Murali over 10 yearsJust a note: juergen d posted an almost same answer 11 minutes ago. :)
-
juergen d over 10 years@Neo83: You used quotes around
order
. Backticks are different. -
Admin over 10 yearsAh, ok. Now I got it. I am using Robin Nixon's "PHP, MySQL, ..." and the syntax he is teaching is far from ideal... Thanks for pointing me in the right direction.
-
Ahmed Abumostafa over 10 yearsohh sorry i saw the answer without the reference to reserved words.. it's my fault, thank you