PHP表单提交多组数据如何插入多条数据库记录

我不知道如何向数据库插入多条关联记录。我搭建的表单包含两组结构完全相同的赛事输入行，后续计划使用JavaScript实现动态新增输入行的功能，现阶段先通过重复编写HTML代码的方式完成功能验证。

现有表单代码 #

<form class="formAddCoupon" action="/?dashboard=coupon" method="post">
    <div class="windowEvents">
        <div class="row">
            <input type="text" class="teamInput" name="team1">
            <input type="text" class="teamInput" name="team2">
            <select name="score" id="selectOptions">
                <option value="Liczba bramek">Liczba bramek</option>
                <option value="Liczba rzutów rożnych">
                        Liczba rzutów rożnych</option>
                <option value="Ilość kartek">Liczba kartek</option>
                <option value="Wygrana drużyny">Wygrana drużyny</option>
                <option value="Wygrana drużyny lub połowy">
                        Wygrana drużyny lub połowy</option>
                <option value="Wygrana połowy">Wygrana jednej z połów</option>
                <option value="Hendicap">Hendicap</option>
                <option value="Ilość punktów">Liczba punktów</option>
            </select>
            <input type="text" class="scoreInput" name="type_score">
        </div>
        <div class="row">
            <input type="text" class="teamInput" name="team1">
            <input type="text" class="teamInput" name="team2">
            <select name="score" id="selectOptions">
                <option value="Liczba bramek">Liczba bramek</option>
                <option value="Liczba rzutów rożnych">
                        Liczba rzutów rożnych</option>
                <option value="Ilość kartek">Liczba kartek</option>
                <option value="Wygrana drużyny">Wygrana drużyny</option>
                <option value="Wygrana drużyny lub połowy">
                        Wygrana drużyny lub połowy</option>
                <option value="Wygrana połowy">Wygrana jednej z połów</option>
                <option value="Hendicap">Hendicap</option>
                <option value="Ilość punktów">Liczba punktów</option>
            </select>
            <input type="text" class="scoreInput" name="type_score">
        </div>
    </div>
    <div class="valueAmountRow">
        <input
            type="number"
            name="bid_amount"
            id="bid_amount"
            placeholder="Stawka"
            min="0"
            step="any"
        >
        <input
            type="number"
            name="winner_amount"
            id="winner_amount"
            placeholder="Wygrana"
            min="0"
            step="any"
        >
        <input type="submit" value="Dodaj">
    </div>
</form>

现有Controller.php处理逻辑 #

$data = $this->getRequestPost();
if(!empty($data)) {
    $couponData = [
        'bid_amount' => $data['bid_amount'],
        'winner_amount' => $data['winner_amount'],
        'team1' => $data['team1'],
        'team2' => $data['team2'],
        'score' => $data['score'],
        'type_score' => $data['type_score'],
    ];
    $this->db->createCoupon($couponData);
}

现有Database.php写入逻辑 #

public function createCoupon(array $data)
{
    try {
        $this->createNewCoupon($data);
    } catch(Throwable $e) {
        dump($e);
        throw new StorageException('Nie udało się wysłać kuponu', 400, $e);
    }
}

private function createNewCoupon($data): void
{
        $nr_coupon =  trim($this->connection->quote($this->checkIssetCode()));
        $created = $this->connection->quote(date('Y-m-d H:i:s'));
        $bid_amount = trim($this->connection->quote($data['bid_amount']));
        $winner_amount = trim($this->connection->quote($data['winner_amount']));
        $team1 = trim($this->connection->quote($data['team1']));
        $team2 = trim($this->connection->quote($data['team2']));
        $score = trim($this->connection->quote($data['score']));
        $typeScore = trim($this->connection->quote($data['type_score']));

        $insertNumberCoupon = "INSERT INTO
            coupon_number(
                nr_coupon,
                created,
                bid_amount,
                winner_amount
            )
            VALUES(
                $nr_coupon,
                $created,
                $bid_amount,
                $winner_amount
            )";

        $sql_create_number_coupon = $this->connection->prepare($insertNumberCoupon);
        if($sql_create_number_coupon)
        {
            $sql_create_number_coupon->execute();
            $id = (int) $this->connection->lastInsertId();
            $addEvent = "INSERT INTO
                events_at_coupon(
                    number_id_coupon,
                    team1,
                    team2,
                    score,
                    type_score
                )
                VALUES($id,$team1,$team2,$score,$typeScore)";

            $sql_add_event_to_table = $this->connection->prepare($addEvent);
            if($sql_add_event_to_table)
            {
                $sql_add_event_to_table->execute();
            }
        }
}

