Re: Inserting data into another table and getting it back into an array field in mysql
First of all, Thank you for your effort. I actually know mysql doesn't have array but but varchar column with csv will be great.
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,
https://www.pricerunner.dk/.
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.
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
SELECT @count := count(id) as count, @uid := id as prodid from products where name = name;
insert into price(price, product_id) select product_prices, id from products; IF @count = 1 THEN
update products set product_prices = concat(product_prices, ", product_prices") where id = @uid;
END IF;
IF @count = 0 THEN
insert into products(name, description, categories, sub_categories, product_images, product_prices, created) values (name, description, categories, sub_categories, product_images, product_prices, created); END IF; END