如何在SQL查询中提供条件？

Question

我正在尝试更新数据库表。如果表单中的密码为空，我如何通过提供一个条件来防止表单中的密码（“memberpassword”，$pass)被sql代码更新？有可能吗？

   //database connection
        $SQL = "mysql:host=" . $this->MYSQL_HOST . ";dbname=" . $this->MYSQL_DB;

        try {
            $this->pdo = new \PDO($SQL, $this->MYSQL_USER, $this->MYSQL_PASS);
            $this->pdo->exec("SET NAMES'" . $this->CHARSET . "'COLLATE'" . $this->COLLATION . "'");
            $this->pdo->exec("SET CHARACTER SET'" . $this->CHARSET . "'");
            $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_OBJ);

            
        } catch (PDOException $e) {
            die( $e->getMessage());
        }
    }
    //Connect DB END

    private function myQuery($query, $params = null)
        {
            if (is_null($params)) {
                $this->stmt = $this->pdo->query($query);
            } else {
                $this->stmt = $this->pdo->prepare($query);
                $this->stmt->execute($params);
            }
            return $this->stmt;
        }
    
    
        public function Update($query, $params = null)
            {
                try {
                    return $this->myQuery($query, $params)->rowCount();
                } catch (PDOException $e) {
                    die($e->getMessage());
                }
            }
    
    
            
            $update = $db->Update("UPDATE members SET
            MemberUsername=?,
            MemberPassword=?,
            MemberEmail=?,
            MemberName=?,
            MemberLastName=?,
            MemberBirthday=?,
            MemberAge=?,
            MemberGender=?,
            CityID=?
            WHERE MemberID=?
            ", array($username, $pass, $email, $name, $lastname, $birthday, $age, $gender, $city, $memberID));

Answer 1

您可以轻松地使用IFNULL(expr1、expr2):

IFNULL返回expr1，如果它们不为null，则返回expr2

$update = $db->Update("UPDATE members SET
            MemberUsername=?,
            MemberPassword=IFNULL(?,MemberPassword),
            MemberEmail=?,
            MemberName=?,
            MemberLastName=?,
            MemberBirthday=?,
            MemberAge=?,
            MemberGender=?,
            CityID=?
            WHERE MemberID=?
            ", array($username, $pass, $email, $name, $lastname, $birthday, $age, $gender, $city, $memberID));