当前问题 #

提交表单后数据库仅会写入1条赛事记录，已确认问题源于同名字段提交时值覆盖、后端仅执行单条插入逻辑，需要修改实现多条关联赛事记录的正确写入。

分三个部分调整，从前端到后端全链路适配多赛事提交场景，同时兼容后续JS动态新增行的需求。

1. 调整前端表单字段命名 #

把所有赛事相关的同名字段改成数组格式，提交时PHP会自动把同组字段解析为数组，不会出现值覆盖问题。注意原代码中两个select复用同一个id不符合HTML规范，改为class属性即可，后续动态新增行也不会出现id重复问题。
修改后的单条赛事行结构示例：

<div class="row">
    <input type="text" class="teamInput" name="events[][team1]">
    <input type="text" class="teamInput" name="events[][team2]">
    <select name="events[][score]" class="selectOptions">
        <!-- 选项内容保持不变 -->
    </select>
    <input type="text" class="scoreInput" name="events[][type_score]">
</div>

后续用JS动态新增行时，直接复制该段结构插入即可，不需要额外调整字段名，提交时会自动按顺序组成赛事数组

2. 调整控制器接收逻辑 #

不再单独提取单个赛事字段，直接把提交上来的赛事数组和优惠券公共字段一起传给数据库层，可增加基础校验避免空数据提交：

$data = $this->getRequestPost();
if(!empty($data)) {
    $couponData = [
        'bid_amount' => $data['bid_amount'],
        'winner_amount' => $data['winner_amount'],
        'events' => $data['events'] ?? []
    ];
    if(empty($couponData['events'])) {
        // 此处添加业务校验失败的提示逻辑即可
    }
    $this->db->createCoupon($couponData);
}

3. 调整数据库层写入逻辑 #

优惠券主表coupon_number仅需插入1次，拿到自增ID之后，循环遍历所有赛事数据，逐条插入到events_at_coupon关联赛事表即可。同步改用参数绑定的预处理语句，避免SQL注入风险，且SQL仅需预处理一次，循环执行的性能更好。
修改后的createNewCoupon方法：

private function createNewCoupon($data): void
{
    // 插入优惠券主记录
    $nr_coupon = $this->checkIssetCode();
    $created = date('Y-m-d H:i:s');
    $insertCouponSql = "INSERT INTO coupon_number(nr_coupon, created, bid_amount, winner_amount)
        VALUES(:nr_coupon, :created, :bid_amount, :winner_amount)";
    $couponStmt = $this->connection->prepare($insertCouponSql);
    $couponStmt->execute([
        ':nr_coupon' => $nr_coupon,
        ':created' => $created,
        ':bid_amount' => $data['bid_amount'],
        ':winner_amount' => $data['winner_amount']
    ]);
    $couponId = (int)$this->connection->lastInsertId();

    // 预处理赛事插入SQL，复用语句对象循环执行
    $insertEventSql = "INSERT INTO events_at_coupon(number_id_coupon, team1, team2, score, type_score)
        VALUES(:coupon_id, :team1, :team2, :score, :type_score)";
    $eventStmt = $this->connection->prepare($insertEventSql);
    
    // 循环写入所有赛事
    foreach($data['events'] as $event) {
        // 跳过空的无效行
        if(empty($event['team1']) || empty($event['team2'])) continue;
        $eventStmt->execute([
            ':coupon_id' => $couponId,
            ':team1' => trim($event['team1']),
            ':team2' => trim($event['team2']),
            ':score' => $event['score'],
            ':type_score' => trim($event['type_score'])
        ]);
    }
}

内容的提问来源于stack exchange，提问作者Throw