我有一个检索多行数据的表单,每个项都有一个文本区域,供用户对特定项进行评论。返回的项目数量是可变的,他们不必在任何/所有框中留下评论。
<textarea name="comment[]" cols="25" rows="2"><?php echo $f2; ?></textarea>
<input name="tableid[]" type="hidden" value="<?php echo $f1; ?>">
echo语句使用当前存储在数据库中的内容填充文本区域,因为用户可以修改其他人输入的内容。
当它被传递到表单处理页面时,它会返回这个。。
Submit: Submit
comment: Test Comment 1,Test Comment 2
tableid: 590,591
因此它似乎正确地通过了数组。我正在使用此代码更新数据库
$conn = new PDO("mysql:host=xxxx;dbname=xxxxx",$username,$password);
$i = 0;
if(isset($_POST['submit'])) {
foreach($_POST['comment'] as $comment) {
$comment = $_POST['comment'][$i];
$id = $_POST['tableid'][$i];
$stmt = $conn->prepare("UPDATE reservations SET comment=:comment WHERE tableid=:id");
$stmt->bindValue(':comment', $comment, PDO::PARAM_INT);
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$i++;
}
}
然而,这似乎根本没有更新,我哪里出了问题?
非常感谢
很少有东西:
- 将PDO设置为在出现错误时抛出
PDOException
。这将使调试更加容易 - 准备好的语句的意义在于,您可以使用不同的变量多次调用它,也就是说,您只需要准备一次,然后多次调用它。你也从中获得了不错的性能提升
代码:
$conn = new PDO("mysql:host=xxxx;dbname=xxxxx", $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //Set PDO to fire PDOExceptions on errors.
PDO::ATTR_EMULATE_PREPARES => false //Disable emulated prepares. Solves some minor edge cases.
]);
//No need for incrementer. The index of the comment should be enough.
if (isset($_POST['submit'])) {
//Note the prepare on the outside.
$stmt = $conn->prepare("UPDATE `reservations` SET `comment` = :comment WHERE `tableid` = :id");
//As well as the binding. By using bindParam, and supplying a variable, we're passing it by reference.
//So whenever it changes, we don't need to bind again.
$stmt->bindParam(":comment", $comment, PDO::PARAM_STR);
$stmt->bindParam(":id", $id, PDO::PARAM_INT);
foreach ($_POST['comment'] as $index => $comment) {
//All that's left is to set the ID, see how we're reusing the $index of the comment input?
$id = $_POST['tableid'][$index];
$stmt->execute();
}
}
<textarea name="comment[<?=$f1?>]" cols="25" rows="2"><?=$f2?></textarea>
<?php
$dsn = "mysql:host=xxxx;dbname=xxxxx";
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$conn = new PDO($dsn, $username, $password, $opt);
$sql = "UPDATE reservations SET comment=? WHERE tableid= ?";
$stmt = $conn->prepare($sql);
foreach ($_POST["comment"] as $id => $comment) {
if ($comment) {
$stmt->execute([$comment, $id]);
}
}
如果使用CASE,速度会快2-3倍,例如查询:
UPDATE website
SET http_code = CASE id_website
WHEN 1 THEN 200
WHEN 2 THEN 201
WHEN 3 THEN 202
END,
link_exists = CASE id_website
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
END
WHERE id_website IN (1,2,3)
我在1000行上进行了测试。
准备时间:0.328秒
案例:0.109 s