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

Question

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

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

我的代码

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

}



?>

Answer 1

如果您看到此代码，则说明您正在覆盖 $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)) {