How do I achieve the outcome of call check_shipments;

Question

Hello I want to achieve the outcome of call check_shipments;

Table Definitions:

How do I achieve the said storedprocedure with cursors using this code: Please tell me what should I change within the code

DELIMITER $$

CREATE PROCEDURE check_shipments()
BEGIN
DECLARE no_more_products int DEFAULT 0;
DECLARE p_id int DEFAULT 0;
DECLARE ship_times int DEFAULT 0;
DECLARE cur_shipments CURSOR FOR
    SELECT prod_id FROM shipments;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_products = 1;
CREATE table IF NOT EXISTS products_Info(prod_id int, prod_shipments int);
OPEN cur_shipments;
FETCH cur_shipments INTO p_id;
REPEAT
    SELECT count(prod_id) INTO ship_times FROM shipments where prod_id = p_id;
    IF ship_times > 0 THEN
        INSERT INTO products_Info VALUES(p_id, ship_times);
    END IF;
    FETCH cur_shipments INTO p_id;
UNTIL no_more_products = 1 END REPEAT;
CLOSE cur_shipments;
SELECT * FROM products_Info;
drop table products_Info;
END $$

DELIMITER ;

But the outcome of my storedprocedure is like this:

Help is muchly appreaciated since I am still new to learning database stuff.

Answer 1

We want to count the number of lines with each id so we use

 INSERT INTO products_Info VALUES(p_id, 1)
>                ON DUPLICATE KEY UPDATE 
>                prod_shipments = prod_shipments + 1;

DELIMITER $$ 

CREATE PROCEDURE check_shipments()
BEGIN
DECLARE no_more_products int DEFAULT 0;
DECLARE p_id int DEFAULT 0;
DECLARE ship_times int DEFAULT 0;
DECLARE cur_shipments CURSOR FOR
    SELECT prod_id FROM shipments;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_products = 1;
CREATE table IF NOT EXISTS products_Info(
       prod_id int primary key, 
       prod_shipments int);
OPEN cur_shipments;
FETCH cur_shipments INTO p_id;
REPEAT
    SELECT count(prod_id) INTO ship_times FROM shipments where prod_id = p_id;
    IF ship_times > 0 THEN
        INSERT INTO products_Info VALUES(p_id, 1)
           ON DUPLICATE KEY UPDATE 
           prod_shipments = prod_shipments + 1;
    END IF;
    FETCH cur_shipments INTO p_id;
UNTIL no_more_products = 1 END REPEAT;
CLOSE cur_shipments;
SELECT * FROM products_Info;
drop table products_Info;
END $$

DELIMITER ;

call check_shipments

prod_id | prod_shipments
------: | -------------:
      1 |              3
      2 |              2

db<>fiddle here