php移动mysql数据的行数据,将数据从旧表移动到另一个表PHP MYSQL

i have some problem on creating a delete query on my code.

the flow will be, moving data from one table to another, im placing delete codes but its not working

my code

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;

?>

alert("The account has been successfuly verified");

location.href = "account_man.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);

}

?>

The old table is named "tbl_user_reco" and "tbl_accounts_reco".

Note: tbl_user_reco is auto increment

Delete Code

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

}

This is my merged Add and Delete Code and when i run it shows the "Record deleted successfully" but it never deletes anything but it adds data to the new table

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

}

?>

解决方案

If you see this code, you are overwriting $sql, and its executing only second query,

$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)) {