dividing quatity INT value into unique separate rows for each item I am working on a project where we are selling tickets. How do I create multiple rows from a quantity value. Currently it looks like this

line_item_id | order_id | product_id | quantity
------------------------------------------------
      1      |     1    |      6     |    5
------------------------------------------------


I wish for my end result to look like this into a separate table

ticket_id    | customer_id | product_id | quantity
------------------------------------------------
      1      |     2       |      6     |    1
------------------------------------------------
      2      |     2       |      6     |    1
------------------------------------------------
      3      |     2       |      6     |    1
------------------------------------------------
      4      |     2       |      6     |    1
------------------------------------------------
      5      |     2       |      6     |    1
------------------------------------------------


I am using a stored procedure but every time I call it it does what I want but it also duplicates the previous data into the table as well as adding the new tickets.

Also here is the stored procedure. Maybe I'm doing something wrong

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_item_import`()
BEGIN
    # Declare variables to read records from the cursor
    DECLARE parent_id_val INT(10) UNSIGNED;
    DECLARE item_type_val VARCHAR(255);
    DECLARE quantity_val INT(11);

    # Declare variables for cursor and loop control
    DECLARE no_more_rows BOOLEAN;
    DECLARE item_qty INT DEFAULT 0;

    # Declare the cursor
    DECLARE item_cur CURSOR FOR
        SELECT oi.MerchID, m.MerchName, oi.quantity FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            JOIN merchandise m ON m.MerchID = oi.MerchID
                WHERE o.customer_id = 2 AND
                MerchCatID = '7' OR MerchCatID = '8';

    # Declare handlers for exceptions
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

    # Open the cursor and loop through results
    OPEN item_cur;

    input_loop: LOOP

        FETCH item_cur
        INTO parent_id_val, item_type_val, quantity_val;

        # Break out of the loop if there were no records or all have been processed
        IF no_more_rows THEN
            CLOSE item_cur;
            LEAVE input_loop;
        END IF;

        SET item_qty = 0;

        qty_loop: LOOP

            INSERT INTO items_new
                (itemID, itemDesc)
            SELECT
                parent_id_val, item_type_val;

            SET item_qty = item_qty + 1;

            IF item_qty &gt;= quantity_val THEN
                LEAVE qty_loop;
            END IF;

        END LOOP qty_loop;

    END LOOP input_loop;
END$$
DELIMITER ;
To prevent all existing order_items rows for being added to items_new, you have to exclude rows that have already been processed from the cursor.

There are several ways to do this; the best option depends on things we don't know, at this point.


You could pass order_id into the procedure, so you're only processing the order_items from a specific order each time through.
If there's a date attached to orders or order_items, you could store the procedure's last run date, and only process orders with a date between the last run date and the current run date.
If you have some control over the database structure, you could add a bit field to order_items to indicate whether it had been processed or not, and only process the unprocessed rows.
Similarly, if the items_new records are retained as long as order_items, you could add order_id and line_item_id to items_new, and only insert records if there are no records with those two values already in the table.


NOTE: Working off dates could create a race condition, where a new order has been assigned its date, but hasn't actually been saved when the procedure runs - in that case, it might possibly be missed, and never have the ticket numbers generated.

UPDATE: - If you modify your procedure to accept 1 argument, @proc_order_id, which would be the order_id of the order you want to create ticket numbers for, then your cursor would be:

# Declare the cursor
DECLARE item_cur CURSOR FOR
    SELECT oi.MerchID, m.MerchName, oi.quantity FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN merchandise m ON m.MerchID = oi.MerchID
            WHERE o.customer_id = 2 AND
            MerchCatID = '7' OR MerchCatID = '8' AND
            o.order_id = @proc_order_id;


On the other hand, if you add order_id and line_item_id to items_new, the cursor would be:

DECLARE item_cur CURSOR FOR
    SELECT oi.MerchID, m.MerchName, o.order_id, oi.line_item_id, oi.quantity FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN merchandise m ON m.MerchID = oi.MerchID
            WHERE o.customer_id = 2 AND
            MerchCatID = '7' OR MerchCatID = '8' AND
            NOT EXISTS (SELECT 1 FROM items_new
                         WHERE order_id = o.order_id
                           AND line_item_id = oi.line_item_id);