thara

In MySQL, I want to create a trigger with AFTER UPDATE triggering event for my "user" table. In next, I have a table named "user_log" which is use to store the modifications that occurred on the parent table "user" after any update commands. So, data in "user_log" table need be as follows: select * from user_log; +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | log_id | user_id | action | old_data | new_data | changed_date | read | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | 1 | 10 | Changed yyy's name | yyy | xxx | 2022-06-20 14:06:56 | no | | 2 | 10 | Changed xxx's address | No.111, | No.112, | 2022-06-20 19:07:38 | no | | 3 | 10 | Changed xxx's city | Old City Name | New City Name | 2022-06-20 19:07:38 | no | | 4 | 10 | Changed xxx's phone number | 011-5000000 | 011-4000000 | 2022-06-20 19:07:38 | no | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ As you can see from the data in the table above, it will update several columns at once. So I created my triger as follows, and its working for me. DELIMITER $$ DROP TRIGGER IF EXISTS `user_log` ; $$ CREATE TRIGGER `user_log` AFTER UPDATE ON `user` FOR EACH ROW BEGIN IF OLD.name <> NEW.name THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name ELSE '' END ); END IF; IF OLD.address <> NEW.address THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN OLD.address ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN NEW.address ELSE '' END ); END IF; IF OLD.city <> NEW.city THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN OLD.city ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN NEW.city ELSE '' END ); END IF; IF OLD.phone <> NEW.phone THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); END IF; END$$ DELIMITER ; My problem is, I have a lot more columns in the user table. Like I said, all columns or several of them are updated at once. In that case I have to add a large amount of INSERT query to my trigger. So here I would like to know if there is another suitable way to do this. I also tried it in this way. But its working only for one column. DROP TRIGGER IF EXISTS `user_log`; CREATE TRIGGER IF NOT EXISTS `user_log` AFTER UPDATE ON user FOR EACH ROW INSERT INTO user_log (user_id,action,old_data,new_data) VALUES ( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name WHEN (NEW.address <> OLD.address) THEN OLD.address WHEN (NEW.city <> OLD.city) THEN OLD.city WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name WHEN (NEW.address <> OLD.address) THEN NEW.address WHEN (NEW.city <> OLD.city) THEN NEW.city WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); Thank you.

Hello Sir, I have found another case. Check below results Starting Stock select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ | 1 | a | 10.00 | 3 | 1 | 5 | 0 | 2022-06-08 16:17:05 | NULL | | 2 | a | 10.00 | 2 | 1 | 5 | 0 | 2022-06-08 16:17:05 | NULL | | 3 | a | 10.00 | 1 | 1 | 5 | 0 | 2022-06-08 16:17:05 | NULL | | 7 | a | 5.00 | 1 | 1 | 1 | 72 | 2022-06-08 16:18:03 | NULL | | 8 | a | 5.00 | 2 | 1 | 1 | 72 | 2022-06-08 16:18:03 | NULL | | 9 | a | 5.00 | 3 | 1 | 1 | 72 | 2022-06-08 16:18:03 | NULL | | 13 | a | 4.00 | 1 | 1 | 1 | 74 | 2022-06-08 16:18:32 | NULL | | 14 | a | 4.00 | 2 | 1 | 1 | 74 | 2022-06-08 16:18:32 | NULL | | 15 | a | 4.00 | 3 | 1 | 1 | 74 | 2022-06-08 16:18:32 | NULL | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ First run sales select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 1 | 1 | 1.00 | 99 | | 2 | 2 | 2.00 | 99 | | 3 | 3 | 3.00 | 99 | +----------+---------+----------+-----------+ select * from stock; +----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 7.00 | 3 | 1 | 5 | 0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:04 | | 2 | a | 8.00 | 2 | 1 | 5 | 0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:04 | | 3 | a | 9.00 | 1 | 1 | 5 | 0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:04 | | 7 | a | 5.00 | 1 | 1 | 1 | 72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:04 | | 8 | a | 3.00 | 2 | 1 | 1 | 72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:04 | | 9 | a | 2.00 | 3 | 1 | 1 | 72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:04 | | 13 | a | 3.00 | 1 | 1 | 1 | 74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:04 | | 14 | a | 2.00 | 2 | 1 | 1 | 74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:04 | | 15 | a | 1.00 | 3 | 1 | 1 | 74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:04 | +----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Second run sales select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 1 | 1 | 1.00 | 99 | | 2 | 2 | 2.00 | 99 | | 3 | 3 | 3.00 | 99 | | 4 | 1 | 1.00 | 100 | | 5 | 2 | 2.00 | 100 | | 6 | 3 | 3.00 | 100 | +----------+---------+----------+-----------+ select * from stock; +----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 4.00 | 3 | 1 | 5 | 0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:52 | | 2 | a | 6.00 | 2 | 1 | 5 | 0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:52 | | 3 | a | 8.00 | 1 | 1 | 5 | 0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:52 | | 7 | a | 5.00 | 1 | 1 | 1 | 72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:52 | | 8 | a | 3.00 | 2 | 1 | 1 | 72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:52 | | 9 | a | 2.00 | 3 | 1 | 1 | 72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:52 | | 13 | a | 3.00 | 1 | 1 | 1 | 74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:52 | | 14 | a | 2.00 | 2 | 1 | 1 | 74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:52 | | 15 | a | 1.00 | 3 | 1 | 1 | 74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:52 | +----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Sir, you can see how qty updates when it first runs. It updates all records belonging to item_id. But in second runs update works correctly. What would be the issue?

