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

Question

This question is an exact duplicate of:

• How to move then delete field in MySQL 1 answer

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>

Answer 1

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.";
    }
}