Mysql – Inserting data into another table and getting it back into an array field in thesql

database-designMySQL

I have two tables

Products table

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

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[i].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.

EDITED: I am trying stored procedure as @H.79 suggested.
But the @uid is always null and count is always 0.

CREATE DEFINER=`xyz` PROCEDURE `create_procedure`(IN name varchar(255), description varchar(255), sub_category_id varchar(255), categories varchar(255), sub_categories varchar(255), product_images varchar(255), product_prices varchar(255), created varchar(255))
BEGIN

SELECT @count := count(id) as count, @uid := id as prodid, @name := name as product_name , @product_prices := product_prices as v_product_prices
from products
where name = @name;

IF @count = 1 THEN
    update products SET product_prices = @product_prices where id = @uid;
    insert into price(price, product_id) select product_prices, id from products where id = @uid;
END IF;

IF @count = 0 THEN
    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, product_prices, created);
    insert into price(price, product_id) select product_prices, id from products where id = @uid;
END IF;

END

Thanks in advance.

Best Answer

First things first.... No inline T-SQL. You open yourself up to SQL injection. :)

First, let's get the process of inserting or updating the data. Then once the data is in the table, or once the data is updated, then you can focus on the second part of your want / need.

Create a stored procedure in your MySQL database. Here is an example that should help you get there.

First we get a count of records that have the the name of the product you are looking for. If it exists set the @count variable and the @uid to the ID of that product (remember these are made variables, and can be named whatever you want. I just pulled this real quick from a stored proc that I have).

Do an IF statement, and if the @count = 1 then perform the update statement. If the @count = 0 then perform the insert statement.

CREATE PROCEDURE `sp_ProcessProductData`(IN v_product_name varchar(75), v_data varchar(100))
BEGIN

select @count := count(id) as count, @uid := id as prodid,
from tbl_products
where product_name = v_productname

IF @count = 1 THEN

    update tbl_products
    set
    columnname = v_formdata
    where id = @uid;

END IF;

IF @count = 0 THEN

    insert into tbl_products(column, column, column)
    values (v_data, v_data, v_data);

END IF;

END

Now to call the stored proc use this code:

call sp_ProcessProductData ('${name}', '${yourdata}')
Related Question