Thank you very much! You perfectly resolved my issue, I am once again very grateful to you. Meanwhile, before I got your query, I also made some effort for this. Of course I thought this could not be done without a store procedure. But you did it. Sir, you are a mysql geneious 😄 This is my attempt. But I coudn't get it to work. CREATE PROCEDURE `UpdateStock` ( IN @item_id INT, IN @sales_qty INT ) BEGIN -- Fetch total stock in hand DECLARE stock_tot INT SET stock_tot = (SELECT SUM(qty) FROM stock WHERE item_id = @item_id) -- Check if the available stock is less than sales quantity IF stock_tot < @sales_qty BEGIN SELECT 'Stock not available' AS message END DECLARE @stock_id INT DECLARE @quantiti_in_hand INT -- Declare a CURSOR DECLARE @get_stock_id CURSOR SET @get_stock_id = CURSOR FOR SELECT stock_id, qty FROM stock WHERE item_id = @item_id AND qty_type = 'a' ORDER BY stock_id -- Open the CURSOR OPEN @get_stock_id -- Fetch record from the CURSOR FETCH NEXT FROM @get_stock_id INTO @stock_id, @quantiti_in_hand -- Loop if record found in CURSOR WHILE @@FETCH_STATUS = 0 BEGIN -- Check if sales quantity becomes 0 IF @sales_qty = 0 BEGIN PRINT 'Updated Successfully' RETURN 1 END -- If sales Qty is less than or equal to Quantity In Hand IF @sales_qty <= @quantiti_in_hand BEGIN UPDATE stock SET qty = qty - @sales_qty WHERE stock_id = @stock_id SET @sales_qty = 0 END -- If sales Qty is greater than Quantity In Hand ELSE BEGIN UPDATE stock SET qty = 0 WHERE stock_id = @stock_id SET @sales_qty = @sales_qty - @quantiti_in_hand END FETCH NEXT FROM @get_stock_id INTO @stock_id, @quantiti_in_hand END -- Close and Deallocate CURSOR CLOSE @get_stock_id DEALLOCATE @get_stock_id END

Thank you very much for your time and effort. I really appreciate. Now I safe to delete 0 qty records from stock table. For that, do I need to use separate delete query, right? Sir, may I know is there a way to do it in single query, I mean like a store procedure.

Yes sir, its my mistake. now it's working nicely. One thing, the updated_date updates all records related to item_id.

Sir, you mean this? INSERT INTO stock (stock_id, qty, updated_date) SELECT stock_id , newqty , NOW() as updatedDate FROM ( SELECT stock_id , @sales := IF(@prev = k.item_id, @sales, sold) as sales , IF(qty <= @sales, 0, qty - @sales) as newqty , @sales := IF(@sales >= qty, @sales - qty, 0) , @prev := k.item_id as item FROM stock k JOIN ( SELECT item_id , sum(quantity) as sold FROM sales sl JOIN basket b ON b.basket_id = sl.basket_id AND b.sale_time > (SELECT COALESCE(MAX(updated_date),0) FROM stock) GROUP BY item_id ) s ON k.item_id = s.item_id AND qty_type = 'a' JOIN (SELECT @prev:=null, @sales:=0) init ORDER BY k.item_id, stock_id ) calc ON DUPLICATE KEY UPDATE qty = VALUES(qty), updated_date = VALUES(updated_date);

