PHP/MySQL:结合 UPDATE 查询?

问题描述：

堆栈溢出.PHP 和 SQL 新手在这里.

Stack Overflow. PHP and SQL novice here.

作为多用户私人消息系统的一部分，我一直在尝试编写以了解如何通过 PDO 与数据库正确交互，我有两个单独的查询，当用户删除消息时，通过单个查询执行函数 deleteMessage():

As part of a multi-user private messaging system I've been trying to write to learn how to properly interact with a database through PDO, I have two separate queries that are executed when a user deletes a message, via a single function deleteMessage():

UPDATE messages SET s_deleted = 1 WHERE id = :id AND sender = :sender
UPDATE messages SET r_deleted = 1 WHERE id = :id AND recipient = :recipient

它们工作得很好，足以完成我需要的工作，但一个接一个地运行，就像我目前正在做的那样，并没有让我觉得特别高效.

They work well enough to accomplish what I need but running both one after the other, as I am currently doing, doesn't strike me as particularly efficient.

我查看了 CASE，但就我而言可以理解这不是我所需要的.

I looked into CASE, but as far as I could understand it wasn't quite what I needed.

有没有办法将这两个查询结合起来，这样我就不会用无关的请求来填充我的数据库?将每个查询拼接成一个单独的函数是否会更好，即 deleteMessageSender() 和 deleteMessageRecipient()?

Is there a way to combine these two queries so that I'm not peppering my database with extraneous requests? Would I be better off splicing each query into a separate function, i.e., deleteMessageSender() and deleteMessageRecipient()?

答

UPDATE messages
SET s_deleted = IF(sender = :sender, 1, s_deleted),
    r_deleted = IF(recipient = :recipient, 1, r_deleted)
WHERE id = :id

如您所见，技巧"是在不满足条件时简单地将列设置为其现有值，以便仅在必要时更新.

As you can see, the "trick" is to simply set a column to its existing value when the criteria isn't met, so it only gets updated when necessary.

PHP/MySQL:结合 UPDATE 查询?

相关推荐