Inserting data into another table and getting it back into an array field in mysql

[Yogesh Bhattarai]

I have two tables

Products table

CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`description` varchar(255) NOT NULL,
`created` varchar(255) NOT NULL,
`categories` varchar(255) NOT NULL,
`sub_categories` varchar(255) NOT NULL,
`sub_category_id` varchar(255) NOT NULL,
`product_images` varchar(255) DEFAULT NULL,
`product_prices` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
UNIQUE KEY `name_UNIQUE` (`name`),
KEY `products_ibfk_1` (`sub_category_id`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8

Products table contains column name which is unique. This name is used as a FK in price table as products_id.


Price table

CREATE TABLE `price` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`price` varchar(255) NOT NULL,
`products_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `price_ibfk_1` (`products_id`),
CONSTRAINT `price_ibfk_1` FOREIGN KEY (`products_id`) REFERENCES `products` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8


I have a post request to create products:

router.post('/createproduct', multipleUpload, (req, res) => {
var name = req.body.name;
var description = req.body.description;
var categories = req.body.categories;
var sub_categories = req.body.sub_categories;
var product_prices= req.body.product_prices;
var gallery = req.files;
const product_images = [];
for (let i = 0; i < gallery.length; i++) {
productKey = gallery.key;
product_images.push(productKey)
}
var created = moment().format('YYYY-MM-DD HH:mm:ss');

var query = `INSERT INTO products (name, description, sub_category_id, categories, sub_categories, product_images, product_prices, created) VALUES ("${name}", "${description}", (SELECT id FROM sub_category WHERE sub_category_name = "${sub_categories}"), "${categories}", "${sub_categories}", "${product_images}", (SELECT price FROM price WHERE products_id = "${name}"), "${created}")`;
connection.query(query, (err, rows) => {
if (!err) {
res.send(rows);
} else {
throw err;
}
});
});
What I want to achieve is while creating products IF name exists update products table if not insert into products.

Another important thing: while creating products the value from product_prices should be inserted into price table and get all the price in the price table with the matching FK.

And the product_prices in products table is an array of the price from price table.

I know the query is not right because I am still testing it.

Bare with this beginner, if this sounds vague and complicated.

I would be really grateful if someone could help me out with the query or if there is another better solution.

Note: I am doing this project using mysql, node.js, express, react and redux.

Thanks in advance.