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

Question

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:

if(isset($_POST['submit'])){ 

$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 1

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 have not executed the query for inserting to first table

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