Insert into two sql tables using one html form

Question

I am trying to insert data into two separate mysql tables using one form.

In my php code below I have two INSERT statements, one for Table Games and one for Table Results. If I run the code with either the "INSERT INTO Games ..." or the "INSERT INTO Results ..." statements alone (modifying of corse the form to include only the corresponding one or two respective fields only) the data gets inserted into the database correctly. Its when I try to do it simultaneously that I can't figure out what I am doing wrong. Just to note that when I run the two INSERT statements together in phpmyadmin the data gets inserted into the two tables correctly.

I have read about mysqli_multi_query and tried various options to use it but without any result as I am not sure how to use it with the following prepare statment I am using.

if($stmt = mysqli_prepare($link, $sql))

Here is the php part:

<?php

// Define variables and initialize with empty values
$tourn_id = "";
$game_id = "";
$player_id = "";
$tourn_name_err = "";

// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    // Check input errors before inserting in database
    if(empty($tourn_name_err)){

        // Prepare an insert statement
        $sql = "INSERT INTO Games (idTournaments) VALUES (?);
        INSERT INTO Results (idGames, idPlayers) VALUES (?, ?);";

        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "iii", $param_tourn_id, $param_game_id, $param_player_id);

            // Set parameters
            $param_tourn_id = trim($_POST["tourn_id"]);
            $param_game_id = trim($_POST["game_id"]);
            $param_player_id = trim($_POST["player_id"]);

            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Redirect to login page
                header("location: welcome.php");
            } else{
                echo "Something went wrong. Please try again later.";
            }
        }

        // Close statement
        mysqli_stmt_close($stmt);
    }

    // Close connection
    mysqli_close($link);
}
?>

and here is the HTML Form part:

<div class="wrapper">
    <form class="form-signin" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
        <h1 class="form-signin-heading">New Match</h1>

        <div class="form-group <?php echo (!empty($tourn_name_err)) ? 'has-error' : ''; ?>">
            <label>Enter Tournament ID here ...:</label>
            <input type="number" name="tourn_id"class="form-control" value="<?php echo $tourn_id; ?>">
            <span class="help-block"><?php echo $tourn_name_err; ?></span>
        </div>

        <div class="form-group <?php echo (!empty($tourn_name_err)) ? 'has-error' : ''; ?>">
            <label>Enter Game ID here ...:</label>
            <input type="number" name="game_id"class="form-control" value="<?php echo $game_id; ?>">
            <span class="help-block"><?php echo $tourn_name_err; ?></span>
        </div>

        <div class="form-group <?php echo (!empty($tourn_name_err)) ? 'has-error' : ''; ?>">
            <label>Enter Player ID here ...:</label>
            <input type="number" name="player_id"class="form-control" value="<?php echo $player_id; ?>">
            <span class="help-block"><?php echo $tourn_name_err; ?></span>
        </div>

        <div class="form-group">
            <input type="submit" class="btn btn-primary" value="Submit">
            <input type="reset" class="btn btn-default" value="Reset">
        </div>
    </form>
</div>

Answer 1

OK, got it to work after many trial-error attempts and getting rid of the "Prepare statements" in the original php code. I also followed as much as possible the mysqli_multi_query example from the manual.

I am not sure that setting the parameters like below is necessary tough.

$param_tourn_id = trim($_POST["tourn_id"]);

Here is the complete php code that works:

<?php

// Define variables and initialize with empty values
$tourn_id = "";
$game_id = "";
$player1_id = "";
$tourn_name_err = "";


// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){


        // Set parameters
        $param_tourn_id = trim($_POST["tourn_id"]);
        $param_game_id = trim($_POST["game_id"]);
        $param_player1_id = trim($_POST["player1_id"]);


        // Prepare an insert statement
        $sql = "INSERT INTO Games (idTournaments) VALUES ($param_tourn_id);";
        $sql .= "INSERT INTO Results (idGames, idPlayers) VALUES ($param_game_id, $param_player1_id)";


        /* execute multi query */
        if (mysqli_multi_query($link, $sql)) {
            do {
                /* store first result set */
                if ($result = mysqli_store_result($link)) {
                    while ($row = mysqli_fetch_row($result));
                    mysqli_free_result($result);
                }
                /* print divider */
                if (mysqli_more_results($link));
            } while (mysqli_next_result($link));
        }

        /* close connection */
        mysqli_close($link);

    }

?>