使用PDO更新多列和多行 [英] Updating multiple columns and rows using PDO

问题描述

我从

I picked up a small snippet from a forum that allows me to update multiple row using PDO. The example only allows single column, but I wish it can enable column-wise update.

我对代码段做了一些修改，问题是，对row(url)的一次更改将更改row(url)中的所有条目

I modified the snippet a bit, the problem is, a single change in row(url) will change all entry in row(url)

如果某人敏锐的眼睛看到了什么问题:

If somebody has keen eyes to see what is the problem:

if (isset($_POST['submit'])) {
$stmt = $db->prepare("UPDATE `$tbl_name` SET `url`=:url, `country`=:country WHERE id=:id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':url', $url, PDO::PARAM_STR);
$stmt->bindParam(':country', $country, PDO::PARAM_STR);
foreach ($_POST['url'] as $id => $url) {
    $stmt->execute();
}
foreach ($_POST['country'] as $id => $country) {
    $stmt->execute();
}
echo '<h1>Updated the records.</h1>';
}
// Print the form.
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF']) . '" method="post">';
foreach ($db->query("SELECT * FROM `$tbl_name` ORDER BY `id`") as $row) {
    echo '<input type="text" name="url[' . (int)$row['id'] . ']" value="'
        . htmlspecialchars($row['url']) . '" /><input type="text" name="country[' . (int)$row['id'] . ']" value="'
        . htmlspecialchars($row['country']) . '" /><br />';
}
echo '<input type="submit" name="submit" value="Update" /></form>';

推荐答案

您只想做一个循环，同时绑定$url和$country.像这样

You're only going to want to do one loop, binding both $url and $country. Something like this

if (!isset($_POST['url'], $_POST['country']) || count($_POST['url']) != count($_POST['country'])) {
    throw new Exception('URL / country mismatch');
}

foreach ($_POST['url'] as $id => $url) {
    if (!array_key_exists($id, $_POST['country'])) {
        throw new Exception("No matching country for ID $id");
    }
    $country = $_POST['country'][$id];
    $stmt->execute();
}

这篇关于使用PDO更新多列和多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！