Create the MySQL table and autofill a column using monthname function

Question

Not sure how to use monthname function while creating a table in MySQL. All I trying to do is when is entered in a Column A then the Column B should fill the month name in words based on the input in column A.

I am using a simple html form to post the values to MySQL. User fills the HTML data's like Name, Orderno, order date, Quantity and item name.

These values are posted to MySQL table orderdata. MySQL table has columns like id, name, orderno, order_date, order_month, quantity and item_name.

Want the order_month column to auto fill month name based on the order_date.

Below are the syntax I tried

create table orderdata (id int, order_date date, order_month DEFAULT ON UPDATE MONTHNAME(order_date),quantity int,item_name varchar(50));

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 'DEFAULT ON UPDATE MONTHNAME(order_date),quantity int,item_name varchar(50))' at line 1

create table orderdata (id int, order_date date, order_month UPDATE test.orderdata SET month= MONTHNAME(order_date),quantity int,item_name varchar(50));

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 'UPDATE test.orderdata SET month= MONTHNAME(order_date),quantity int,item_name va' at line 1

Any help to make this much appreciated

Thank you

Answer 1

You are trying to add a trigger's jobs in the table definition.

Basically the creation for your table should be:

CREATE TABLE orderdata 
   (id INT, order_date DATE, order_month VARCHAR(15), quantity INT, item_name VARCHAR(50));

According to MySQL Trigger documentation, you will have the following Trigger to fulfill your purpose:

DELIMITER $$

USE `<DATABASE-NAME>`$$

CREATE DEFINER = CURRENT_USER TRIGGER `<DATABASE-NAME>`.`orderdata_BEFORE_INSERT` 
       BEFORE INSERT ON `orderdata` FOR EACH ROW
BEGIN
    SET NEW.order_month = MONTHNAME(NEW.order_date);
END $$

DELIMITER ;

Then when you perform any given INSERT it will automatically add the month for you.

Example:

INSERT INTO `orderdata`(`id`, `order_date`, `order_month`, `quantity`, `item_name`) 
    VALUES (3,NOW(),NULL,23,'First order');

The result is:

Answer 2

if you have MySQL version 5.7.6 and up you can use generated columns and do it without triggers.

create table

    CREATE TABLE orderdata (
        id INT   
      , order_date DATE
      , order_month VARCHAR(50) AS (MONTHNAME(order_date)) #generated column
      , quantity INT
      , item_name VARCHAR(50)
   );

Insert query.

INSERT INTO orderdata (id, order_date, quantity, item_name)  VALUES(1, '2016-12-25', 1, 'item 1');

Result

SELECT * FROM orderdata;


 id  order_date  order_month  quantity  item_name  
------  ----------  -----------  --------  -----------
     1  2016-12-25  December            1  item 1