0

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.

  • 2
    Since name is a unique key - INSERT ON DUPLICATE KEY UPDATE. SQL has a fussy syntax. Looking at manuals is better than guessing syntax. Also look up about SQL injection and how to prevent it in nodejs. – danblack Dec 3 '19 at 23:37
0

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}')
|improve this answer|||||
  • From what I understand, this will update the table if product name exists and insert if not. Right? I achieved this using ON DUPLICATE KEY UPDATE 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}") ON DUPLICATE KEY UPDATE product_prices = concat(product_prices, ", ${product_prices}") – Yogesh Bhattarai Dec 5 '19 at 12:07
  • This concatinates product prices into products table. But main difficulty I am having is to insert into another table price and get that price data and insert into product_prices in products table as an array. – Yogesh Bhattarai Dec 5 '19 at 12:12
  • Explain why you want the price column to be an array? I've built many custom admin panels for the web dealing with all kinds of crazy requirements from my customers. Never once did I have a requirement where a product would have more than one price. I mean price would change depending on options selected. I have your tables in my test MySQL database, but I don't understand your reasoning. Please elaborate. – H.79 Dec 5 '19 at 17:16
  • First of all, Thank you for your ellaboration. What I want is when you create a product if product name exists, all field remain the same but inserted price gets add up to price table with FK product name or ID, if not create new product. Main problem I am having is when creating a product, i want to insert the price in price table not products table and add the price in product_prices in products table along with all existing prices. The reason I am doing this is to make an application to compare prices. To have an idea you can take a refence from, pricerunner.dk. – Yogesh Bhattarai Dec 5 '19 at 22:40
  • A product should have a price range. And the reason to create a price table is also to have a FK store_id. For now I am testing with stored procedure. I am able to add the price in price table but it is adding the price to all the products available. I will post my current stored procedure in next comment. CREATE DEFINER='xyz' PROCEDURE create_procedure(IN name varchar(255), description varchar(255), categories varchar(255), sub_categories varchar(255), product_images varchar(255), product_prices varchar(255), created varchar(255)) BEGIN – Yogesh Bhattarai Dec 5 '19 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.