不从MySQL中的表中删除数据[重复]

I am having an issue where I am trying to move data from table 1 to table 2 and then delete that row moved from table 1.

Here is my code:

<?php
if(isset($_POST["id"]) && !empty($_POST["id"])){

require_once 'config.php';


$sql = "INSERT INTO table_2 SELECT * FROM table_1 WHERE id = ? ";
$sql = "DELETE FROM table_1 WHERE id = ? ";


if($stmt = mysqli_prepare($link, $sql)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "i", $param_id);

    // Set parameters
    $param_id = trim($_POST["id"]);

    // Attempt to execute the prepared statement
    if(mysqli_stmt_execute($stmt)){
        // Records deleted successfully. Redirect to landing page
        header("location: index.php");
        exit();
    } else{
        echo "Oops! Something went wrong. Please try again later.";
    }
}

// Close statement
mysqli_stmt_close($stmt);

// Close connection
mysqli_close($link);
} else{
// Check existence of id parameter
if(empty(trim($_GET["id"]))){
    // URL doesn't contain id parameter. Redirect to error page
    header("location: error.php");
    exit();
}
}
?>

The problem with the above code is that it deletes it from both tables. When I remove the $sql = "DELETE FROM table_1 WHERE id = ? "; from the code it then moves the data fine to table 2 but not deleting it from table1

</div>

You are overwriting your insert with delete ... for avoid this you could use different vars for query and execute the two different query ..

$sql1 = "INSERT INTO table_2 SELECT * FROM table_1 WHERE id = ? ";
$sql2 = "DELETE FROM table_1 WHERE id = ? ";


if($stmt = mysqli_prepare($link, $sql1)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "i", $param_id);

    // Set parameters
    $param_id = trim($_POST["id"]);

    // Attempt to execute the prepared statement
    if(mysqli_stmt_execute($stmt)){
        // Records deleted successfully. Redirect to landing page
        header("location: index.php");
        exit();
    } else{
        echo "Oops! Something went wrong. Please try again later.";
    }
}

if($stmt = mysqli_prepare($link, $sql2)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "i", $param_id);

    // Set parameters
    $param_id = trim($_POST["id"]);

    // Attempt to execute the prepared statement
    if(mysqli_stmt_execute($stmt)){
        // Records deleted successfully. Redirect to landing page
        header("location: index.php");
        exit();
    } else{
        echo "Oops! Something went wrong. Please try again later.";
    }
}

You are redeclaring the $sql variable.

$sql = "INSERT INTO table_2 SELECT * FROM table_1 WHERE id = ? ";
$sql = "DELETE FROM table_1 WHERE id = ? ";

here, the value of the variable $sql becomes

DELETE FROM table_1 WHERE id = ? 

hence, when you execute the query, only the delete action takes place.

To fix this, you can use mysqli_multi_query(), but you'll have to give up on the prepared statement, since prepared statements can execute only one MySQL query.

If you want to keep the prepared statements, you can use this code:

 $sql1 = "INSERT INTO table_2 SELECT * FROM table_1 WHERE id = ? ";
    $sql2 = "DELETE FROM table_1 WHERE id = ? ";


    if($stmt1 = mysqli_prepare($link, $sql1) && $stmt2 = mysqli_prepare($link, $sql2)){

        // Set parameters
        $param_id = trim($_POST["id"]);
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt1, "i", $param_id);
        mysqli_stmt_bind_param($stmt2, "i", $param_id);

        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt1) && mysqli_stmt_execute($stmt2)){
            // Records deleted successfully. Redirect to landing page
            header("location: index.php");
            exit();
        } else{
            echo "Oops! Something went wrong. Please try again later.";
        }
    }

You're overwriting $sql variable, so INSERT statement won't get executed at all.

$sql = "INSERT INTO ...";
$sql = "DELETE ...";

You could use mysqli_multi_query but given the current scenario, you should individually prepare each query, bind appropriate variable and execute them, like this:

<?php
if(isset($_POST["id"]) && !empty($_POST["id"])){
    require_once 'config.php';
    $param_id = trim($_POST["id"]);

    $sql1 = "INSERT INTO table_2 SELECT * FROM table_1 WHERE id = ? ";
    $sql2 = "DELETE FROM table_1 WHERE id = ? ";


    if($stmt = mysqli_prepare($link, $sql1)){
        mysqli_stmt_bind_param($stmt, "i", $param_id);
        if(mysqli_stmt_execute($stmt)){
            // Record inserted successfully. Close statement and delete record from table_1
            mysqli_stmt_close($stmt);
            if($stmt = mysqli_prepare($link, $sql2)){
                mysqli_stmt_bind_param($stmt, "i", $param_id);
                if(mysqli_stmt_execute($stmt)){
                    // Close statement
                    mysqli_stmt_close($stmt);
                    // Records deleted successfully. Redirect to landing page
                    header("location: index.php");
                    exit();
                }else{
                    echo "Oops! Something went wrong. Please try again later.";
                }
            }
        } else{
            echo "Oops! Something went wrong. Please try again later.";
        }
    }

    // Close connection
    mysqli_close($link);
} else{
    // Check existence of id parameter
    if(empty(trim($_GET["id"]))){
        // URL doesn't contain id parameter. Redirect to error page
        header("location: error.php");
        exit();
    }
}
?>