Sir, This is few result set Starting Stock select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ | 1 | a | 50.00 | 2 | 1 | 5 | 0 | 2022-06-06 20:12:39 | NULL | | 2 | a | 50.00 | 1 | 1 | 5 | 0 | 2022-06-06 20:12:39 | NULL | | 5 | a | 50.00 | 1 | 1 | 1 | 48 | 2022-06-06 20:15:18 | NULL | | 6 | a | 50.00 | 2 | 1 | 1 | 48 | 2022-06-06 20:15:18 | NULL | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ Result 01 - After 1st sale select s.*, sale_time from sales s join basket b using(basket_id); +----------+---------+----------+-----------+---------------------+ | sales_id | item_id | quantity | basket_id | sale_time | +----------+---------+----------+-----------+---------------------+ | 1 | 1 | 5.00 | 36 | 2022-06-06 23:29:28 | | 2 | 2 | 4.00 | 36 | 2022-06-06 23:29:28 | +----------+---------+----------+-----------+---------------------+ select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 42.00 | 2 | 1 | 5 | 0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 | | 2 | a | 40.00 | 1 | 1 | 5 | 0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 | | 5 | a | 50.00 | 1 | 1 | 1 | 48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 | | 6 | a | 50.00 | 2 | 1 | 1 | 48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Result 02 - After 2nd sale select s.*, sale_time from sales s join basket b using(basket_id); +----------+---------+----------+-----------+---------------------+ | sales_id | item_id | quantity | basket_id | sale_time | +----------+---------+----------+-----------+---------------------+ | 1 | 1 | 5.00 | 36 | 2022-06-06 23:29:28 | | 2 | 2 | 4.00 | 36 | 2022-06-06 23:29:28 | | 3 | 1 | 1.00 | 37 | 2022-06-06 23:35:05 | +----------+---------+----------+-----------+---------------------+ select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 42.00 | 2 | 1 | 5 | 0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 | | 2 | a | 38.00 | 1 | 1 | 5 | 0 | 2022-06-06 20:12:39 | 2022-06-06 23:35:19 | | 5 | a | 50.00 | 1 | 1 | 1 | 48 | 2022-06-06 20:15:18 | 2022-06-06 23:35:19 | | 6 | a | 50.00 | 2 | 1 | 1 | 48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Result 03 - After 3rd sale select s.*, sale_time from sales s join basket b using(basket_id); +----------+---------+----------+-----------+---------------------+ | sales_id | item_id | quantity | basket_id | sale_time | +----------+---------+----------+-----------+---------------------+ | 1 | 1 | 5.00 | 36 | 2022-06-06 23:29:28 | | 2 | 2 | 4.00 | 36 | 2022-06-06 23:29:28 | | 3 | 1 | 1.00 | 37 | 2022-06-06 23:35:05 | | 4 | 1 | 1.00 | 38 | 2022-06-06 23:48:06 | +----------+---------+----------+-----------+---------------------+ select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 42.00 | 2 | 1 | 5 | 0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 | | 2 | a | 36.00 | 1 | 1 | 5 | 0 | 2022-06-06 20:12:39 | 2022-06-06 23:48:49 | | 5 | a | 50.00 | 1 | 1 | 1 | 48 | 2022-06-06 20:15:18 | 2022-06-06 23:48:49 | | 6 | a | 50.00 | 2 | 1 | 1 | 48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Sir, this is how my query now. But still I can not get the correct updation. INSERT INTO stock (stock_id, qty, updated_date) SELECT stock_id , newqty , NOW() as updatedDate FROM ( SELECT stock_id , @sales := IF(@prev = k.item_id, @sales, sold) as sales , IF(qty <= @sales, 0, qty - @sales) as newqty , @sales := IF(@sales >= qty, @sales - qty, 0) , @prev := k.item_id as item FROM stock k JOIN ( SELECT item_id , sum(quantity) as sold FROM sales sl JOIN stock s USING (item_id) JOIN basket b ON b.basket_id = sl.basket_id AND b.sale_time > (SELECT COALESCE(MAX(updated_date),0) FROM stock) GROUP BY item_id ) s ON k.item_id = s.item_id AND qty_type = 'a' JOIN (SELECT @prev:=null, @sales:=0) init ORDER BY k.item_id, stock_id ) calc ON DUPLICATE KEY UPDATE qty = VALUES(qty), updated_date = VALUES(updated_date);

