0

Using symfony 4.4, i create a form to update data table in mysql 5.7. all other data table is working fine, but having an error in one table.

I tried to insert a new data but having an error like this.

An exception occurred while executing 'INSERT INTO dtb_repair (order_no, product_id, product_code, resize, original_size, option, description, discriminator_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' with params ["9", "4", "329", 0.5, "0.5", 3, "eee", "repair"]:

SQLSTATE[42000]: Syntax error or access violation: 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 'option, description, discriminator_type) VALUES (9, 4, '329', '0.5', '0.5', 3, '' at line 1

To make sure it's not a form issue, i tried to put it manually in mysql command line, but still have the same error. So i guess my command is wrong, but i really have no idea which part i have to fix.

 Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| order_no           | int(11)          | NO   |     | NULL    |                |
| product_id         | int(11)          | NO   |     | NULL    |                |
| product_code       | varchar(50)      | YES  |     | NULL    |                |
| resize             | decimal(10,1)    | NO   |     | NULL    |                |
| original_size      | decimal(10,1)    | NO   |     | NULL    |                |
| option             | int(11)          | NO   |     | NULL    |                |
| description        | varchar(255)     | NO   |     | NULL    |                |
| discriminator_type | varchar(255)     | NO   |     | NULL    |

this is the data table that i created.

INSERT INTO dtb_repair (order_no, product_id, product_code, resize, original_size, option, description, discriminator_type) VALUES (9, 4, '324', '0.5', '0.5', 1, 'test', 'repair');

the command seems ok for me, but i have no idea where i got an error.

maz32
  • 127
  • 1
  • 13
  • I will not be providing you solution but Read this article you will be able to debug your issue: https://www.w3schools.com/mysql/mysql_insert.asp – Ajay Nov 04 '22 at 06:17
  • _option_ is a reserved keyword. Try to change the name of the attribute from _option_ to something else and verify, if that solves the problem - little hint: It will – Markus Safar Nov 04 '22 at 06:24
  • @MarkusSafar Thank you so much!!! i changed 'option' to something else then it works! guess it was a reserved keyword issue! – maz32 Nov 04 '22 at 06:28
  • Duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) – Markus Safar Nov 04 '22 at 06:29

1 Answers1

0

Actually the decimal values should not go into '0.5' it should be 0.5 without quotes, MYSQL will treat them as VARCHAR Type, Try to put decimal values directly without quotes

Mihir Sawant
  • 1
  • 2
  • thanks for the reply. I just tried decimal values without quotes but still having the same error.. – maz32 Nov 04 '22 at 06:18
  • Ok, so can you please tell me what error you are getting ? – Mihir Sawant Nov 04 '22 at 06:23
  • yup, it says [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 'option, description, discriminator_type) VALUES (9, 4, '324', '0.5', '0.5', 1, '' at line 1] quite strange, that all other data table is working but this one – maz32 Nov 04 '22 at 06:24
  • Oh, then what error it gives you when you try them without quotes, the decimal ones.. – Mihir Sawant Nov 04 '22 at 06:27
  • thank mate! i solved it! so it was reserved keyword issue. i changed 'option' to something else then it works :D – maz32 Nov 04 '22 at 06:28
  • Oh, I too missed it Yes we cannot use Reserved Keywords as our attributes Nice, but did it accept values with quotes, the decimal values ? – Mihir Sawant Nov 04 '22 at 06:41
  • yep it did accept! :D – maz32 Nov 07 '22 at 02:04