将值从一个表插入另一个表 [英] INSERTING values from one table into another table
问题描述
我有这段代码可以从"jobseeker"表中选择所有字段，并可以通过将userType设置为"admin"来更新"user"表，其中userID = $ userID(该userID为我的数据库中的用户).然后，该语句应将这些值从"jobseeker"表插入到"admin"表中，然后从"jobseeker表"中删除该用户. sql表很好，我的语句正在将userType更改为admin并从'jobseeker'表中获取用户...但是，当我进入数据库(通过phpmyadmin)时，没有添加任何细节的admin .请问有人可以阐明为什么$ userData没有将'jobseeker'表中的用户详细信息传递到'admin'表中吗?
这是代码:
<?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 *:如果表结构发生变化(永远不要说从不)，它将咬你(或任何必须维护此应用程序的人). ).
您可以考虑使用直接从SELECT获取其值的INSERT:
"INSERT INTO admin(userID, forename, ..., `password`, ...)
SELECT userID, forename, ..., `password`, ...
FROM jobseeker WHERE userID = ..."
您不必通过PHP就能做到这一点.
(以上回答的早期版本中依靠mysql_real_escape_string使用示例的道歉.不使用mysql_real_escape_string一个好主意，尽管它可能比直接将参数放入查询字符串要好一些.)
我不确定您使用的是哪个MySQL引擎，但是您也应该考虑在单个事务中执行这些语句(您需要InnoDB而不是MyISAM).
此外，我建议使用 mysqli和准备好的语句能够绑定参数:这是一种更干净的方法，不必转义输入值(以避免SQL注入攻击).
(如果您要打开魔术引号，则可能要关闭它们.)
$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();
我还没有意识到您的userID是一个整数(但是这可能是因为您已经说过它在注释中自动增加了):将其转换为int和/或不要将其用作WHERE userID = '$userID'中的字符串(即带引号)(但同样，不要将变量直接插入查询中，无论是从数据库中读取还是从请求参数中读取).
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?
Here is the code:
<?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');
?>
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).
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 = ..."
You don't have to go via PHP to do this.
(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.)
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).
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).
EDIT 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\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();
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).
这篇关于将值从一个表插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