Sir, I modified the query as below, now query is executing but not updating.. INSERT INTO stock (stock_id, qty, updated_date) SELECT stock_id , newqty , NOW() as updatedDate FROM ( SELECT stock_id , @sales := IF(@prev = k.item_id, @sales, sold) as sales , IF(qty <= @sales, 0, qty - @sales) as newqty , @sales := IF(@sales >= qty, @sales - qty, 0) , @prev := k.item_id as item FROM stock k JOIN ( SELECT item_id , sum(quantity) as sold FROM sales sl JOIN stock s USING (item_id) JOIN basket b ON b.basket_id = sl.basket_id AND b.sale_time > coalesce(s.updated_date, NOW()) GROUP BY item_id ) s ON k.item_id = s.item_id AND qty_type = 'a' JOIN (SELECT @prev:=null, @sales:=0) init ORDER BY k.item_id, stock_id ) calc ON DUPLICATE KEY UPDATE qty = VALUES(qty), updated_date = VALUES(updated_date); Also I changed "updated_date" column in stock table. now its not update when a purchase is added. select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+ | 1 | a | 50.00 | 2 | 1 | 5 | 0 | 2022-06-06 20:12:39 | NULL | | 2 | a | 50.00 | 1 | 1 | 5 | 0 | 2022-06-06 20:12:39 | NULL | | 5 | a | 50.00 | 1 | 1 | 1 | 48 | 2022-06-06 20:15:18 | NULL | | 6 | a | 50.00 | 2 | 1 | 1 | 48 | 2022-06-06 20:15:18 | NULL | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+

Hello Sir, My sales table is an one to many relationship with basket table. So there is a datetime column in the basket table to keep the date information for each sale. Eg: select basket_id, sale_time from basket; +-----------+---------------------+ | basket_id | sale_time | +-----------+---------------------+ | 26 | 2022-06-06 17:28:05 | | 27 | 2022-06-06 17:29:39 | | 28 | 2022-06-06 17:32:03 | +-----------+---------------------+ So, Can we use this column for the update query?

Sir, as you say, the above query was run only once. Once meant, sales data was inserted into the sales table every time a sale took place. Whenever that happens I want to reduce the stock by running the above query once. The following is the result of running the above query one at a time each time data is added to the sales table. Initial Stock select * from stock; +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 100.00 | 2 | 1 | 5 | 0 | 2022-06-06 11:22:58 | 2022-06-06 11:22:58 | | 2 | a | 100.00 | 1 | 1 | 5 | 0 | 2022-06-06 11:22:58 | 2022-06-06 11:22:58 | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Result 01 - After 1st sale select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 1 | 1 | 2.00 | 26 | | 2 | 2 | 3.00 | 26 | +----------+---------+----------+-----------+ select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 97.00 | 2 | 1 | 5 | 0 | 2022-06-06 17:27:10 | 2022-06-06 17:28:57 | | 2 | a | 98.00 | 1 | 1 | 5 | 0 | 2022-06-06 17:27:10 | 2022-06-06 17:28:57 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Result 02 - After 2nd sale select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 1 | 1 | 2.00 | 26 | | 2 | 2 | 3.00 | 26 | | 3 | 1 | 10.00 | 27 | -- from 2nd sale +----------+---------+----------+-----------+ select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 94.00 | 2 | 1 | 5 | 0 | 2022-06-06 17:27:10 | 2022-06-06 17:30:01 | | 2 | a | 86.00 | 1 | 1 | 5 | 0 | 2022-06-06 17:27:10 | 2022-06-06 17:30:01 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ Result 03 - After 3rd sale select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 1 | 1 | 2.00 | 26 | | 2 | 2 | 3.00 | 26 | | 3 | 1 | 10.00 | 27 | | 4 | 1 | 5.00 | 28 | -- 3rd sale | 5 | 2 | 4.00 | 28 | -- 3rd sale +----------+---------+----------+-----------+ select * from stock; +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | updated_date | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+ | 1 | a | 87.00 | 2 | 1 | 5 | 0 | 2022-06-06 17:27:10 | 2022-06-06 17:32:47 | | 2 | a | 69.00 | 1 | 1 | 5 | 0 | 2022-06-06 17:27:10 | 2022-06-06 17:32:47 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Thank you Sir, Do I need to assign values to @prev and @sales variables when it is executing? or can I execute the query without doing anything to your query? I tried both methods. In both cases the first execution is done correctly, but the qty decrease is not correct every time the next execution is done.

