MBRD MBRD - 1 year ago 37
MySQL Question

INSERT into MYSQL and then before closing the connection select the insert id and then adding this id to 3 other tables

I am pretty new to the world of SQL so sorry for my ignorance on this.

I have a form on an admin page that adds a player to a team database. When the form is submitted what I need to have happen is:

  • The player gets inserted into players table (player_id is Primary key and used in next step).

  • A select statement runs to get the player_id.

  • Then inserts that into 2 other tables:

    • team_players and cards.

Below is the best representation of what I have tried:


$first_name = mysqli_real_escape_string($con2, $_POST['first_name']);
$last_name = mysqli_real_escape_string($con2, $_POST['last_name']);
$email = mysqli_real_escape_string($con2, $_POST['email']);
$validation_code = md5($email + microtime());

$sql0 ="INSERT INTO players
(first_name, last_name, email, validation_code)
VALUES ('$first_name', '$last_name','$email', '$validation_code')";

$sql01 = "SELECT player_id FROM players WHERE email='$email'";
$result01 = $con2->query($sql01);

if ($result01->num_rows > 0) {
$row01 = $result01->fetch_assoc();

$playerID = $row01['player_id'];
echo $playerID; //In for debugging. Sometimes it works sometimes it doesn't

$sql02 = "INSERT INTO team_players, cards (player_id, team_id)
VALUES('$playerID', '$id')";

Thanks for any help on this.

Answer Source

You cannot insert into two tables using one query.you can use a transaction and have both of them be contained within one transaction. Otherwise execute two separate queries for each insertion. One more thing, you havenot executed the query for inserting to first table

INSERT INTO team_players (player_id, team_id) VALUES (...);
INSERT INTO cards (player_id, team_id) VALUES  (...);