php一个表更新另一个表,PHP-将值从一个表插入到另一个表

我有这段代码可以从“ jobseeker”表中选择所有字段,并可以通过将userType设置为“ admin”来更新“ user”表,其中userID = $userID(此userID是我用户中的用户数据库).然后,该语句应将这些值从“ jobseeker”表插入“ admin”表,然后从“ jobseeker表”中删除该用户. sql表很好,我的语句正在将userType更改为admin并从’jobseeker’表中获取用户…但是,当我进入数据库(通过phpmyadmin)时,没有添加任何细节的admin .请问有人能阐明为什么$userData没有将’jobseeker’表中的用户详细信息传递到’admin’表中吗？

这是代码：

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 *：如果表结构发生变化(永远不要说从不),它将(或必须维护此应用程序的人)咬住您.

您可以考虑使用直接从SELECT获取其值的INSERT：

"INSERT INTO admin(userID, forename, ..., `password`, ...)

SELECT userID, forename, ..., `password`, ...

FROM jobseeker WHERE userID = ..."

您不必通过PHP来执行此操作.

(使用上面示例的道歉在此答案的早期版本中依赖于mysql_real_escape_string.Using mysql_real_escape_string is not a good idea,尽管可能比直接将参数直接放入查询字符串要好一些.)

我不确定您使用的是哪个MySQL引擎,但是您也应该考虑在单个事务中执行这些语句(您需要InnoDB而不是MyISAM).

另外,我建议使用mysqli and prepared statements来绑定参数：这是一种更简洁的方法,不必转义输入值(以避免SQL注入攻击).

编辑2：

(如果启用了魔术引号,则可能要关闭它们.)

$userID = $_GET['userID'];

// Put the right connection parameters

$mysqli = new mysqli("localhost", "user", "password", "db");

if (mysqli_connect_errno()) {

printf("Connect failed: %s\n", 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();

编辑3：我还没有意识到您的userID是一个i​​nt(但这可能是因为您已经说过它在注释中自动增加了)：将其转换为int和/或不将其用作字符串(即带引号)的WHERE userID =’$userID'(但同样,永远不要将变量直接插入查询中,无论是从数据库中读取还是从请求参数中读取).

标签：php,sql,mysql,insert

来源： https://codeday.me/bug/20191009/1881535.html