Thanks for your reply. As you suggested a UNION query, I currently use a query as follows when I need to obtain current stock and sales quantity. SELECT i.item_id , i.item_name , i.sku , round(coalesce(current_actual_stock,0), 0) as actual_stock , round(coalesce(current_virtual_stock,0), 0) as virtual_stock , round(coalesce(sum(sl.quantity),0), 0) as item_sold FROM item i LEFT JOIN ( SELECT item_id, qty_type, sum(qty) AS current_actual_stock FROM stock WHERE qty_type = 'a' GROUP BY item_id ) s1 USING (item_id) LEFT JOIN ( SELECT item_id, qty_type, sum(qty) AS current_virtual_stock FROM stock WHERE qty_type = 'v' GROUP BY item_id ) s2 USING (item_id) LEFT JOIN sales sl USING (item_id) GROUP BY item_id ORDER BY item_id In the previous occation, my stock table was structured as one row per item. Then the stock update can be done without any problems. But with the advent of the stock type, the table structure had to be changed as above. May I know from you here, what is the most suitable method for this? Yours comments and suggestions are greatly appreciated. Thank you.

Sir, there are mainly 3 type of stocks (Purchase, Donation and Opening ). That is why "stock_type_id" there in stock table. "purchase_id" also there to identify qty by purchase invoices. Here I have to make some reports based on purchase invoices and stock types. That is the reason for looking for a solution of the above kind. qty_type is a flag for actual and virtual quantity of the table. 'a'=actucal (received qty) 'v'=virtual (pindng and ordered)

I have two mysql tables. The two tables with the sample data are as follows. select * from stock; +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ | 48 | v | 44.00 | 1 | 1 | 1 | 38 | 2022-05-16 14:27:19 | | 49 | v | 8.00 | 263 | 1 | 1 | 38 | 2022-05-16 14:27:19 | | 50 | a | 6.00 | 1 | 1 | 1 | 39 | 2022-05-16 14:30:04 | | 51 | a | 4.00 | 263 | 1 | 1 | 39 | 2022-05-16 14:30:04 | | 56 | a | 28.00 | 1 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 57 | a | 57.00 | 263 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 58 | a | 6.00 | 264 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 59 | a | 19.00 | 301 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 64 | a | 15.00 | 263 | 1 | 5 | 0 | 2022-05-18 17:23:37 | | 65 | a | 546.00 | 264 | 1 | 5 | 0 | 2022-05-18 17:23:37 | | 66 | a | 15.00 | 263 | 1 | 5 | 0 | 2022-05-18 17:24:21 | | 67 | a | 546.00 | 264 | 1 | 5 | 0 | 2022-05-18 17:24:21 | | 72 | v | 20.00 | 720 | 1 | 1 | 44 | 2022-05-24 09:24:43 | | 73 | v | 2.00 | 729 | 1 | 1 | 44 | 2022-05-24 09:24:43 | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 7 | 1 | 20.00 | 4 | | 8 | 263 | 3.00 | 4 | | 9 | 1 | 2.00 | 5 | | 10 | 263 | 4.00 | 5 | | 11 | 264 | 6.00 | 5 | | 12 | 301 | 1.00 | 5 | +----------+---------+----------+-----------+ By this I want to build up a update query. This should deduct the quantity of the items in the sales table from the stock table. If such a query can be created in mysql it should be updated in ascending order on the stock_id in the stock table. If such an update query can be built, based on the data in the two tables above, I expect the result to be as follows. select stock_id, qty_type, qty, item_id from stock where qty_type = 'a'; +----------+----------+--------+---------+ | stock_id | qty_type | qty | item_id | +----------+----------+--------+---------+ | 50 | a | 0.00 | 1 | -- clear by sales | 51 | a | 0.00 | 263 | -- clear by sales | 56 | a | 12.00 | 1 | -- deduct qty by sales | 57 | a | 54.00 | 263 | -- deduct qty by sales | 58 | a | 0.00 | 264 | -- clear by sales | 59 | a | 18.00 | 301 | -- deduct qty by sales | 64 | a | 15.00 | 263 | | 65 | a | 546.00 | 264 | | 66 | a | 15.00 | 263 | | 67 | a | 546.00 | 264 | +----------+----------+--------+---------+ Any help would be highly appreciated.

