Insert rows on one table based on the values of a field of another table in PHP-MySQL

Question

I would like to ask for a solution on how to insert n rows based on the values of a field in another table.

Consider the following tables:

CREATE TABLE Input (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(128),
    Ticket_Piece INT
);

CREATE TABLE Output (
    Ticket_ID INT AUTO_INCREMENT PRIMARY KEY,
    Transaction_ID INT,
    Ticket_Number VARCHAR(23) UNIQUE,
    FOREIGN KEY (Transaction_ID)
        REFERENCES Input (ID)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

If a row from the Input table has n in the Ticket_Number column, then n rows should be inserted into the Output table, with Ticket_Number having values "ID-1" through "ID-n" (e.g. (4, "D", 5) in Input should result in rows with ticket numbers "4-1" through "4-5" being added to Output). How can rows for Output be generated in a range of numbers based on the Ticket_Piece column using PHP and MySQL?

For example, with the input:

INSERT INTO Input (ID, Name, Ticket_Piece)
  VALUES
(1, 'A', 2),
(2, 'B', 1),
(3, 'C', 3)
;

the result should be:

Ticket_ID	Transaction_ID	Ticket_Number
1	1	1-1
2	1	1-2
3	2	2-1
4	3	3-1
5	3	3-2
6	3	3-3

Answer 1

For each row you fetch from the input table, use a for loop to insert multiple rows into the output table.

$res = $pdo->query("SELECT id, ticket_piece FROM Input_Table");
$insert_stmt = $pdo->prepare("INSERT INTO Output_Table (transaction_id, ticket_number) VALUES (:id, :ticket)");
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
    $pieces = $row['ticket_piece'];
    $id = $row['transaction_id'];
    for ($i = 1; $i <= $pieces; $i++) {
        $insert_stmt->execute([':id' => $id, ':ticket' => "$id-$i"]);
    }
}

Answer 2

A solution in PHP or SQL will likely need to use a loop.

If this comes from the data model rather than business rules (and depending on other factors), a trigger might be a fairly simple option. The trigger body could have a WHILE or other loop to iterate over the ticket piece numbers, and CONCAT to combine the ID and piece number into a ticket number, inserting each in turn.

DELIMITER ;;
CREATE TRIGGER create_ticket_pieces
  AFTER INSERT ON Input
  FOR EACH ROW
BEGIN
  DECLARE piece INT DEFAULT 1;
  WHILE piece <= NEW.Ticket_Piece DO
    INSERT INTO Output (Transaction_ID, Ticket_Number) Values (NEW.ID, Concat(NEW.ID, '-', piece));
    SET piece := piece + 1;
  END WHILE;
END;;
DELIMITER ;

An alternative some use is to pregenerate a table of numbers, then join with this table to generate rows:

INSERT INTO Output (Transaction_ID, Ticket_Number)
  SELECT Input.ID, Concat(Input.ID, '-', Numbers.Number)
    FROM Input
      JOIN Numbers ON Numbers.Number <= Input.Ticket_Piece
    WHERE ... -- select Input rows

It should be noted that by duplicating information (the transaction ID) in two different columns, the Output table isn't normalized. In particular, it violates 3rd normal form, due to a functional dependency of Transaction_ID on Ticket_Number. The way to resolve this is to leave the transaction ID out of the ticket number field (i.e. Output.Ticket_Number holds only the generated integer ≤ Input.Ticket_Piece). (See: "Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY)")