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>

I would build those 2 INSERT separate. Execute 1st, then right after the second. Why you troubling yourself with combining them...? After the insert, you will have the same data in the database anyway, and still executed from only 1 form submission. — michal
– michal, Commented Oct 20, 2018 at 14:01
Hm! OK, which part of the code do I repeat? I tried having each INSERT on its own and repeated the code found between the "// Prepare an insert statement" and the "// Close statement" but it is still not working. — her66
– her66, Commented Oct 20, 2018 at 17:38

her66 · Accepted Answer · 2018-10-21 23:08:06Z

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

    }

?>

Collectives™ on Stack Overflow

Insert into two sql tables using one html form

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related