Let me explain this little bit more.. Through my question I have pointed out a "Buy and pay a fixed price" type of offer. (Eg: Buy 3 SAVE 25 / 😃 Suppose the selling price of item X is 70.00. The owner decides to make an offer for that X item. Think that offer named as "Buy 3 SAVE 30 / =". That offer means that 3 of the X items will sell for 180 / -. Through the system, the shop owner has the ability to create, delete or edit this kind of offers for a product. He has the ability to change an offer created in this way at any time. Each time that is changed, the data related to the changed offer should be entered as a separate row in the database.This change can even happen to a single day also. In such a case, There is a possibility that the offer made to the customer in the morning may be changed or not received in the evening. Each of these offers has a relevant date range, so when adding a new offer to the table, you should be careful about the date range of the previous offer related to that particular item. For that reason the previous offers' date range should be updated accordingly, then the conditions should be applicable as I mentioned in the question above. For better understanding, here I have attached an image of my offer input form:

Hi All, I have some issues when I trying to insert/update data into mysql through php. Hoping someone may help me out. This is how I store data in my table: select * from offer; +----------+---------------+---------------------+---------------------+-----------------------------+------+ | offer_id | offer_type_id | valid_from | valid_until | terms | name | +----------+---------------+---------------------+---------------------+-----------------------------+------+ | 1 | 1 | 2022-04-29 21:26:38 | 2022-04-30 21:28:09 | {"qty": 4, "total": 260.00} | aaa | | 2 | 1 | 2022-04-30 21:26:38 | 2022-05-25 23:59:59 | {"qty": 2, "total": 150.00} | bbb | | 3 | 1 | 2022-05-10 21:26:38 | 2022-05-20 23:59:59 | {"qty": 3, "total": 200.00} | ccc | +----------+---------------+---------------------+---------------------+-----------------------------+------+ This is how it look my POST array: (not for the data in the table shown above) Array ( [offerName] => Array ( [34] => aaa [35] => bbb [36] => ccc ) [quantity] => Array ( [34] => 3 [35] => 2 [36] => 4 ) [offerPrice] => Array ( [34] => 200 [35] => 150 [36] => 250 ) [validFrom] => Array ( [34] => 2022-04-29 [35] => 2022-04-30 [36] => 2022-05-01 ) [validUntil] => Array ( [34] => 2022-04-30 [35] => 2022-05-05 [36] => 2022-05-20 ) [offer_type] => multisellTotal ) Insert/Update conditions: If the "validFrom" date in POST array overlaps the previous offer in the table, then previous offer record need to be updated as following. valid_until = (validFrom - 1 second) and "validFrom" date of new offer = (validFrom H:i:s) (H:i:s is the time when the script is executed.) If the "validFrom" date in POST array equal to "curdate()", then previous offer record need to be updated as following. valid_until = (now() - 1 second) and "validFrom" date of new offer = now(); If the "validFrom" date in POST array is not overlaps the previous offer and new offer have future dates, then previous offer record updation should be ignore, and new offer records need to be inserted with relavant "validFrom" and "validUntil" dates. when inserting "validFrom" time should be "00:00:00" and "validUntil" time shold be "23:59:59" This is how I tried it. But it doesn't work as I needed. $d1 = new DateTime(); $d2 = clone $d1; $d2->modify("-1 second"); $today = $d1->format('Y-m-d H:i:s'); $yesterday = $d2->format('Y-m-d H:i:s'); $offerType = isset($_POST['offer_type']) ? $_POST['offer_type'] : ''; $prvOferUpdt = $newOferUpdt = []; if ($offerType != '' && $offerType == 'multisellTotal') { foreach ($_POST['validUntil'] as $key => $endDate) { if ($endDate < $_POST['validFrom'][$key]) { $messages = array('success'=>false, 'message'=>'Start date should be less than to End date.'); echo json_encode($messages); return; } $start_date[$key] = $_POST['validFrom'][$key]; $end_date[$key] = $_POST['validUntil'][$key]; } $sql = "SELECT offer_id FROM offer o JOIN offer_item i USING(offer_id) WHERE i.item_id = ? AND ? BETWEEN valid_from AND valid_until AND o.offer_id = i.offer_id"; $stmt = $pdo->prepare($sql); $i = 1; foreach ($start_date as $id => $sdate) { $stmt->execute([$id, $sdate]); $offerID = $stmt->fetch(); // If offer already available and new offer's start date is still // in between previous offer's date_from and date_valid: // then, first closed the old offer and set its date_valid for NOW()-1 second // and set to new offer's date from to NOW(): // Create array to store offer_id and valid_until date which // is need to be updated in previous offer: $oferStart1 = DateTime::createFromFormat( "Y-m-d", $sdate); $oferStart2 = DateTime::createFromFormat( "Y-m-d", $sdate); $oferStart2->modify("-1 second"); if (isset($offerID['offer_id'])) { $diff = $d1->diff($oferStart1); $diffDays = (integer)$diff->format("%R%a"); if ($diffDays == 0) { $prvOferUpdt[$offerID['offer_id']] = $yesterday; $newOferUpdt[$id] = $today; } else if ($diffDays > 0) { $prvOferUpdt[$offerID['offer_id']] = $oferStart2->format("Y-m-d H:i:s"); $newOferUpdt[$offerID['offer_id']] = $oferStart1->format("Y-m-d H:i:s"); } } else { // IF offer start date is a future Date: // $prvOferUpdt[$i] = $oferStart1->format("Y-m-d H:i:s"); $newOferUpdt[$i] = $oferStart1->format("Y-m-d H:i:s"); } $i++; } // Insert new offers into the DB: $query = "INSERT INTO offer ( offer_type_id , valid_from , valid_until , terms , name ) VALUES (1,?,?,?,?)"; $stmt = $pdo->prepare($query); $offerIDs = []; ksort($_POST['offerPrice']); foreach ($_POST['offerPrice'] as $id => $offerPrice) { //$offerPrice = floatval($offerPrice); if(!empty($offerPrice) && $_POST['validFrom'][$id] != '') { $quantity = intval($_POST['quantity'][$id]); $offerPrice = floatval($offerPrice); $offerPrice = number_format($offerPrice,2, '.', ''); $validFrom = date('Y-m-d H:i:s', strtotime($_POST['validFrom'][$id])); $validUntil = $_POST['validUntil'][$id]; $validUntil = ($validUntil != '' AND $validUntil != NULL) ? date('Y-m-d 23:59:59', strtotime($validUntil)) : '9999-12-31 23:59:59'; $offerName = (!empty($_POST['offerName'][$id])) ? filter_var($_POST['offerName'][$id], FILTER_SANITIZE_STRING) : 'Buy and pay a fixed price'; $terms = "{\"qty\": $quantity, \"total\": $offerPrice}"; $stmt->execute([$validFrom,$validUntil,$terms,$offerName]); $lastInsertID = $pdo->lastInsertId(); array_push($offerIDs, $lastInsertID); } else { $messages = array('success'=>false, 'message'=>'Offer Price and Start Date fields can NOT be empty.'); echo json_encode($messages); return; } } $op = array_filter($_POST['offerPrice'],'is_numeric'); $offerItems = array_combine(array_keys($op), array_values($offerIDs)); $sql = "INSERT INTO offer_item (offer_id, item_id) VALUES (?,?)"; $stmt = $pdo->prepare($sql); foreach ($offerItems as $k => $v) { $offerID = $v; $itemID = $k; $stmt->execute([$offerID,$itemID]); } // Then, update previous offers if exist: if (!empty($prvOferUpdt)) { $sql = "UPDATE offer SET valid_until = ? WHERE offer_id = ?"; $stmt = $pdo->prepare($sql); foreach ($prvOferUpdt as $oid => $date) { $stmt->execute([$date,$oid]); } } // If, newOferUpdt array is not empty, // then reset valid_from date for newly added offers: if (!empty($newOferUpdt)) { $newStartDates = array_combine(array_values($offerIDs), array_values($newOferUpdt)); $sql = "UPDATE offer SET valid_from = ? WHERE offer_id = ?"; $stmt = $pdo->prepare($sql); foreach ($newStartDates as $k => $dt) { if (isset($newStartDates[$k])) { $stmt->execute([$dt, $k]); } } } } Also attached sample SQL. Thank you. sql-dump.txt

Thank you, Sir! This is what I was trying to achieve as alternative. I modified your code a bit to output the desired results and all works perfectly now. Yes, the main reason for updating this script was to reduce its execution time. The method you suggested works ten times faster than my version made using arrays and loops. For thousands of records, that process was simply very slow. Again, Thank you very much, Sir!

Sir, Yes in that case, your gess is correct. But in my case I can't use "ON DUPLICATE KEY UPDATE" query for price table. Since every price have a date range. +---------------+-------------+-----------+---------------------+-------------+---------+ | item_price_id | sales_price | our_price | valid_from | valid_until | item_id | +---------------+-------------+-----------+---------------------+-------------+---------+ | 1 | 100.00 | 50.00 | 2022-04-26 15:50:36 | NULL | 42 | | 2 | 120.00 | 50.00 | 2022-04-26 15:50:36 | NULL | 41 | | 3 | 130.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 43 | | 4 | 40.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 40 | | 5 | 55.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 38 | | 6 | 30.00 | 0.00 | 2022-04-26 15:50:36 | NULL | 39 | +---------------+-------------+-----------+---------------------+-------------+---------+ How I create my two array? One create from POST array as follows: // Sales Prices from POST array: $sp = $_POST['salesPrice']; // Remove 0.00 prices from array: foreach ($sp as $k => $v) { if ($sp[$k] == 0) { unset($sp[$k]); } } // Rearrange the POST array to access both prices in one place: foreach ($sp as $key => $value) { $newPrices[$key] = [$value, $_POST['ourPrice'][$key]]; } Other one is creating from SELECT query as follows: // Create new array from item_id: foreach ($sp as $iid => $sPrice) { $itemIDs[] = $iid; } // Select Prices from DB: $in = join(',', array_fill(0, count($itemIDs), '?')); $sql = "SELECT item_id,sales_price,our_price FROM item_price WHERE NOW() BETWEEN valid_from AND COALESCE(valid_until, '9999-12-31 00:00:00') AND item_id IN ($in)"; $stmt = $pdo->prepare($sql); $stmt->execute($itemIDs); $oldPrices = $stmt->fetchAll(PDO::FETCH_UNIQUE); Now I want execute insert / update queries comparing these two arrays..

I have two multidimensional arrays as follows: Array 01 for new prices of items: Array ( [42] => Array ( [0] => 110 [1] => 0.00 ) [41] => Array ( [0] => 80 [1] => 70.00 ) [43] => Array ( [0] => 70 [1] => 60 ) [40] => Array ( [0] => 90 [1] => 80 ) ) 1 Array 02 for old prices of items: Array ( [42] => Array ( [sales_price] => 100.00 [our_price] => 0.00 ) [41] => Array ( [sales_price] => 80.00 [our_price] => 0.00 ) ) 1 Array key of both array are item ids (42,41,43,40). Now I need to compare $new and $old arrays to create update and insert queries. If the values in the new array are different from those in the old one, the table should be updated. If there are more elements in the new array than in the old array, they should be identified for the insert query. So basically I want to divide new array into two parts with comparing old array and considering the terms above. Expecting two arrays should be as follows: Array ( [42] => Array ( [0] => 110 [1] => 0.00 ) [41] => Array ( [0] => 80 [1] => 70.00 ) ) 1 Array ( [43] => Array ( [0] => 70 [1] => 60 ) [40] => Array ( [0] => 90 [1] => 80 ) ) 1 The Code I have so far: foreach ($priceNew as $newIds => $newPrices) { foreach($priceOld as $oldIds => $oldPrices) { } }

Sir, this is my actual size of the paper Total width: 110mm Total Height: 31mm

Sir, My label size is 34x25mm

Sir I have been doing some experiments for a considerable time. Now it is at a satisfactory level. The printer settings also have the ability to adjust to a certain level.

The maximum width of the sticker is used for the barcode.

Yes sir, I have customized your code to suit my needs. Sir one thing, I have tested these codes with few actual barcode scanners. Sometimes, barcode scanners can't recognize a barcode or sometimes its take few seconds to recognize. And aslo, in some cases, I have to manage some distance between label and scaner to read the code. I have tested this for code 128 barcode and code 39 barcode scripts at FPDF. Both scripts work the same way.