MYSQL query, loop all rows, based on specific field value run loop with another query

Question

I have 2 tables as the follows:

Purchases:

Purchase_id	Stations
1	4
2	6

question from:https://stackoverflow.com/questions/65872335/mysql-query-loop-all-rows-based-on-specific-field-value-run-loop-with-another

Answer 1

I think you should write a stored procedure for that like this:

CREATE PROCEDURE FILLSP()
        BEGIN
        DECLARE counter INT DEFAULT 0;
        DECLARE count INT;
        DECLARE repeat INT;
        DECLARE pid INT;
        DECLARE i INT;
        SELECT COUNT(*) FROM Purchases INTO count;
        
        WHILE counter < count  DO 
          SELECT Stations FROM Purchases LIMIT counter,1 INTO repeat;
          SELECT Purchase_id FROM Purchases LIMIT counter,1 INTO pid;
          SET i = 0;
          WHILE i < repeat DO
          INSERT INTO allItems VALUES (pid, 17);
          SET i = i + 1;
          END WHILE;
          SET counter = counter + 1;
        END WHILE;
    END