我如何将一个表中的 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_fixturehomeTeam 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) {

    $free = "";

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

    $stmt = $link->prepare('INSERT INTO fixture (fixture) VALUES (?)');

    $stmt->bind_param('i', $i);

    $stmt->execute();

    $id_fixture = $link->insert_id; // The auto generated ID

    foreach ($rounds as $match) {

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

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

            $stmt = $link->prepare('INSERT INTO `match` (fixture, homeTeam, awayTeam) VALUES (?, ?, ?)');

            $stmt->bind_param('iss', $id_fixture, $match[1], $match[0]);

            $stmt->execute();

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

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

            $stmt = $link->prepare('INSERT INTO `match` (fixture, homeTeam, awayTeam) VALUES (?, ?, ?)');

            $stmt->bind_param('iss', $id_fixture, $match[0], $match[1]);

            $stmt->execute();

        } else {

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

            $stmt = $link->prepare('INSERT INTO `match` (fixture, homeTeam, awayTeam) VALUES (?, ?, ?)');

            $stmt->bind_param('iss', $id_fixture, $match[0], $match[1]);

            $stmt->execute();

        }

    }

    echo $free;

    echo "<br>";

    $i++;

}

我ID从您的查询中删除了列，因为我假设所有列都是自动生成的 ID，在这种情况下，您不需要为每个列都传递 NULL。