php - 将数据从旧表移动到另一个表PHP MYSQL

我在代码上创建删除查询时遇到问题。

流程将是，将数据从一个表移动到另一个表，并放置删除代码，但是它不起作用

我的代码

<?php

require_once('php/dbase.php');


$query="INSERT INTO tbl_user (username, password, access_type) VALUES ('".$_POST['user']."','".$_POST['pass']."','staff')";
if($mysqli->query($query)){
    $id=$mysqli->insert_id;

        $query2="INSERT INTO tbl_accounts (id,fname,mname,lname,gender,designation,s_question,s_answer) VALUES (".$id.",'".$_POST['fname']."','".$_POST['mname']."','".$_POST['lname']."','".$_POST['gender']."','".$_POST['designation']."','".$_POST['s_question']."','".$_POST['s_answer']."')";

        if($mysqli->query($query2)){
            $error=0;
            ?>
            <script type="text/javascript">
            alert("The account has been successfuly verified");
            location.href = "account_man.php";
            </script>

            <?php
        }else{
            $error=1;
            $message="Error";
            $data=array('error' =>$error , 'message'=>$message);
            echo json_encode($data);
        }

}else{

$error=1;
            $message="Error";
            $data=array('error' =>$error , 'message'=>$message);
            echo json_encode($data);

}



?>

旧表名为“ tbl_user_reco”和“ tbl_accounts_reco”。

注意：tbl_user_reco是自动递增

删除密码

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tdsw_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to delete a record
$sql = "DELETE FROM tbl_user_reco WHERE id= '.$id.'";
$sql = "DELETE FROM tbl_accounts_reco WHERE id= '.$id.'";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);

        }else{
            $error=1;
            $message="Error";
            $data=array('error' =>$error , 'message'=>$message);
            echo json_encode($data);
        }

这是我合并的添加和删除代码，运行时显示“记录已成功删除”，但从不删除任何内容，但会将数据添加到新表中

<?php

require_once('php/dbase.php');


$query="INSERT INTO tbl_user (username, password, access_type) VALUES ('".$_POST['user']."','".$_POST['pass']."','staff')";
if($mysqli->query($query)){
    $id=$mysqli->insert_id;

        $query2="INSERT INTO tbl_accounts (id,fname,mname,lname,gender,designation,s_question,s_answer) VALUES (".$id.",'".$_POST['fname']."','".$_POST['mname']."','".$_POST['lname']."','".$_POST['gender']."','".$_POST['designation']."','".$_POST['s_question']."','".$_POST['s_answer']."')";

        if($mysqli->query($query2)){
            $error=0;
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tdsw_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to delete a record
$sql = "DELETE FROM tbl_user_reco WHERE id= '.$id.'";
$sql = "DELETE FROM tbl_accounts_reco WHERE id= '.$id.'";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);

        }else{
            $error=1;
            $message="Error";
            $data=array('error' =>$error , 'message'=>$message);
            echo json_encode($data);
        }

}else{

$error=1;
            $message="Error";
            $data=array('error' =>$error , 'message'=>$message);
            echo json_encode($data);

}



?>

最佳答案

如果看到此代码，则说明您正在覆盖$sql，并且它仅执行第二个查询，

$sql = "DELETE FROM tbl_user_reco WHERE id= '.$id.'";
$sql = "DELETE FROM tbl_accounts_reco WHERE id= '.$id.'";//$sql overwriting.

if (mysqli_query($conn, $sql)) {