CODEWITHSUNDEEP
phpsqldatabaseforeign-keys
Negura Alexandru
Negura Alexandru

Reputation: 21

How I pass the id from one table in a row in another table?

I am generating some football fixtures and what I try to do is to insert the fixtures and the games in two tables. The fixtures table is good to go but I am struggling with inserting matches in the 'match' table because this table have a foreign key to 'fixture' table.

fixture table

id_fixture unsigned bigint(20)
fixture bigint(20)

match table

id_match unsigned bigint(20)
fixture bigint(20) -> foreign key to fixture.id_fixture
homeTeam varchar(191)
awayTeam varchar(191)

php algorithm

$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);

How can I pass the fixture.id_fixture to the match.fixture as the games are generated?

Upvotes: 0

Views: 59

Answers (1)

Dharman
Dharman

Reputation: 33325

You can use $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++;
}

I removed the ID columns from your queries, because I assumed that all of them were auto-generate ID, in which case you do not need to pass NULL for each one of them.

Upvotes: 1

Related Questions