我如何将一个表中的 id 连续传递到另一个表中？

Question

我正在生成一些足球赛程，我尝试做的是将赛程和比赛插入两个表中。Fixtures 表很好用，但我在“match”表中插入匹配项很困难，因为这个表有一个“fixture”表的外键。

夹具表

id_fixture unsigned bigint(20)

fixture bigint(20)

匹配表

id_match unsigned bigint(20)

fixture bigint(20) -> foreign key to fixture.id_fixture

homeTeam varchar(191)

awayTeam varchar(191)

php算法

$i = 1;

foreach ($games as $rounds) {

    $free = "";

    echo "<h5>Etapa {$i}</h5>";

    $SQL1 = "INSERT INTO `fixture` (`id_fixture`, `fixture`) VALUES (NULL, '$i');";

    $query1 = $link->query($SQL1);

    foreach ($rounds as $match) {

        if ($match[0] == "stă etapa asta.") {

            $free = "<span style='color:red;'>{$match[1]} {$match[0]}</span><br>";

            $SQL2 = "INSERT INTO `match` (`id_match`, `fixture`, `homeTeam `, `awayTeam `) VALUES (NULL, '$match[1]', '$match[0]');";

            $query2 = $link->query($SQL2);

        } elseif ($match[1] == "stă etapa asta.") {

            $free = "<span style='color:red;'>{$match[0]} {$match[1]}</span><br>";

            $SQL3 = "INSERT INTO `match` (`id_match`, `fixture`, `homeTeam `, `awayTeam `) VALUES (NULL, '$match[0]', '$match[1]');";

            $query3 = $link->query($SQL3);

        } else {

            echo "{$match[0]} vs {$match[1]}<br>";

            $SQL4 = "INSERT INTO `match` (`id_match`, `fixture`, `homeTeam `, `awayTeam `) VALUES (NULL, '$match[0]', '$match[1]');";

            $query4 = $link->query($SQL4);

        } 

    }

    echo $free;

    echo "<br>";

    $i++; 

}

mysqli_close($link);

如何在游戏生成时将 fixture.id_fixture 传递给 match.fixture？

Answer 1

您可以使用$link->insert_id.$i = 1;foreach ($games as $rounds) {&nbsp; &nbsp; $free = "";&nbsp; &nbsp; echo "<h5>Etapa {$i}</h5>";&nbsp; &nbsp; $stmt = $link->prepare('INSERT INTO fixture (fixture) VALUES (?)');&nbsp; &nbsp; $stmt->bind_param('i', $i);&nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; $id_fixture = $link->insert_id; // The auto generated ID&nbsp; &nbsp; foreach ($rounds as $match) {&nbsp; &nbsp; &nbsp; &nbsp; if ($match[0] == "stă etapa asta.") {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $free = "<span style='color:red;'>{$match[1]} {$match[0]}</span><br>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt = $link->prepare('INSERT INTO `match` (fixture, homeTeam, awayTeam) VALUES (?, ?, ?)');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->bind_param('iss', $id_fixture, $match[1], $match[0]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; &nbsp; &nbsp; } elseif ($match[1] == "stă etapa asta.") {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $free = "<span style='color:red;'>{$match[0]} {$match[1]}</span><br>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt = $link->prepare('INSERT INTO `match` (fixture, homeTeam, awayTeam) VALUES (?, ?, ?)');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->bind_param('iss', $id_fixture, $match[0], $match[1]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "{$match[0]} vs {$match[1]}<br>";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt = $link->prepare('INSERT INTO `match` (fixture, homeTeam, awayTeam) VALUES (?, ?, ?)');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->bind_param('iss', $id_fixture, $match[0], $match[1]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; echo $free;&nbsp; &nbsp; echo "<br>";&nbsp; &nbsp; $i++;}我ID从您的查询中删除了列，因为我假设所有列都是自动生成的 ID，在这种情况下，您不需要为每个列都传递 NULL。