将一个表中的值插入到另一个表中

I have this code to select all the fields from the 'jobseeker' table and with it it's supposed to update the 'user' table by setting the userType to 'admin' where the userID = $userID (this userID is of a user in my database). The statement is then supposed to INSERT these values form the 'jobseeker' table into the 'admin' table and then delete that user from the 'jobseeker table. The sql tables are fine and my statements are changing the userType to admin and taking the user from the 'jobseeker' table...however, when I go into the database (via phpmyadmin) the admin has been added by none of the details have. Please can anyone shed any light onto this to why the $userData is not passing the user's details from 'jobseeker' table and inserting them into 'admin' table?

代码如下:

<?php

include ('../database_conn.php');

$userID = $_GET['userID'];

$query = "SELECT * FROM jobseeker WHERE userID = '$userID'";
$result = mysql_query($query);
$userData = mysql_fetch_array ($result, MYSQL_ASSOC);
$forename = $userData ['forename'];
$surname = $userData ['surname'];
$salt = $userData ['salt'];
$password = $userData ['password'];
$profilePicture = $userData ['profilePicture'];

$sQuery = "UPDATE user SET userType = 'admin' WHERE userID = '$userID'";

$rQuery = "INSERT INTO admin (userID, forename, surname, salt, password, profilePicture) VALUES ('$userID', '$forename', '$surname', '$salt', '$password', '$profilePicture')";

$pQuery = "DELETE FROM jobseeker WHERE userID = '$userID'";


mysql_query($sQuery) or die (mysql_error());
$queryresult = mysql_query($sQuery) or die(mysql_error());


mysql_query($rQuery) or die (mysql_error());
$queryresult = mysql_query($rQuery) or die(mysql_error());

mysql_query($pQuery) or die (mysql_error());
$queryresult = mysql_query($pQuery) or die(mysql_error());


mysql_close($conn);


header ('location:     http://www.numyspace.co.uk/~unn_v002018/webCaseProject/index.php');

?>

推荐答案

首先，千万不要在某些代码中使用 SELECT *:如果 table结构变化(永远不要说永远).

"INSERT INTO admin(userID, forename, ..., `password`, ...)
    SELECT userID, forename, ..., `password`, ...
    FROM jobseeker WHERE userID = ..."

$userID = $_GET['userID'];

// Put the right connection parameters
$mysqli = new mysqli("localhost", "user", "password", "db");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s
", mysqli_connect_error());
    exit();
}

// Use InnoDB for your MySQL DB for this, not MyISAM.
$mysqli->autocommit(FALSE);

$query = "INSERT INTO admin(`userID`, `forename`, `surname`, `salt`, `password`, `profilePicture`)"
    ." SELECT `userID`, `forename`, `surname`, `salt`, `password`, `profilePicture` "
    ." FROM jobseeker WHERE userID=?";

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', (int) $userID);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

$query = "UPDATE user SET userType = 'admin' WHERE userID=?";

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', (int) $userID);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

$query = "DELETE FROM jobseeker WHERE userID=?";

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', (int) $userID);
    $stmt->execute();
    $stmt->close();
} else {
    die($mysqli->error);
}

$mysqli->commit();

$mysqli->close();