使用条件更新查询？

发布于 2024-08-31 01:31:17 字数 1149 浏览 2 评论 0原文

我不确定这是否可能。如果没有，请告诉我。

我有一个更新 3 个字段的 PDO mysql。

$update = $mypdo->prepare("UPDATE tablename SET field1=:field1, 
                                                field2=:field2, 
                                                field3=:field3 
                                            WHERE key=:key");

但我希望仅当 $update3 = true; 时才更新 field3 （意味着 field3 的更新是由条件语句控制的）

是这可以通过单个查询来完成吗？

我可以使用 2 个查询来完成此操作，其中更新 field1 和 field2，然后检查布尔值，并根据需要在单独的查询中更新 field3。

//run this query to update only fields 1 and 2
$update_part1 = $mypdo->prepare("UPDATE tablename SET field1=:field1, 
                                                      field2=:field2
                                                  WHERE key=:key");

//if field3 should be update, run a separate query to update it separately
if ($update3){
  $update_part2 = $mypdo->prepare("UPDATE tablename SET field3=:field3 
                                                    WHERE key=:key");
}

但希望有一种方法可以在一次查询中完成此任务吗？

原文

I'm not sure if this possible. If not, let me know.

I have a PDO mysql that updates 3 fields.

$update = $mypdo->prepare("UPDATE tablename SET field1=:field1, 
                                                field2=:field2, 
                                                field3=:field3 
                                            WHERE key=:key");

But I want field3 to be updated only when $update3 = true; (meaning that the update of field3 is controlled by a conditional statement)

Is this possible to accomplish with a single query?

I could do it with 2 queries where I update field1 and field2 then check the boolean and update field3 if needed in a separate query.

//run this query to update only fields 1 and 2
$update_part1 = $mypdo->prepare("UPDATE tablename SET field1=:field1, 
                                                      field2=:field2
                                                  WHERE key=:key");

//if field3 should be update, run a separate query to update it separately
if ($update3){
  $update_part2 = $mypdo->prepare("UPDATE tablename SET field3=:field3 
                                                    WHERE key=:key");
}

But hopefully there is a way to accomplish this in 1 query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

多孤肩上扛 2024-09-07 01:31:17

您不需要进行多次查询。为什么不根据该条件构建查询字符串，然后将其传递给数据库适配器来执行？可以是这样的：

    $pdo = new PDO($dsn, $user, $password);

    $sql = "UPDATE table SET columnname1=:field1, columname2=:field2";

    if ( $update ) $sql .= ",columname3=:field3";

    $sql .= " WHERE key=:key";

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":key", $key, PDO::PARAM_INT);
    $stmt->bindParam(":field1", $field1, PDO::PARAM_STR);
    $stmt->bindParam(":field2", $field2, PDO::PARAM_STR);

    if($update) $stmt->bindParam(":field3", $field3, PDO::PARAM_STR);

    $stmt->execute();

You don't need to do multiple queries. Why don't you just structure your query string based on that conditional, and after that just pass it to your DB adapater for execution ? It could be as follow:

    $pdo = new PDO($dsn, $user, $password);

    $sql = "UPDATE table SET columnname1=:field1, columname2=:field2";

    if ( $update ) $sql .= ",columname3=:field3";

    $sql .= " WHERE key=:key";

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":key", $key, PDO::PARAM_INT);
    $stmt->bindParam(":field1", $field1, PDO::PARAM_STR);
    $stmt->bindParam(":field2", $field2, PDO::PARAM_STR);

    if($update) $stmt->bindParam(":field3", $field3, PDO::PARAM_STR);

    $stmt->execute();

回复收藏 0 原文

~没有更多了~