我尝试过使用插入到所需表select * from XXX中然后将其删除的方法，但我似乎无法插入到新表中并删除旧表中的表。
它将trade_id读取为1,2,3,4，但我似乎无法将其删除。第4行，trade_id有一个错误，我似乎不明白为什么没有定义它。
这个查询有什么问题吗？
删除.php
<?php
// we need to know the student id so that we can delete the right student
$tradeid= $_GET['trade_id'];
// the file that contains your database credentials like username and password
require_once('connect.php');
// see Lecture Webp_Week13_14_Using_PHPandMySQL(updating).pptx Slide 4 aka Step 1
$mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database");
// Slide 5 aka Step 2
$stmt = $mysqli->multi_query("INSERT INTO `trade_history1` (session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close)
SELECT session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close
FROM `opentrades`
WHERE `trade_id` = ?;
DELETE FROM `opentrades` WHERE `trade_id` = ?;
COMMIT;");
// Slide 6 aka Step 3 the bind params must correspond to the ?
$stmt->bind_param("i", $tradeid); // 1 ? so we use i. we use i because id is INT
// Slide 7 aka Step 4
$successfullyDeleted = $stmt->execute();
// Slide 8 aka Step 5
// we won't check the delete result here.
// Slide 9 aka Step 6 and 7
$stmt->close();
$mysqli->close();
// if we successfully delete this, we
if ($successfullyDeleted) {
$_SESSION['message'] = 'Successfully deleted';
} else {
$_SESSION['message'] = 'Unable to delete';
}
header('Location: js.php');
?>
代码的重要部分
JS.php
while($row = mysql_fetch_array($result)){ //Creates a loop to loop through results
echo "<tr><td>" . $row['trade_id'] . "</td><td>" . $row['selection'] . "</td><td>" . $row['date'] ."</td><td>" . $row['type'] ."</td><td>" . $row['size'] ."</td><td>" . $row['bidprice'] ."</td><td>" . $row['offerprice'] ."</td><td>" . $row['stoploss'] ."</td><td>" . $row['takeprofit'] ."</td><td>" . $row['profitandloss'] . "</td><td><a href ='delete.php?id=".$row['trade_id']."'>X</a></td></tr>"; //$row['index'] the index here is a field name
}
mysqli:：multi_query对数据库执行查询（一个或多个查询）。它不会创建与mysqli_stmt:：bind_param一起使用并与mysqli_tmt:：execute一起执行的prepare语句（如mysqli:：prepare）。此外，准备好的语句中的查询参数必须由单个SQL语句组成。
您应该这样做一个事务（没有准备好的语句！）（取自PHP+MySQL事务示例）：
$tradeid= filter_var($_GET['trade_id'], FILTER_SANITIZE_NUMBER_INT);
require_once('connect.php');
$mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database");
try {
// First of all, let's begin a transaction
$mysqli->begin_transaction();
// A set of queries; if one fails, an exception should be thrown
$mysqli->query("INSERT INTO `trade_history1` (session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close)
SELECT session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close
FROM `opentrades`
WHERE `trade_id` = " . $tradeid);
$mysqli->query("DELETE FROM `opentrades` WHERE `trade_id` = " . $tradeid);
// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$mysqli->commit();
$_SESSION['message'] = 'Successfully deleted';
} catch (Exception $e) {
// An exception has been thrown
// We must rollback the transaction
$_SESSION['message'] = 'Unable to delete';
$mysqli->rollback();
}
$mysqli->close();
...
或者使用准备好的语句（来自How can I use prepared statements with Transactions with PHP？）：
$tradeid= $_GET['trade_id'];
require_once('connect.php');
$mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database");
try {
// First of all, let's begin a transaction
$mysqli->begin_transaction();
// A set of queries; if one fails, an exception should be thrown
$stmt = $mysqli->stmt_init();
$stmt = $stmt->prepare("INSERT INTO `trade_history1` (session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close)
SELECT session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close
FROM `opentrades`
WHERE `trade_id` = ?");
$stmt->bind_param("i", $tradeid);
$stmt->execute();
$mysqli->query("DELETE FROM `opentrades` WHERE `trade_id` = ?");
$stmt->bind_param("i", $tradeid);
$stmt->execute();
// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$mysqli->commit();
$_SESSION['message'] = 'Successfully deleted';
} catch (Exception $e) {
// An exception has been thrown
// We must rollback the transaction
$_SESSION['message'] = 'Unable to delete';
$mysqli->rollback();
}
$mysqli->close();
...