mysql How can I update a value in another table after I post-CodePudding

I have a page that allows me to input a weight.

The weight goes into a weight table, birdid is taken from the original birds id using a link on a previous page taking over the id of the bird. All this works fine.

What I need to do is when submit is pressed, I want it to continue to operate how it is but also update the column weighed to yes where id = id in a different table called birds.

this is my sql

<?php
$birdid = $_GET['id'];
?>
        



<?php if (isset($_POST['submit'])) {

require "../config.php"; require "../common.php";

try  {
    $connection = new PDO($dsn, $username, $password, $options);
   

    
    
    $new_user = array(
       "BirdId" => $_POST['BirdId'],
        "Weight"  => $_POST['Weight']
        
    );

    $sql = sprintf(
        
            "INSERT INTO %s (%s) values (%s)",
            "Weights",
        
            implode(", ", array_keys($new_user)),
            ":" . implode(", :", array_keys($new_user))
    );
   echo "<script type=\"text/javascript\">

document.location.href='weightsselect.php'; // ";

    $statement = $connection->prepare($sql);
    
    $statement->execute($new_user);
    $statement->execute($sql1);
    
} catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
}}; ?>

can anyone advise how I do this please? I'm only learning as I go along.

CodePudding user response：

I've edited your code to make it a bit secured. Kindly try below code.

<?php

if( isset($_POST['submit']) ){
    require "../config.php";
    require "../common.php";

    try{
        $birdid     = $_GET['id']; // get birdid
        $connection = new PDO($dsn, $username, $password, $options);

        // insert weight
        $statement = $connection->prepare("
            INSERT INTO Weights (BirdId, Weight) 
            VALUES (:BirdId, :Weight)
        ");
        $statement->execute([
            "BirdId" => $_POST['BirdId'],
            "Weight" => $_POST['Weight']
        ]);

        // update weighed to yes
        $statement2 = $connection->prepare("
            UPDATE birds SET weighed='yes' 
            WHERE id=:id
        ");
        $statement2->execute(['id' => $birdid]);
        
    }catch(PDOException $error) {
        echo $error->getMessage();
    }
}

?>