在MySQL更新脚本中对许多PHP变量进行流化处理

Question

我有33个变量从Ajax调用传递到我的PHP MySQL更新脚本中，我想简化代码。这是我现在拥有的：

<?php
include 'dbconfig.php';
$p1 = ($_POST['price-1']);
$p2 = ($_POST['price-2']);
$p3 = ($_POST['price-3']);
$p4 = ($_POST['price-4']);
$p5 = ($_POST['price-5']);
$p6 = ($_POST['price-6']);
$p7 = ($_POST['price-7']);
$p8 = ($_POST['price-8']);
$p9 = ($_POST['price-9']);
$p10 = ($_POST['price-10']);
$p11 = ($_POST['price-11']);
$p12 = ($_POST['price-12']);
$p13 = ($_POST['price-13']);
$p14 = ($_POST['price-14']);
$p15 = ($_POST['price-15']);
$p16 = ($_POST['price-16']);
$p17 = ($_POST['price-17']);
$p18 = ($_POST['price-18']);
$p19 = ($_POST['price-19']);
$p20 = ($_POST['price-20']);
$p21 = ($_POST['price-21']);
$p22 = ($_POST['price-22']);
$p23 = ($_POST['price-23']);
$p24 = ($_POST['price-24']);
$p25 = ($_POST['price-25']);
$p26 = ($_POST['price-26']);
$p27 = ($_POST['price-27']);
$p28 = ($_POST['price-28']);
$p29 = ($_POST['price-29']);
$p30 = ($_POST['price-30']);
$p31 = ($_POST['price-31']);
$p32 = ($_POST['price-32']);
$p33 = ($_POST['price-33']);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
$sql = "UPDATE hubs SET price='" . $p1 . "' WHERE id='1';";
$sql.= "UPDATE hubs SET price='" . $p2 . "' WHERE id='2';";
$sql.= "UPDATE hubs SET price='" . $p3 . "' WHERE id='3';";
$sql.= "UPDATE hubs SET price='" . $p4 . "' WHERE id='4';";
$sql.= "UPDATE hubs SET price='" . $p5 . "' WHERE id='5';";
$sql.= "UPDATE hubs SET price='" . $p6 . "' WHERE id='6';";
$sql.= "UPDATE hubs SET price='" . $p7 . "' WHERE id='7';";
$sql.= "UPDATE hubs SET price='" . $p8 . "' WHERE id='8';";
$sql.= "UPDATE hubs SET price='" . $p9 . "' WHERE id='9';";
$sql.= "UPDATE hubs SET price='" . $p10 . "' WHERE id='10';";
$sql.= "UPDATE hubs SET price='" . $p11 . "' WHERE id='11';";
$sql.= "UPDATE hubs SET price='" . $p12 . "' WHERE id='12';";
$sql.= "UPDATE hubs SET price='" . $p13 . "' WHERE id='13';";
$sql.= "UPDATE hubs SET price='" . $p14 . "' WHERE id='14';";
$sql.= "UPDATE hubs SET price='" . $p15 . "' WHERE id='15';";
$sql.= "UPDATE hubs SET price='" . $p16 . "' WHERE id='16';";
$sql.= "UPDATE hubs SET price='" . $p17 . "' WHERE id='17';";
$sql.= "UPDATE hubs SET price='" . $p18 . "' WHERE id='18';";
$sql.= "UPDATE hubs SET price='" . $p19 . "' WHERE id='19';";
$sql.= "UPDATE hubs SET price='" . $p20 . "' WHERE id='20';";
$sql.= "UPDATE hubs SET price='" . $p21 . "' WHERE id='21';";
$sql.= "UPDATE hubs SET price='" . $p22 . "' WHERE id='22';";
$sql.= "UPDATE hubs SET price='" . $p23 . "' WHERE id='23';";
$sql.= "UPDATE hubs SET price='" . $p24 . "' WHERE id='24';";
$sql.= "UPDATE hubs SET price='" . $p25 . "' WHERE id='25';";
$sql.= "UPDATE hubs SET price='" . $p26 . "' WHERE id='26';";
$sql.= "UPDATE hubs SET price='" . $p27 . "' WHERE id='27';";
$sql.= "UPDATE hubs SET price='" . $p28 . "' WHERE id='28';";
$sql.= "UPDATE hubs SET price='" . $p29 . "' WHERE id='29';";
$sql.= "UPDATE hubs SET price='" . $p30 . "' WHERE id='30';";
$sql.= "UPDATE hubs SET price='" . $p31 . "' WHERE id='31';";
$sql.= "UPDATE hubs SET price='" . $p32 . "' WHERE id='32';";
$sql.= "UPDATE hubs SET price='" . $p33 . "' WHERE id='33';";

if ($conn->multi_query($sql)  === TRUE) {
echo "Pricing successfully updated";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>

它看起来很笨重，效率很低。我如何自动获取和定义prices-1-prices-33并相应地更新它，而不必像我那样做？我想将来用这个脚本作为模板，避免拼写错误。

谢谢。

Answer 1

首先从更改HTML输入名称属性

<input type="text" name="price-23">

至

<input type="text" name="prices[23]">

您将在单个阵列$_POST['prices']中获得所有价格。现在，您可以创建一个准备好的语句，并在foreach循环中更新所有值：

$stmt = $conn->prepare('UPDATE hubs SET price = ? WHERE id = ?');
$conn->begin_transaction();
foreach ($_POST['prices'] as $id => $price) {
$stmt->bind_param('si', $price, $id);
$stmt->execute();
}
$conn->commit();