问题描述
我有这段代码可以从jobseeker"表中选择所有字段，并且应该通过将 userType 设置为admin"来更新user"表，其中 userID = $userID(此 userID 属于我的数据库中的用户).然后该语句应该将这些值从jobseeker"表中插入到admin"表中，然后从jobseeker"表中删除该用户.sql 表很好，我的语句将 userType 更改为 admin 并从jobseeker"表中获取用户......但是，当我进入数据库(通过 phpmyadmin)时，管理员已被添加，没有任何详细信息.请任何人解释一下为什么 $userData 没有从 'jobseeker' 表中传递用户的详细信息并将它们插入到 'admin' 表中?
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结构变化(永远不要说永远).
Firstly, never use SELECT * in some code: it will bite you (or whoever has to maintain this application) if the table structure changes (never say never).
您可以考虑使用直接从 SELECT 获取其值的 INSERT:
You could consider using an INSERT that takes its values from a SELECT directly:
"INSERT INTO admin(userID, forename, ..., `password`, ...)
SELECT userID, forename, ..., `password`, ...
FROM jobseeker WHERE userID = ..."
您不必通过 PHP 来执行此操作.
You don't have to go via PHP to do this.
(对于在此答案的早期版本中使用依赖于 mysql_real_escape_string 的上述示例表示歉意.使用mysql_real_escape_string 不是一个好主意，尽管它可能比将参数直接放入查询字符串中略好.)
(Apologies for using an example above that relied on mysql_real_escape_string in an earlier version of this answer. Using mysql_real_escape_string is not a good idea, although it's probably marginally better than putting the parameter directly into the query string.)
我不确定您使用的是哪个 MySQL 引擎，但您也应该考虑在单个事务中执行这些语句(您需要 InnoDB 而不是 MyISAM).
I'm not sure which MySQL engine you're using, but your should consider doing those statements within a single transaction too (you would need InnoDB instead of MyISAM).
另外，我建议使用 mysqli和准备好的语句 以便能够绑定参数:这是一种更简洁的方法，不必转义输入值(以避免 SQL 注入攻击).
In addition, I would suggest using mysqli and prepared statements to be able to bind parameters: this is a much cleaner way not to have to escape the input values (so as to avoid SQL injection attacks).
编辑 2:
(如果魔术引号打开，您可能需要关闭它们.)
(You might want to turn off the magic quotes if they're on.)
$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();
编辑 3: 我没有意识到您的 userID 是一个 int (但这可能就是它的样子，因为您在评论中说过它是自动递增的):将其转换为 int 和/或不要将其用作 WHERE userID = '$userID' 中的字符串(即带引号)(但同样，永远不要将变量直接插入查询，无论是从数据库中读取还是从请求参数中读取).
EDIT 3: I hadn't realised your userID was an int (but that's probably what it is since you've said it's auto-incremented in a comment): cast it to an int and/or don't use it as a string (i.e. with quotes) in WHERE userID = '$userID' (but again, don't ever insert your variable directly in a query, whether read from the DB or a request parameter).
这篇关于将一个表中的值插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！