我试图将一行从一个表移动到另一个表，但不起作用。它在第一行做得很好，但我什么都做不到，剩下的都可以回声出来，好吧，只是不进入我的DB。
$result = mysql_query("SELECT * FROM coffees WHERE id='$id'");
while($row = mysql_fetch_array($result)) {
mysql_query("INSERT INTO coffeeorder(coffeetype,topping,shots,milk,size,price) VALUES ('coffeetype', 'topping', 'shots', 'milk', 'Size', 'price')");
echo $row['coffeetype'] . " " . $row['topping']." " . $row['shots']." " . $row['milk']." " . $row['size']." $" . $row['price']."<br />";
$totalPrice = $totalPrice + $row['price'];
}
有什么想法为什么不起作用吗？
完整代码：
<?php
session_start();
if(isset($_SESSION["id"])){
$id = $_SESSION["id"];
$name = $_SESSION["name"];
}else {header('Location: index.php');}
include_once "dbcon.php";
$totalPrice = 0;
$result = mysql_query("SELECT * FROM coffees WHERE id='$id'");
while($row = mysql_fetch_array($result))
{
mysql_query("INSERT INTO `coffeeorder`(`coffeetype`,`topping`,`shots`,`milk`,`size`,`price`) VALUES ('".$row['coffeetype']."', '".$row['topping']."', '".$row['shots']."', '".$row['milk']."', '".$row['size']."', '".$row['price']."')");
echo $row['coffeetype'] . " " . $row['topping']." " . $row['shots']." " . $row['milk']." " . $row['size']." $" . $row['price']."<br />";
$totalPrice = $totalPrice + $row['price'];
}
echo "Total Price $".$totalPrice;
?>
请尝试移动到PDO:
<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$stmt = $db->query("SELECT * FROM coffees WHERE id=:id");
$stmt->execute(array(':id' => $id));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt = $db->query("INSERT INTO coffeeorder(coffeetype,topping,shots,milk,size,price) VALUES (:coffeetype, :topping, :shots, :milk, :size, :price)");
$stmt->execute(array(':coffeetype' => $row['coffeetype'], ':topping' => $row['topping'],':shots' => $row['shots'],':milk' => $row['milk'],':size' => $row['size'],':price' => $row['price']));
echo $row['coffeetype'] . " " . $row['topping']." " . $row['shots']." " . $row['milk']." " . $row['size']." $" . $row['price']."<br />";
$totalPrice = $totalPrice + $row['price'];
?>
您还可以在一个查询中执行插入和选择操作：
INSERT INTO `coffeeorder` (
`coffeetype`,
`topping`,
`shots`,
`milk`,
`size`,
`price`
)
SELECT
`coffeetype`,
`topping`,
`shots`,
`milk`,
`size`,
`price`
FROM `coffees`
WHERE `id`='$id'
但是，这会导致多个表中的数据重复。你真正应该做的是把你的桌子设置得有点不同，在咖啡订购表中，不要存储关于咖啡的所有信息，只存储咖啡的id。然后在查询订单时，您可以加入咖啡桌并获取咖啡信息。
SELECT
`coffee`.`coffeetype`,
`coffee`.`topping`,
`coffee`.`shots`,
`coffee`.`milk`,
`coffee`.`size`,
`coffee`.`price`
FROM `coffeeorder`
JOIN `coffee`
ON `coffee`.`id`=`coffeeorder`.`coffee_id`
这是数据库做得最好的（我想除了存储数据之外）。